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)
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:
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.
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:
(http://support.microsoft.com/kb/290211/EN-US/ )INF: How To Obtain the Latest SQL Server 2000 Service Pack
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.
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.