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

Auditing SQL Logins Part II

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..

 

AuditLoginsII

 

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 1st 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.


Comments

Posted by lianvh on 16 August 2011

Gethyn,

thank you for this lifesaver. But I have a question. PCI DSS requires to run certain software to test for security vulnerabilities and these failed login attempts are also recorded and we should be notified via sms . Will this not give incorrect reports ? Meaning the recording of failed attempts are pointless ?

Posted by David Postlethwaite on 17 August 2011

If I understand correctly, you have software that attempts to login to see if it can break the server and you don’t want to record those failures.

If those attempts use the same login name or are from the same host name then it would be possible to alter query to ignore those records.

Leave a Comment

Please register or log in to leave a comment.