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

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.

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.

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.


References

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

Propriedades

ID do Artigo: 327489 - Última Revisão: 18 de mai de 2015 - Revisão: 1

Comentários