http://www.sqlservercentral.com/blogs/steve_jones/2010/05/28/auditing-failed-logins-_1320_-sql-server-2008/ Printed 2015/08/28 08:59AM
Auditing Failed Logins – SQL Server 2008I 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”
That gives you a dialog where you can add a name:
And then select an audit to which you assign this particular detail.
For my purposes, I need to audit failed logins, so I select that change group. You can get all change groups in BOL.
Once this is done, your audit appears in the folder in SSMS.
Now when someone tries to log in and can’t, you can view this in the logs: