{"id":56,"date":"2011-07-11T07:52:58","date_gmt":"2011-07-11T07:52:58","guid":{"rendered":"https:\/\/sqlserverlearner.azurewebsites.net\/2011\/07\/11\/enable-openrowset\/"},"modified":"2011-07-11T07:52:58","modified_gmt":"2011-07-11T07:52:58","slug":"enable-openrowset","status":"publish","type":"post","link":"https:\/\/sqlserverlearner.com\/2011\/07\/11\/enable-openrowset\/","title":{"rendered":"Enable OPENROWSET"},"content":{"rendered":"

When you try to execute queries involving OPENROWSET on SQL Server you get the following error:<\/p>\n


\nSQL 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. <\/font><\/p>\n

This occurs basically when Ad Hoc Distributed Queries<\/strong> is disabled.<\/p>\n

So how to enable this:<\/strong><\/p>\n

Simple by just changing the configuration of SQL Server using sp_configure<\/strong>
\nMore about using sp_configure can be found here<\/a><\/p>\n

Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:<\/strong><\/p>\n

Open SQL Server Management Studio (SSMS)<\/p>\n

Create a new query on master database
\nRun the following query:
\n[sql]
\nsp_configure ‘show advanced options’,1
\nreconfigure
\n[\/sql]<\/p>\n

This enables sp_configure to show advanced options<\/p>\n

Now run sp_configure<\/p>\n

As shown in the screenshot below you can see ‘Ad Hoc Distributed Queries’ having config_value of 0<\/p>\n

\"Ad<\/a>
Ad Hoc Distributed Queries disabled<\/figcaption><\/figure>\n

To enable this run the below query<\/p>\n

[sql]
\nsp_configure ‘Ad Hoc Distributed Queries’,1
\nreconfigure
\n[\/sql]<\/p>\n

Now if you run sp_configure you will find it enabled as shown in the screen shot below<\/p>\n

\"Ad<\/a>
Ad Hoc Distributed Queries Enabled<\/figcaption><\/figure>\n

If you run the query with OPENROWSET now you do not get the same error again.<\/p>\n

Enjoyed enabling Ad Hoc Distributed Queries? I enjoyed it \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"

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…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,128,41],"tags":[129,130,131,132,133,134,135,136],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/56"}],"collection":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/comments?post=56"}],"version-history":[{"count":0,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/56\/revisions"}],"wp:attachment":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/media?parent=56"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/categories?post=56"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/tags?post=56"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}