Select the product you need help with

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

Article ID: 328569 - View products that this article applies to.

This article was previously published under Q328569

BUG #: 361943 (SHILOH_BUGS)

BUG #: 102857 (SQLBUG_70)

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.

Back to the top | Give Feedback

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

(http://support.microsoft.com/kb/290211/EN-US/ )

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

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.
SQL Server 2000
This problem was first corrected in Microsoft SQL Server 2000 Service Pack 3.

Back to the top | Give Feedback

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>

where <Instance name> is the name of the instance of SQL Server (MSSQLServer for the default instance) and <Provider name> is the name of the OLE DB provider that is specified in the OPENROWSET or the OPENDATASOURCE function.

Back to the top | Give Feedback