How to enable the use of 'Ad Hoc Distributed Queries' by using sp_configure

 

转自：http://www.kodyaz.com/articles/enable-Ad-Hoc-Distributed-Queries.aspx

 

If you are planning to use OpenRowset queries in order to connet to remote database servers or if you have already implemented OpenRowset queries as a solution to remote connections as an alternative tp linked servers in Microsoft SQL Server 2005, you should first configure the database instance to enable Ad Hoc Distributed Queries in the installed SQL Server database instance where the Ad Hoc query will run.

There are two ways that you can configure MS SQL Server 2005 or SQL Server 2008 Katmai instance for Ad Hoc Remote Queries:

• You can either use SQL Server Surface Area Configuration Tool
• Or you can use sp_configure stored procedure to enable the ad hoc connections to remote data sources

• Although I'm sure I have configured all necessary configuration settings in the database server to let OpenRowset functions, after months later the application is released for the production site, I had the following error from an application recently:

  

  An error occured while trying to execute the query:
  - CODBCQuery.Open, SQLExecDirect 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]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 SQK Server Books Online. 42000: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not use view or function '{0}' because of binding errors.

  In fact, the error message is self-explaining the situation.
  It is indicating that in order to run the OpenRowset and the OpenDatasource statements the turned off configuation settings for the related sql database server should be enabled.

   

  First, let's check the SQL 2005 configuration settings using sp_configure sql command.

   

  Now, we should connect to the related SQL Server as an administrator and open a new query window.
  
  After the query window is ready for running sql statements run the "sp_configure" sql statement.
  
  If sp_configure command only lists a limited number (~14) of sql configuation settings, where 'Ad Hoc Distributed Queries' does not exist in the returned result set, we should open/enable the 'show advanced options' configuration parameter.
  
  You can see 'show advanced options' in the list with run_value equals to "0" in such a situation.

  

  To set 'show advanced options' run_value equal to 1 or to enable it, run

  sp_configure 'show advanced options', 1
reconfigure

   

  The return message from the above sql statements for a successful run is as;

  Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

  After Advanced Options is enabled, you can again run sp_configure t-sql command and in the returned list of configuration settings, go to row where name is 'Ad Hoc Distributed Queries' and control its run_value.
  If 'Ad Hoc Distributed Queries' is turned off for considering server security run_value should be "0"
  But since we want to enable 'Ad Hoc Distributed Queries' component in order to run 'OpenRowset/OpenDatasource' sql statements, we should set the run_value to "1"

  The below sql code is a sample how you can enable a SQL Server configuration parameter.

  sp_configure 'Ad Hoc Distributed Queries', 1

   

  The returned message is :

  Configuration option 'Ad Hoc Distributed Queries' changed from 0 to 1. Run the RECONFIGURE statement to install.

   

  A change with sp_configure comment will require reconfigure command to run in order to the new setting takes effect. So just run the "reconfigure" command:

  reconfigure

  Now you can see the run_value is set to 1 if you run the "sp_configure" command and control for the "Ad Hoc Distributed Queries" row.

  Now you can run your OpenRowset queries successfully from your SQL Server 2005 or SQL Server 2008 (Katmai) databases.

  Second, use the SQL Server Surface Area Configuration Tool to enable and/or disable "Ad Hoc Remote Queries".

  

  The OpenRowset and OpenDatasource functions support ad hoc connections to remote data sources without linked or remote servers. We can enable these functions by checking the "Enable OPENROWSET and OPENDATASOURCE support" checkbox.