By: Daniel Farina | Comments (5) | Related: > Monitoring
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.
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.
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.
Next Steps
- Review Error log management - http://www.mssqltips.com/sqlservertip/1155/sql-server-2005-error-log-management/.
- See how to read SQL server log files with T-SQL - http://www.mssqltips.com/sqlservertip/1476/reading-the-sql-server-log-files-using-tsql/.
- Check out these cateogories of tips:
- Look for other uses of the user settable performance counter.
About the author
Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
