January 5, 2005 at 3:10 pm
In an article by Brian Kelly (http://qa.sqlservercentral.com/columnists/bkelley/sp3coresecurity.asp) there is a discussion of SP3's handling of the DisallowAdhocAccess key or the meaning of the lack of this key. I noticed that by default all providers except SQLOLEDB are missing this key so they would ; according to this arcticle ; disable Transact-SQL statements that use ad-hoc connection strings with specific OLE DB providers in the OPENROWSET and OPENDATASOURCE functions.
Okay, that leaves SQLOLEDB vulnerable. Should the DisallowAdhocAccess registry key for this provided be changed to 1? Has anyone done this? I realize that some applications may make use of this so testing is in order, but in general is doing this recommended?
Francis
January 5, 2005 at 7:49 pm
Generally, if you don't need something you should not allow the behavior. This blocks non-sysadmin level access to adhoc queries. Keep in mind that it doesn't stop adhoc queries executed by logins that are members of the sysadmin fixed server role. That means SQL Server and SQL Server Agent will be able to execute adhoc queries if needed (as would any DBAs with sysadmin rights), but the typical user couldn't.
If your users aren't using linked servers and distributed queries, there's no real reason to have adhoc access permitted. Therefore, changing to 1 would be the recommendation from a security perspective.
Microsoft's KB articles on the subject:
HOW TO: Use the DisallowAdHocAccess Setting to Control Access to Linked Servers (327489)
FIX: Ad Hoc Access Incorrectly Permitted If DisallowAdhocAccess Registry Key Is Missing (328569)
K. Brian Kelley
@kbriankelley
January 6, 2005 at 7:43 am
Thanks for the clarification Brian.
Francis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply