FIX: Ad Hoc Access Incorrectly Permitted If DisallowAdhocAccess Registry Key Is Missing (328569)

SYMPTOMS

When you configure an OLE DB provider for use for SQL Server distributed queries, a number of registry keys are available that control how SQL Server tries to use the provider. These options are documented in the following section of SQL Server Books Online:

Configuring OLE DB Providers for Distributed Queries

One of these options, DisallowAdhocAccess, controls whether a non-sysadmin user is permitted to use the OPENROWSET or the OPENDATASOURCE function. If an OLE DB provider is available on the computer but there is no corresponding registry key specified for this provider, then SQL Server incorrectly permits a non-sysadmin user to access the provider with the OPENROWSET or the OPENDATASOURCE function. In the absence of any registry key, the default behavior should be to deny access.

After you install the fix that is described in this article, when the DisallowAdhocAccess value does not exist for the specified provider, non-sysadmin requests to use OPENROWSET or OPENDATASOURCE fail with the following error message:

Server: Msg 7415, Level 16, State 1, Line 1
Ad hoc access to OLE DB provider 'CustomOLEDBProvider' has been denied. You must access this provider through a linked server.

RESOLUTION

To resolve this problem, obtain the latest service pack for Microsoft SQL Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

290211 INF: How To Obtain the Latest SQL Server 2000 Service Pack

MORE INFORMATION

Initially, the DisallowAdhocAccess registry key did not exist in SQL Server 7.0; however, it was added in SQL Server 7.0 Service Pack 2. For backward compatibility, if this key is missing, SQL Server 7.0 still permits access through the SQLOLEDB provider because the remote server will first authenticate you by using the supplied security credentials. By default, SQL Server 2000 installations include the DisallowAdhocAccess key (therefore, allowing access). If the registry key is removed for the SQLOLEDB provider on a SQL Server 2000 installation, it denies access as it would for any other provider.

If the provider is known to honor the authentication information passed to it, and ad hoc access for non-sysadmin users is what you want, then this registry key must be present after the hotfix is applied. You can find the entries for each provider in the following registry location

HKEY_LOCAL_MACHINE\Software\Microsoft\<Instance name>\Providers\<Provider name>