Written by David Postlethwaite

In a previous blog  I talked about how to audit successful logins. So what about failed logins?

As mentioned before logon Auditing can be configured under server properties. Here you can audit failed logins..

This puts the audit records into the Windows event log but a more useful solution would be to have these failed logins written to a table.

It turns out to be quite simple using the master..xp_ReadErrorLog command which reads from the Windows Event log.

So first let's create a table to store the results

  CREATE TABLE [dbo].[lv_Failed_Logins](

      [Failed_Date] [datetime] NULL,

      [Failed_Userid] [varchar](50) NULL,

      [Failed_Hostname] [varchar](50) NULL,

      [Failed_ServerName] [varchar](100) NULL

  ) ON [PRIMARY]

In this example we will run a scheduled job every night to write the failed logins into a table

First we will cycle the error log using the following command. This makes the current log file an archive file and creates a new empty log.

sp_cycle_errorlog

We will then run the following procedure. This will read the 1^st archive file (the one that we've just created) finding every line that contains the words failed and login and write it to a temporary table.

We then cycle through the temporary table looking for the right substrings to extract the login name and host name and writing them to our failed_logins table.

create procedure [dbo].[sp_lv_Failed_logins]

as

declare @cmdF varchar(max)

declare @search1 char(6)

declare @search2 char(2)

declare @search3 char(8)

declare @search4 char(1)

select @search1 = "user '"

select @search2 = "'."

select @search3 = "CLIENT: "

select @search4 = "]"

select @cmdF = "master..xp_ReadErrorLog 1, 1, 'failed', 'login'"

set nocount on

create table #lv_FailLog(date datetime,processinfo varchar(30),message varchar(4000))

insert #lv_FailLog exec (@cmdF)

insert      lv_monitoring..lv_failed_logins

  select date,

  substring(message,charindex(@search1,message)+6,

  (charindex(@search2,message)) - (charindex(@search1,message)+6)),

  substring(message,charindex(@search3,message)+8,

  (charindex(@search4,message)) - (charindex(@search3,message)+8)),@@SERVERNAME

  from      #lv_FailLog where message like '%user%'

drop table #lv_FailLog

GO

By cycling the error log each night we can ensure that we don't get any duplicates, but if the job fails for any reason then we would lose that day's data.

The major disadvantage is that we only get failed logins for yesterday, never for today.

If we wanted an instant alert of a failed login we would need to use log reader, like MS Operations Manager (SCOM), to pick up the error and report on it.