Wednesday, June 16, 2010

SQL Server ERROR 15281 Ad Hoc Distributed Queries Component Is Turned Off

Usually we get this kind of error while retrieving data from other source like MS Access, Excel, etc.

Error :
Msg 15281, Level 16, State 1, Line 3 
SQL Server blocked access to STATEMENT ‘OpenRowset/OpenDatasource’ of component ‘Ad Hoc Distributed Queries’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Ad Hoc Distributed Queries’ by using sp_configure. For more information about enabling ‘Ad Hoc Distributed Queries’, see “Surface Area Configuration” in SQL Server Books Online. 

Fix / Workaround:
For this you have to enable Ad-hoc remote queries components through SQL Server Surface Area Configuration Manager or T-SQL SP_Configure. Please follow below for enabling through GUI.

In SQL Server 2005 you can use Surface area configuration manager, if it is in sql server 2008 or higher version use facets, from 2008 onwards they have replaced Surface are configuration manger with Facets. 

Step 1: Open SQL Server Surface Area Configuration from Start Menu. 

Step 2: Select Surface Area Configuration for Features. Select the Ad-Hoc Remote Queries component and check the box "Enable OPENROWSET and OPENDATASOURCE support".


Click the OK button to apply the changes. Close the Surface Area Configuration window.
Surface Area Configuration for Features. ad hoc distributed queries Server Configuration Option. What are the security risks with enabling Ad Hoc Remote Queries. What are the security risks with enabling Ad Hoc Remote Queries. Running Ad Hoc Queries on Remote SQL Server 2008 or 2012 or 2005 or 2008 R2. How to enable the use of 'Ad Hoc Distributed Queries'. AD Hoc remote quiers. Msg 15281. Level 16. State 1. Line 3. SQL Server blocked access to STATEMENT. xp_cmdshell enabled but still throws error. Excel Import to SQL Server using Distributed Queries. Getting execute permission to xp_cmdshell. Running Queries on Excel Spreadsheets using SQL Server 2008. OLE DB provider.

0 comments:

Post a Comment