Real Time Auditing Failed SQL Server Logins with Performance Monitor

Problem

In some circumstances you will need to raise an alert when there are repeated SQL Server login failures.  For example, let’s say we need to be notified of a potential brute force attack on our SQL Servers. See how you can address this need with a User Settable Object Performance Counter in Performance Monitor.

Solution

In Performance Monitor, have you ever asked yourself what are the User Settable object Performance Counters and how can I use them? In this tip, I will show you how to audit failed logins with these parameters in Performance Monitor.

User Settable Object Performance Counter in Performance Monitor

The User Settable object allows you to create custom performance counter instances. It contains 10 instances of the query counter: User counter 1 through User counter 10. These counters map to the SQL Server stored procedures sp_user_counter1 through sp_user_counter10. As these stored procedures are executed by user applications, the values set by the stored procedures are displayed in System Monitor. A counter can monitor any single integer value.

Something to keep in mind is that these counters aren’t automatically pooled by the OS. Instead you have to change their values by calling the sp_user_counter# stored procedure, where # is a number from 1 to 10.

User settable object Performance Counter view

Configuring SQL Server to Audit for Failed Logins

In order to audit failed logins, you have to configure login auditing in the server properties security page.

Configure login audit

Setting the counter for master.dbo.sp_user_counter1

I have created a script that sets the value of User counter 1 by calling sp_user_counter1 and passing the number of failed logins. This script only reads the current error log because its purpose is the real time monitoring of failed logins, so I think that is useless to check for historical data. But remember, SQL Server switches the error log on every service restart, so you may want to handle this issue on the script. Here is a clue: use the sp_enumerrorlogs system stored procedure to find the log numbers by date.

— =============================================

— Author:  Daniel Farina

— Create date: 07/26/2013

— Description: Reads Error log to find the number of

—  failed logins and sets sp_user_counter1 with that value.

—

— @LogDate: Date to start search. Default NULL = No limit

— =============================================

CREATE PROCEDURE AuditFailedLoginsUserCounter

@LogDate  DATETIME = NULL

AS

BEGIN


 SET NOCOUNT ON;


 DECLARE @FailedCount  INT


  CREATE TABLE #ErrorLog

 (

  ID    INT IDENTITY(1,1),

  LogDate   DATETIME,

  ProcessInfo  VARCHAR(50),

  TextMsg   VARCHAR(1000),

  CONSTRAINT PK1 PRIMARY KEY CLUSTERED (ID)

 )


 INSERT INTO #ErrorLog

 EXEC master.dbo.sp_readerrorlog 0, 1, ‘Login failed’ 


 SELECT  @FailedCount = COUNT(0)

   FROM #ErrorLog

   WHERE @LogDate IS NULL OR LogDate >= @LogDate 


 EXEC master.dbo.sp_user_counter1 @FailedCount


 DROP TABLE #ErrorLog

END

GO

Configure a SQL Server Agent Job to Monitor for Failed Logins

Remember that you have to manually update the counter value, so in order to achieve that we must create a SQL Server Agent Job that executes the stored procedure. I have configured the job to run every half hour.  In order to run this script you will need to change the ‘SELECT @Database = N’MyDB” parameter to your database.

— =============================================

— Author:  Daniel Farina

— Create date: 07/26/2013

— Description: Create job to set User Settable Performance Counter

— with [AuditFailedLoginsUserCounter] Stored Procedure

— =============================================

USE [msdb]

GO


/****** Object:  Job [Failed Login Audit Performance Counter]   ******/

BEGIN TRANSACTION

DECLARE @ReturnCode INT

DECLARE @Database NVARCHAR(50)

— SET @Database to the database in wich the stored procedure AuditFailedLoginsUserCounter is.

SELECT @Database = N’MyDB’

SELECT @ReturnCode = 0


/****** Object:  JobCategory [[Uncategorized (Local)]]]  ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]’ AND category_class=1)

BEGIN

EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N’JOB’, @type=N’LOCAL’, @name=N'[Uncategorized (Local)]’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

END

DECLARE @jobId BINARY(16)

EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N’Failed Login Audit Performance Counter’,

  @enabled=1,

  @notify_level_eventlog=0,

  @notify_level_email=0,

  @notify_level_netsend=0,

  @notify_level_page=0,

  @delete_level=0,

  @description=N’Sets sp_user_counter1 to the number of failed logins audit’,

  @category_name=N'[Uncategorized (Local)]’,

  @owner_login_name=N’sa’, @job_id = @jobId OUTPUT

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback


/****** Object:  Step [Step1]  ******/

EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N’Step1′,

  @step_id=1,

  @cmdexec_success_code=0,

  @on_success_action=1,

  @on_success_step_id=0,

  @on_fail_action=2,

  @on_fail_step_id=0,

  @retry_attempts=0,

  @retry_interval=0,

  @os_run_priority=0, @subsystem=N’TSQL’,

  @command=N’EXEC AuditFailedLoginsUserCounter NULL’,

  @database_name= @Database,

  @flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, @name=N’Failed Login Audit Performance Counter Schedule’,

  @enabled=1,

  @freq_type=4,

  @freq_interval=1,

  @freq_subday_type=4,

  @freq_subday_interval=30,

  @freq_relative_interval=0,

  @freq_recurrence_factor=0,

  @active_start_date=20130728,

  @active_end_date=99991231,

  @active_start_time=0,

  @active_end_time=235959,

  @schedule_uid=N’00da2068-560f-449a-a1cb-3b014b723e73′

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)’

IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

COMMIT TRANSACTION

GOTO EndSave

QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

EndSave:

GO

Test the SQL Server Failed Login Process

First, configure Performance Monitor to capture the data for the SQLServer:User Settable Counter 1.

Here is a test script that connects with wrong credentials to show you the counter in action. Update this script to have your development SQL Server name for the server parameter i.e. ‘-S SERVER2008 ‘ for sqlcmd.  Run this code to generate data in Performance Monitor.

DECLARE @FailedCount INT

SET @FailedCount = 5

WHILE @FailedCount > 0

BEGIN

 EXEC master.dbo.xp_cmdshell ‘sqlcmd -S SERVER2008 -U sa -P 1234’ , NO_OUTPUT

 SET @FailedCount = @FailedCount – 1

END

And here you have a screen capture from Performance Monitor of the User Settable performance counter showing the failed logins count.

User counter with failed logins

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *