Enable OPENROWSET

Posted on : 11-07-2011 | By : Devi Prasad | In : Configuring SQL Server, SQL Errors, SQL Server Basics

Share:

1


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

Ad Hoc Distributed Queries disabled

Ad Hoc Distributed Queries disabled

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

Ad Hoc Distributed Queries Enabled

Ad Hoc Distributed Queries Enabled

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 🙂




References : Devi Prasad (sqlserverlearner.com)

Need Help On SQL Server?

Cannot Find Solution to your problem (or) If you are looking for some help on SQL Server. Dont worry Click Here to Post your question and solve your issue.


Do you like my blog?

If you liked reading this blog, please help spread the word by sharing this blog with your friends.




Comments (1)

Thanks for posting this article. Very helpful.

Write a comment



Tags: , , , , , , ,