Problem Statement: One day I was trying to fetch data from one instance of SQL Server to another instance of SQL Server and I was getting below error message while executing the Openrowset() funtion. So I thought to share this with my blog readers. Below is the query that I was trying to execute.
from openrowset(‘SQLNCLI’,’Server=Neeraj-PC\sql2012;uid=sa;pwd=Mind1234;database=MyDB’,’select * from employee’)
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’, see “Surface Area Configuration” in SQL Server Books Online.
Solution: The reason for this problem is that Adhoc distributed queries are not enabled in SQL Server by default. We have to explicitly enable that feature. Lets see how to enable that feature. To enable or disable any feature at server level, Microsoft has given one SP(stored procedure) that is really very useful. In this article I will tell you how to see server level configuration using that SP.
“Sp_Configure” can be used to see the server level configurations in SQL, If we run this SP it will show 16 server level configurations as shown in above screenshot. This is not the complete list, to see more features, we have to run this SP by passing below parameters. Lets see what it returns after executing it.
sp_configure ‘Show Advanced Options‘,1 reconfigure with override
After running above command, we can see all the advanced options that can be configure using “Sp_Reconfigure” SP. lets run “SP_Configure” again and see how many configuration settings are available to configure.
Now “Sp_Configure” will show 70 configurations and also show the option “Ad Hoc Distributed Quries” as well that we want to enable. If you look at the above screenshot, its config_Value is 0, which means it is not enabled. To enable this option, we will run “Sp_configure” SP with below mentioned parameter.
sp_configure ‘Ad Hoc Distributed Queries‘,1 reconfigure with override
Now Adhoc queries are enabled, lets execute the below query and see the results.
Conclusion: We can use “Sp_Configure” SP to enable or disable a specific feature in SQL Server. To enable the feature, we have to provide feature name as parameter and “1” , followed by reconfigure with override statement. To disable the feature, we have to provide feature name as parameter and “0” , followed by reconfigure with override statement. I hope you will find this article useful.
2,006 total views, 25 views today