http://www.sqlservercentral.com/blogs/steve_jones/2010/05/28/auditing-failed-logins-_1320_-sql-server-2008/

Printed 2014/08/23 04:08AM

Auditing Failed Logins – SQL Server 2008

By Steve Jones, 2010/05/28

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

Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.