Monday, July 28, 2014

SQL Server blocked access to statement OPENROWSET / OPENDATASOURCE

Sometime you may get below error when you execute query using OPENROWSET statement:

Msg 15281, Level 16, State 1, Line 1
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', search for 'Ad Hoc Distributed Queries' in SQL Server Books Online.

This error is coming because Ad Hoc Distributed Queries configuration parameter is disabled. To enable Ad Hoc Distributed Queries you had to execute below commands:

sp_configure 'show advanced options',1  
sp_configure 'Ad Hoc Distributed Queries',1  

After executing above commands your problem will be solved.

You can do above changes in following ways also:

Right Click in SSMS on the SQL Server instance from the object explorer pane and choose Facets from the drop down menu.

In the facets window change the Facet to Surface Area Confuguration and set the AdHocRemoteQueriesEnabled to True

No comments:

Post a Comment