failed login auditing

Question

failed login auditing

roger.hurst

SSC Veteran

Points: 270
More actions
August 21, 2008 at 3:38 pm

#197227

This is a quickie for somebody, I'm sure:
How is "failed login auditing" turned on in SQL Server via some procedural method. I need to set it in a script.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

The Auditlevel is set in a registry key. You can change it programmatically by running this

EXEC master..xp_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer',N'Auditlevel', REG_DWORD, 2

In case you use a named instance use xp_instance_regwrite instead of xp_regwrite and you also have to adjust the location of the key.

And of course you must have write permission in the registry.

[font="Verdana"]Markus Bohse[/font]

roger.hurst SSC Veteran Points: 270 More actions · Answer 2

Thanks very much for your help!

Does this require a reboot?

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 3

roger.hurst (8/26/2008)
Does this require a reboot?

Not exactly a reboot, but you need to restart the SQL Server Service.

[font="Verdana"]Markus Bohse[/font]

Michael Meierruth SSChampion Points: 10051 More actions · Answer 4

roger.hurst (8/21/2008)
This is a quickie for somebody, I'm sure:
How is "failed login auditing" turned on in SQL Server via some procedural method. I need to set it in a script.
Thanks

Just a few questions - out of curiosity:

1. why do you need to do this with a script? do you intend to turn it on and off often? if so, why?

2. how far are you going with this? are you just occassionally scanning the SQL Server log? or are you building something that catches failed logins in real time?

Regarding this last item of catching failed logins in real time, it was quite a nightmare when I had to approach this problem. It was on SQL Server 2000 and my approach was to catch failed logins getting written to the Windows security event log using a program that binds a WMI query to an event monitor. In the end, it provided a lot of flexibility especially catching N failed logins for a given user within a given time period. This would fire email alerts and 'disable' the user, etc.

If you are going this route, let me know how you are approaching this.

roger.hurst SSC Veteran Points: 270 More actions · Answer 5

Michael:

Thanks for your input:

Just a few questions - out of curiosity:

1. why do you need to do this with a script? do you intend to turn it on and off often? if so, why?

The requirement is to turn it on during installation--hence the script.

2. how far are you going with this? are you just occassionally scanning the SQL Server log? or are you building something that catches failed logins in real time?

Turning on failed login auditing is the only requirement right now. Any input you might have for real-time catches is appreciated if we do have that requirement in the future.

b/r.

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 6

You can catch Logon events real time by using DDL Triggers. Only catch is you need to install SP2 first.

Here you find a good article about this feature:

http://www.sqlservercentral.com/articles/SQLServerCentral.com/sqlserver2005logontriggers/2366/

[font="Verdana"]Markus Bohse[/font]