Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

The Voice of the DBA

Steve Jones is the editor of SQLServerCentral.com and visits a wide variety of data related topics in his daily editorial. Steve has spent years working as a DBA and general purpose Windows administrator, primarily working with SQL Server since it was ported from Sybase in 1990. You can follow Steve on Twitter at twitter.com/way0utwest

Auditing Failed Logins – SQL Server 2008

I wrote about setting up a basic server audit recently. That showed about how a server level audit is set up, but that’s a just a shell. The audit itself is just a container in which you can store various audited items. Like a SQL Server Agent job, it doesn’t do anything until you add some details.
One of the details that I think is worth adding is the failed login audit at the server level. Finding a large number of failed logins can clue you in to some hacking going on, so it can be good to log these.
To add these, you first need to add a server audit specification, which is like a job step. It’s a detail at the server level. Using the SSMS GUI, you can do this by right clicking the Server Audit Specification under Security. Select “New Audit Specification”
 ServerAudit_01
That gives you a dialog where you can add a name:
ServerAudit_02
And then select an audit to which you assign this particular detail.
ServerAudit_03
For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.
ServerAudit_04
Once this is done, your audit appears in the folder in SSMS.
ServerAudit_05
Now when someone tries to log in and can’t, you can view this in the logs:
DisableAudit_07

Comments

Posted by Dukagjin Maloku on 31 May 2010

Nice tip, I started to use it from the beginning!

Leave a Comment

Please register or log in to leave a comment.