Microsoft KB Archive/327489

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

Article ID: 327489

Article Last Modified on 10/30/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q327489



IN THIS TASK
SUMMARY
 * Disable Transact-SQL Statements
 * Specify the DisallowAdHocAccess Property When You Create a Linked Server
 * Manually Modify the Registry and Add the DisallowAdHocAccess Value
 * Add the DisallowAdHocAccess Value
 * Modify an Existing DisallowAdHocAccess Value

REFERENCES



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

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

back to the top

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 article contains information about modifying the registry. Before you modify the registry, make sure to back it up and make sure that you understand how to restore the registry if a problem occurs. For information about how to back up, restore, and edit the registry, click the following article number to view the article in the Microsoft Knowledge Base:

256986 Description of the Microsoft Windows Registry

Add the DisallowAdHocAccess Value
To add the DisallowAdHocAccess value, follow these steps:  Start Registry Editor. Locate, and then click the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\MSDASQL

 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

 Quit Registry Editor.

Modify an Existing DisallowAdHocAccess Value
To modify an existing DisallowAdHocAccess value, follow these steps:  Start Registry Editor.</li> Locate, and then click the DisallowAdHocAccess value under the following key in the registry:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Providers\SQLOLEDB

</li> On the Edit menu, click DWORD, type 1, and then click OK.</li> Quit Registry Editor.</li></ol>

For a named instance, the registry key is different:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<Instance Name>\Providers\MSDASQL

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:

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

back to the top

<div class="references_section">