FIX: Only System Administrators Can Perform Ad Hoc Queries in SQL Server Service Pack 3 (327969)

SYMPTOMS

You may receive one of the following messages when you try to perform an ad hoc query:

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

If you are using Oracle, you receive the following error message:

Ad hoc access to OLE DB provider ProviderName has been denied. You must access this provider through a linked server.

CAUSE

By default, in SQL Server Service Pack 3 (SP3) or later, users who are not members of the sysadmin role (whether they are using SQL Server or Microsoft Windows authentication) cannot perform ad hoc queries when the provider registry key is not present. Only members of the sysadmin role can perform ad hoc queries when the provider registry key is not present. However, if the provider is SQLOLEDB, all users can perform ad hoc queries, even if the provider key is not present.

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

By default, in Microsoft SQL Server 2000, Microsoft SQL Server 2000 Service Pack 1 (SP1), and Microsoft SQL Server 2000 Service Pack 2 (SP2), ad hoc access is enabled for all Windows logins if the DisallowAdhocAccess registry key was not present for a particular provider.

This registry key is in the right-pane window under the AllowInProcess key in the HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLSERVER\Providers\your provider key.

This behavior provides a safer environment:

Untrusted providers cannot be enabled by default.
Users with guest-level user rights cannot take advantage of provider vulnerabilities.

STATUS

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