SQL SERVER – Fix : Error : 15281 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

Error :
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.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Fix : Error : 15281 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

  1. 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?

  2. 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?

  3. 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

  4. 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

  5. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s