Problem

When executing queries using OPENROWSET statement, the following error can appear:

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.

 

Solution

This error appears because Ad Hoc Distributed Queries configuration parameter is disabled.

To enable Ad Hoc Distributed Queries follow the next steps:

First, make sure that you can see all SQL configuration settings when running sp_configure command by enabling the 'show advanced options' configuration parameter:

sp_configure 'show advanced options',1
reconfigure

After this, enable the 'Ad Hoc Distributed Queries' by running the following statements:

sp_configure 'Ad Hoc Distributed Queries',1
reconfigure

Executing sp_configure command you can see that the parameter is enabled:

 

...

 

The same modification can be done by right clicking in SSMS (SQL Server management Studio) 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:

 

 

 


4 comments

Comment from: pavan [Visitor]
pavanU r great, I always search ur site first.. then i go to other site if i donot find here.
11/10/15 @ 07:18
Comment from: admin [Member] Email
Thanks!
11/12/15 @ 23:40
Comment from: JJin SA [Visitor] Email
JJin SAGreat, I don't really like sp_configure with the Force option.
03/10/16 @ 21:26
Comment from: DDDDD [Visitor]
DDDDDThe same modification can be done by right clicking in SSMS (SQL Server management Studio) on the SQL Server instance from the Object Explorer pane and choose Facets from the drop down menu:

this is working.
12/27/16 @ 11:02

Leave a comment


Your email address will not be revealed on this site.
(Line breaks become <br />)
(For my next comment on this site)
(Allow users to contact me through a message form -- Your email will not be revealed!)