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
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
Server: Msg 7415, Level 16, State 1, Line
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
INF: How To Obtain the Latest SQL Server 2000 Service Pack
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
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.