Error 15281 :
Msg 15281, Level 16, State 1, Line 3
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.
Fix/Workaround/Solution :
On the Start menu, point to All Programs, Microsoft SQL Server 2005, Configuration Tools, and then click SQL Server Surface Area Configuration.
Click on Surface Area Configuration for Features
Turn on the check box displayed in the screen below.
Update: I have noticed that this error does not apply to the new version of the SQL Server. The surface area configuration is deprecated in the new version of the SQL Server and it is much simpler configuration in the new version.
If you are still using SQL Server 2005, I strongly suggest that you upgrade to the latest version of SQL Server as while writing this note, SQL Server 2005 is almost 11 years old product and it is not safe for your application to run such a outdated product.
Reference: Pinal Dave (https://blog.sqlauthority.com)
11 Comments. Leave new
Hello,
is there a good alternativ to connect to other server than using opendatasource? Linked server?
Thanks and regards,
Volker Bachmann
thanks, good option to query(DML as well) data from excel file
Abid Hussain tabassum
To use the adho query, why should we enable the ‘Ad Hoc Distributed Queries’ both on the orginating SQL Server Instance and target SQL Server Instances?
Why should we have “‘Ad Hoc Distributed Queries” value as 1 both in source and target SQL Server Instances?
Can this be done with script?
Good Article
Okay, I have a question about this, because I have the same problem with the query I am running the only thing is that I am trying to get a query to work under 2008 R2 where this is disabled by our security policies. Is their a different method that can be used without enabling Ad Hoc Distributed Queries?
thnx alot
Hi,
How to use “sp_configure to set some common Advanced Options” in STORED PROCEDURE?
In my Procedure i have to use some common Advanced Options in “SP_CONFIGURE”.
but there is following error Occurred at every time when i execute.
Error is : “Msg 15281, Level 16, State 1, Procedure PrExportToExcel, Line 40
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. ”
i have solved this error but when i was alter my given procedure again this error is occurred.
The Problem is
i’m using following command in between #temp in Given Procedure.
exec sp_configure ‘show advanced options’, 1;
RECONFIGURE WITH OVERRIDE
exec sp_configure ‘Ad Hoc Distributed Queries’, 1;
RECONFIGURE WITH OVERRIDE
Please any one know, how i can use “sp_configure ” in my procedure.
then please reply immediately.
Thnx in Advance
even me facing problem i am new to sql server i am using sql server 2008 and i am unable to view SQL Server Surface Area Configuration on defiened location… so how can i change things using sp_configuration
Thanks
After few hours trying to start the SQL Agent because I could not start it anymore after moving databases including the system ones this solution allowed me to do it and MANY THANKS Dave.
Thank you very much