Check Adhoc

  • Hi,

    How can i  Check Adhoc Query Settings in sql server.

    from

    Killer

  • 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

     

  • 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

  • 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

     


    Kindest Regards,

    Scott Beckstead

    "We cannot defend freedom abroad by abandoning it here at home!"
    Edward R. Murrow

    scottbeckstead.com

  • 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"

  • 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