HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers

This article explains how to disable ad hoc queries that use the OPENROWSET or the OPENDATASOURCE functionality in SQL Server.

Disable Transact-SQL Statements

There are two ways that you can disable Transact-SQL statements that use ad-hoc connection strings with specific OLE DB providers in the OPENROWSET and OPENDATASOURCE functions:

• When you create a linked server on a computer that is running SQL Server, you can specify the DisallowAdHocAccess property for the OLE DB provider.
  
  -or-
  
  
• If the linked server already exists, you can manually modify the registry and add the DisallowAdHocAccess value.

Specify the DisallowAdHocAccess Property When You Create a Linked Server

When you create a linked server on a computer that is running SQL Server, you can specify the DisallowAdHocAccess property for the OLE DB provider. To do so, follow these steps:

1. Open SQL Server Enterprise Manager, and then click to select the Security folder of the server in question.
2. Right-click the Linked Servers entry, and then click New Linked Server.
3. Click to select the OLE DB provider you want to use, and then click the Provider Options button.
4. Scroll down and select the Disallow adhoc access property check box. Continue to finish the creation of your linked server entry.

Manually Modify the Registry and Add the DisallowAdHocAccess Value

After a linked server is saved, the DisallowAdHocAccess property can only be set through a registry setting.

NOTE: The two illustrations are just examples of how you can change the OLE DB provider for both ODBC and for the SQL Server OLE DB provider. If you want to use a different OLE DB provider, then you must modify that Provider's entry.

Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base:

Add the DisallowAdHocAccess Value

To add the DisallowAdHocAccess value, follow these steps:

1. Start Registry Editor.
2. Locate, and then click the following key in the registry:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL
3. On the Edit menu, click Add Value, and then add this registry value:

      Value name: DisallowAdHocAccess
      Data type:  REG_DWORD
      Radix:      Hex
      Value data: 1

4. Quit Registry Editor.

Modify an Existing DisallowAdHocAccess Value

To modify an existing DisallowAdHocAccess value, follow these steps:

1. Start Registry Editor.
2. Locate, and then click the DisallowAdHocAccess value under the following key in the registry:
   HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB
3. On the Edit menu, click DWORD, type 1, and then click OK.
4. Quit Registry Editor.

For a named instance, the registry key is different:


NOTE: With the DisallowAdHocAccess property set to 1, SQL Server does not allow ad hoc access through the OPENROWSET and the OPENDATASOURCE functions against the specified OLE DB provider. If you try to call these functions in ad hoc queries, you will receive an error message similar to: In other words, with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a pre-defined linked server setup for the specific OLE DB provider; you can no longer pass in an ad hoc connection string that references that provider to the OPENROWSET or the OPENDATASOURCE function.

SQL Server Books Online; topics: "Configuring OLE DB providers for Distributed Queries"; "OPENROWSET Transact-SQL Reference"; "OPENDATASOURCE Transact-SQL Reference"