Microsoft KB Archive/327969

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

Article ID: 327969

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Service Pack 3

-



This article was previously published under Q327969



BUG #: 360547 (SHILOH_BUGS)



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  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  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\  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.

