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: