October 20, 2005 at 4:06 am
Hi,
How can i Check Adhoc Query Settings in sql server.
from
Killer
October 20, 2005 at 5:43 am
Not exact sure which Adhoc Query Settings you are looking for, but you may want to read this Microsoft article to get you started:
http://support.microsoft.com/default.aspx?scid=kb;en-us;q327489
October 20, 2005 at 7:50 pm
Hi Don,
By using adhoc queries a user can run queries against other database provider, this
also can be use by an attaker to brute force passwords.
i think it is clear .
from
Killer
October 21, 2005 at 10:17 am
Ok, clear as mud. My understanding of AdHoc queries is that they are queries made by anyone against any database for the purpose of determinig out of band or data for which no formal report exists. Now how does one set anything or why would there be settings for AdHoc queries? I'm sure what you say is true, using AdHoc queries is one way of brute forcing a password I suppose, but you can do that with something that simply tries to connect to the database, you don't even have to run a query. Am I misunderstanding "AdHoc" or is there a special AdHoc setting in SQLServer that I'm unaware of?
Scott
October 21, 2005 at 2:35 pm
Don's post looked like it might be relevant. Otherwise, Scott .... I have the same take on this as you ..... There are a few references in BOL to "ad hoc" regarding temporary or infrequent operations, but I don't know of any "settings"
October 21, 2005 at 7:43 pm
Hi,
SOLUTION: To Disable AdHoc Queries in the registry for each one not set: '
1. Start the Registry Editor. (Click on the Start menu, select Run, type "regedt32" and click OK.)'
2. From the Registry Editor window, select the following registry key: HKEY_LOCAL_MACHINE\Software\Microsoft\MSSQLServer\Providers\
3. For every provider add DisallowAdhocAccess REGD_WORD value and set it to 1.
Help by Sql Server Central
USE MASTER
DECLARE @keyexist INT
DECLARE @value INT
CREATE TABLE #tmp_keyexist (keyexist INT)
CREATE Table #tmp_valueSET (value INT)
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\SQLOLEDB\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: SQLOLEDB Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\SQLOLEDB','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'SQLOLEDB Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: SQLOLEDB Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\Microsoft.Jet.Oledb.4.0\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: Microsoft.Jet.Oledb.4.0 Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\Microsoft.Jet.Oledb.4.0','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'Microsoft.Jet.Oledb.4.0 Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: Microsoft.Jet.Oledb.4.0 Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSDAORA\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: MSDAORA Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSDAORA','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'MSDAORA Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: MSDAORA Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\ADSDSOObject\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: ADSDSOObject Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\ADSDSOObject','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'ADSDSOObject Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: ADSDSOObject Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\DB2OLEDB\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: DB2OLEDB Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\DB2OLEDB','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'DB2OLEDB Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: DB2OLEDB Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSIDXS\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: MSIDXS Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSIDXS','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'MSIDXS Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: MSIDXS Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSQLImpProv\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: MSQLImpProv Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSQLImpProv','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'MSQLImpProv Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: MSQLImpProv Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSSEARCHSQL\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: MSSEARCHSQL Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSSEARCHSQL','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'MSSEARCHSQL Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: MSSEARCHSQL Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
INSERT #tmp_keyexist EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSDASQL\DisallowAdhocAccess'
SELECT @keyexist = keyexist FROM #tmp_keyexist
IF @keyexist = 0
PRINT 'WARNING: MSDASQL Adhoc Queries not Disabled'
ELSE
BEGIN
INSERT #tmp_valueSET EXECUTE master.dbo.xp_regread 'HKEY_LOCAL_MACHINE','Software\Microsoft\MSSQLServer\Providers\MSDASQL','DisallowAdhocAccess'
SELECT @value = value FROM #tmp_valueSET
IF @Value = 1
PRINT 'MSDASQL Adhoc Queries are Disabled'
ELSE
PRINT 'WARNING: MSDASQL Adhoc Queries not Disabled'
END
TRUNCATE TABLE #tmp_keyexist
TRUNCATE TABLE #tmp_valueSET
--/////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
DROP TABLE #tmp_keyexist
DROP TABLE #tmp_valueSET
GO
From
Killer
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply