SQL Server Auditing

Question

Post reply

SQL Server Auditing

Kyle Schlapkohl

SSCrazy

Points: 2463
More actions
March 28, 2008 at 12:32 pm

#185674

Does anyone have a query to determine if auditing is turned on and what it is set to (It needs to be set to failed logins)? Also where the log directory is going? I need the query to work on both SQL Server 2000 and 2005 servers. Any help is appreciated.
-Kyle
-----------------------------------------------
This isn't C2 audit mode that I am looking for. I need to know that auditing is turned on and at a minimum set to failed logins. Also, I need to know the directory that the log files are going.
-Kyle

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

N J Right there with Babe Points: 732 More actions · Answer 1

Sql server saves this information in registry.

You can use this query to get the info

DECLARE @test-2 int

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',

@value_name='AuditLevel',

@value=@test OUTPUT

SELECT case @test-2

when '0' then 'None'

when '2' then 'Failed Logins Only'

when '1' then 'Successful Logins Only'

when '3' then 'Both Failed and Successful Logins'

else 'Unknown'

end

Adam Bean One Orange Chip Points: 26631 More actions · Answer 2

neeraj arora (3/28/2008)
Sql server saves this information in registry.
You can use this query to get the info
DECLARE @test-2 int
EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',
@key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',
@value_name='AuditLevel',
@value=@test OUTPUT
SELECT case @test-2
when '0' then 'None'
when '2' then 'Failed Logins Only'
when '1' then 'Successful Logins Only'
when '3' then 'Both Failed and Successful Logins'
else 'Unknown'
end

I was just posting something similar 🙂

Anyways, be careful ... depending on your server, your installation may not reside in the \MSSQL.1 folder (instances for example). The above will work fine if you only have one installation on your server(s). If that is not the case, than with the above query, you'll always get unknown ... I'd recommend taking out that else statement to be sure. You should receive a 'RegOpenKeyEx() returned error 2, 'The system cannot find the file specified' and a NULL output if that location is not correct.

N J Right there with Babe Points: 732 More actions · Answer 3

This will work for Sql Server 2000

DECLARE @test-2 int

EXEC master..xp_regread @rootkey='HKEY_LOCAL_MACHINE',

@key='SOFTWARE\Microsoft\MSSQLServer\MSSQLServer',

@value_name='AuditLevel',

@value=@test OUTPUT

SELECT case @test-2

when '0' then 'None'

when '2' then 'Failed Logins Only'

when '1' then 'Successful Logins Only'

when '3' then 'Both Failed and Successful Logins'

else 'Unknown'

end

This login information is saved in Sql Server errorlog .. no seperate log file created for this..

N J Right there with Babe Points: 732 More actions · Answer 4

Actually i am running this script in my environment which have all default instances so working fine for me..

Anyway thanks for valuable advice.

Thanks

NJ

Kyle Schlapkohl SSCrazy Points: 2463 More actions · Answer 5

Kyle Schlapkohl

SSCrazy

Points: 2463

March 28, 2008 at 1:03 pm

#795422

What does this mean when unknown is queried?

-Kyle

Adam Bean One Orange Chip Points: 26631 More actions · Answer 6

Kyle Schlapkohl (3/28/2008)
What does this mean when unknown is queried?
-Kyle

Means that the registry location is incorrect. @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer'

Kyle Schlapkohl SSCrazy Points: 2463 More actions · Answer 7

Would the registry location be findable with regedit? If not, how did you find where the registry location was?

-Kyle

N J Right there with Babe Points: 732 More actions · Answer 8

kyle,

You can find this @key location by runnig regedit also for verification. This is the default location in registry for sql server parameters and configuration.

You can also search for 'auditlevel' parameter in registry if you don't find it at default location

NJ