SQLSERVERLEARNER

When you try to execute queries involving OPENROWSET on SQL Server you get the following error:

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.

This occurs basically when Ad Hoc Distributed Queries is disabled.

So how to enable this:

Simple by just changing the configuration of SQL Server using sp_configure
More about using sp_configure can be found here

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:

Open SQL Server Management Studio (SSMS)

Create a new query on master database
Run the following query:
[sql]
sp_configure ‘show advanced options’,1
reconfigure
[/sql]

This enables sp_configure to show advanced options

Now run sp_configure

As shown in the screenshot below you can see ‘Ad Hoc Distributed Queries’ having config_value of 0

To enable this run the below query

[sql]
sp_configure ‘Ad Hoc Distributed Queries’,1
reconfigure
[/sql]

Now if you run sp_configure you will find it enabled as shown in the screen shot below

If you run the query with OPENROWSET now you do not get the same error again.

Enjoyed enabling Ad Hoc Distributed Queries? I enjoyed it 🙂