{"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":"<p>When you try to execute queries involving OPENROWSET on SQL Server you get the following error:<\/p>\n<p><font color=\"red\"><br \/>\nSQL Server blocked access to STATEMENT &#8216;OpenRowset\/OpenDatasource&#8217; of component &#8216;Ad Hoc Distributed Queries&#8217; because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of &#8216;Ad Hoc Distributed Queries&#8217; by using sp_configure. For more information about enabling &#8216;Ad Hoc Distributed Queries&#8217;, see &#8220;Surface Area Configuration&#8221; in SQL Server Books Online. <\/font><\/p>\n<p>This occurs basically when <strong>Ad Hoc Distributed Queries<\/strong> is disabled.<\/p>\n<p><strong>So how to enable this:<\/strong><\/p>\n<p>Simple by just changing the configuration of SQL Server using <strong>sp_configure<\/strong><br \/>\nMore about using sp_configure can be found <a href=\"http:\/\/sqlserverlearner.com\/2011\/using-sp_configure-sql-server\">here<\/a><\/p>\n<p><strong>Following are the steps to enable Ad Hoc Distributed Queries on SQL Server:<\/strong><\/p>\n<p>Open SQL Server Management Studio (SSMS)<\/p>\n<p>Create a new query on master database<br \/>\nRun the following query:<br \/>\n[sql]<br \/>\nsp_configure &#8216;show advanced options&#8217;,1<br \/>\nreconfigure<br \/>\n[\/sql]<\/p>\n<p>This enables sp_configure to show advanced options<\/p>\n<p>Now run sp_configure<\/p>\n<p>As shown in the screenshot below you can see &#8216;Ad Hoc Distributed Queries&#8217; having config_value of 0<\/p>\n<figure id=\"attachment_506\" aria-describedby=\"caption-attachment-506\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/sqlserverlearner.com\/wp-content\/uploads\/2011\/07\/Ad-Hoc-Distributed-Queries.png\"><img loading=\"lazy\" decoding=\"async\" src=\"\/wp-content\/uploads\/2011\/07\/Ad-Hoc-Distributed-Queries-300x85.png\" alt=\"Ad Hoc Distributed Queries disabled\" title=\"Ad Hoc Distributed Queries\" width=\"300\" height=\"85\" class=\"size-medium wp-image-506\"><\/a><figcaption id=\"caption-attachment-506\" class=\"wp-caption-text\">Ad Hoc Distributed Queries disabled<\/figcaption><\/figure>\n<p>To enable this run the below query<\/p>\n<p>[sql]<br \/>\nsp_configure &#8216;Ad Hoc Distributed Queries&#8217;,1<br \/>\nreconfigure<br \/>\n[\/sql]<\/p>\n<p>Now if you run sp_configure you will find it enabled as shown in the screen shot below<\/p>\n<figure id=\"attachment_507\" aria-describedby=\"caption-attachment-507\" style=\"width: 300px\" class=\"wp-caption aligncenter\"><a href=\"https:\/\/sqlserverlearner.com\/wp-content\/uploads\/2011\/07\/Ad-Hoc-Distributed-Queries-Enabled.png\"><img loading=\"lazy\" decoding=\"async\" src=\"\/wp-content\/uploads\/2011\/07\/Ad-Hoc-Distributed-Queries-Enabled-300x72.png\" alt=\"Ad Hoc Distributed Queries Enabled\" title=\"Ad Hoc Distributed Queries Enabled\" width=\"300\" height=\"72\" class=\"size-medium wp-image-507\"><\/a><figcaption id=\"caption-attachment-507\" class=\"wp-caption-text\">Ad Hoc Distributed Queries Enabled<\/figcaption><\/figure>\n<p>If you run the query with OPENROWSET now you do not get the same error again.<\/p>\n<p>Enjoyed enabling Ad Hoc Distributed Queries? I enjoyed it \ud83d\ude42<\/p>\n","protected":false},"excerpt":{"rendered":"<p>When you try to execute queries involving OPENROWSET on SQL Server you get the following error: SQL Server blocked access to STATEMENT &#8216;OpenRowset\/OpenDatasource&#8217; of component &#8216;Ad Hoc Distributed Queries&#8217; because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of &#8216;Ad Hoc Distributed&hellip;<\/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],"class_list":["post-56","post","type-post","status-publish","format-standard","hentry","category-configuring-sql-server","category-sql-errors","category-sql-server-basics","tag-a-system-administrator-can-enable-the-use-of-ad-hoc-distributed-queries-by-using-sp_configure","tag-for-more-information-about-enabling-ad-hoc-distributed-queries-see-surface-area-configuration-in-sql-server-books-online","tag-how-to-enable-ad-hoc-distributed-queries","tag-i-am-getting-error-when-trying-to-use-openrowset","tag-sp_configure-ad-hoc-distributed-queries","tag-sql-server-blocked-access-to-statement-openrowset-opendatasource-of-component-ad-hoc-distributed-queries","tag-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","tag-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-s"],"_links":{"self":[{"href":"https:\/\/sqlserverlearner.com\/wp-json\/wp\/v2\/posts\/56","targetHints":{"allow":["GET"]}}],"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}]}}