How to use the DisallowAdHocAccess setting to control access to linked servers

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

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

Disable Transact-SQL statements

You can disable Transact-SQL statements that use ad-hoc connection strings with specific OLE DB providers in the OPENROWSET and OPENDATASOURCE functions by taking one of the following actions:

  • 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.
  • If the linked server already exists, you can manually modify the registry and then add the DisallowAdHocAccess value.

back to the top

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 this, 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.

back to the top

Manually modify the registry, and add the DisallowAdHocAccess value

After a linked server is saved, the DisallowAdHocAccess property can be set only 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, 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:
322756 How to back up and restore the registry in Windows

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\<ProviderName>

    Example

    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\<ProviderName>

    Example

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\Microsoft.ACE.OLEDB.12.0
  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:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\<ProviderName>

Note
  • A change of the value of DisallowAdHocAscess from 1 to 0 would not require a restart of the SQL Service, whereas a change from 0 to 1 would have to have a SQL Service restart for the change that was made to become effective.
  • 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 receive an error message that resembles the following:
    Server: Msg 7415, Level 16, State 1, Line 1 Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.
    In other words, with the DisallowAdHocAccess property set to 1 for a specific OLE DB provider, you must use a predefined 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.


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

back to the top
Microsoft Security Bulletin MS02-040
Properties

Article ID: 327489 - Last Review: 05/18/2015 16:07:00 - Revision: 4.0

Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2000 Standard Edition, Microsoft SQL Server 7.0 Standard Edition

  • kbhowtomaster KB327489
Feedback