Auditing SQL Login Failures

Auditing SQL for login failures is a great way to check for security issues before they become a problems, I myself have written an app that sits in the icon tray and pops up when a failure is detected, you could also set up a job to email you should a failure become apparent. Setting up SQL for auditing is simply and easy so let’s go through some steps.
First make sure that the auditing feature is turned on (it should be by default, but you never know) so open up management studio, right click on the server node and select properties, when the server properties window appears select the “Security” page and then check the setting shown below is enabled.

If you have to change the setting then make sure that you restart the SQL Service since it only seems to pick up on the changes to this section during startup? Anywho, once you’ve made sure the setting is in affect you can start monitoring the SQL Server logs for these failed events, one way is to check in the xp_readerrorlog stored procedure which will give you a list of all the current server logs so run the following line :

EXEC xp_readerrorlog

This will give you a list of results for current log, if you want to go back further you can check additional logs by adding a integer value so as 0 for current, 1 for previous, 2 for… errr.. more previous.. etc. e.g.

EXEC xp_readerrorlog 1

The xp_readerrorlog procedure has a few more parameters though to narrow our search, the second parameter has two options and allows you to view either the errorlog (1 – default) or the agentlog (2) and the third option allows you to search only for specific text, so if we only wanted to see the failed login attempts we could use this

EXEC xp_readerrorlog 0,1,'Login Failed'

And vola, a list of failed logins 