Real time auditing of failed SQL Server logins with user settable performance counters in Performance Monitor

By:   |   Updated: 2013-08-26   |   Comments (5)   |   Related: More > Monitoring

Is the Database the Culprit of Your Application Issues?

Free MSSQLTips Webinar: Is the Database the Culprit of Your Application Issues?

When you're troubleshooting application performance issues, have you ever found the problem residing a few layers deep in the database or not at all? How long did it take you to find and fix the issue? Don't worry if you said "a while". Learn how to solve performance problems fast.


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.


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 

 DECLARE @FailedCount  INT
  ID    INT IDENTITY(1,1),
  LogDate   DATETIME,
  ProcessInfo  VARCHAR(50),
  TextMsg   VARCHAR(1000),
 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 

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]

/****** Object:  Job [Failed Login Audit Performance Counter]   ******/
-- 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)
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
EXEC @ReturnCode =  msdb.dbo.sp_add_job @job_name=N'Failed Login Audit Performance Counter', 
  @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 @[email protected], @step_name=N'Step1', 
  @os_run_priority=0, @subsystem=N'TSQL', 
  @command=N'EXEC AuditFailedLoginsUserCounter NULL', 
  @database_name= @Database, 
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 @[email protected], @name=N'Failed Login Audit Performance Counter Schedule', 
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
GOTO EndSave

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
 EXEC master.dbo.xp_cmdshell 'sqlcmd -S SERVER2008 -U sa -P 1234' , NO_OUTPUT
 SET @FailedCount = @FailedCount - 1

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

Last Updated: 2013-08-26

get scripts

next tip button

About the author
MSSQLTips author Daniel Farina Daniel Farina was born in Buenos Aires, Argentina. Self-educated, since childhood he showed a passion for learning.

View all my tips
Related Resources

Comments For This Article

Monday, December 23, 2013 - 5:15:52 PM - Daniel Farina Back To Top (27873)

Hi Bob,

You can add this counter in "Performance Monitor" for real time monitoring of failed logins. Also there are data collector for performance counters from software vendors that can generate a report about this counter at a given interval.


Best Regards!

Monday, December 23, 2013 - 10:01:38 AM - bob Back To Top (27871)

OK, this is nice for parsing what is already collected by SQL Server, but where's the ALERT?  How do I setup an alert to notify the DBA that there have been x multiple "Failed Login" attempts in x minutes ??


Wednesday, August 28, 2013 - 12:32:30 PM - Daniel Farina Back To Top (26512)

Thanks both of you!
Ed, nice script you have done! I get nostalgic when I remember the DOS days.
But I must confess that my goal with this article was to show a possible way to use the user settable object performance counter

Wednesday, August 28, 2013 - 9:21:58 AM - Richnet Back To Top (26503)

Daniel - Very helpful!  TY

Monday, August 26, 2013 - 10:25:13 AM - Ed - sqlscripter Back To Top (26483)

Great article Dan, not try not to laugh bu this is the "old school" way of doing this noted by the time stamp I wrote it. The .vbs file is nothing but a CDOSYS cal to send a alert.

CREATE proc sp_dba_findFailedLogin1 AS
Edward J Pochinski III 07/02/01
   This procedure gets scheduled and uses a dos function FINDSTR to parse
   the sql error log and bulk insert to a table. When the table is checked
   for any rows of data using the @@rowcount and a .exe vb file is called that 
   calls the cdonts mail object that uses a ADO custom function for the mail
   body where the ADO performs a query and formats the result set in a HTML
   table sent via email to the designated recipient. Last we use dos to rid of
   the txt file that may contain any failed login data and truncate the table.
   Also cycle the error log so next day when alert is kicked off only new data
   will be sent.
   You must have the DBA database created and a table to hold the error log data,
   auditing enabled, failed logins and either SMTP service working
   to send email or a third party tool such as free asp mail program. Hint: you
   only need a specific dll in the path and registered for mail to be sent and
   a valid smtp server with the 3rd party tool.

   Table Code: CREATE TABLE [dbo].[SQLErrorLogs] (
            [Events] [varchar] (8000) NULL
                   ON [PRIMARY]
   Email: [email protected]
declare @cmd1 varchar(255),@cmd2 varchar(255), @cmd3 varchar(255)
if @@error = 0
set @cmd1 = 'FINDSTR /I /C:"Login Failed" "D:\Program Files\Microsoft SQL Server\MSSQL\Log\errorlog" >D:\dbatools\dbsecurity\failedlogins.txt'
exec master..xp_cmdshell @cmd1
if @@error = 0
 /*DTS was unreliable for this task
 set @cmd2 = ' dtsrun.exe /Ssbr1047 /E /Ngetfailedlogin /R'
 exec xp_cmdshell @cmd2
bulk insert dba..sqlerrorlogs from 'D:\dbatools\dbsecurity\failedlogins.txt'
if @@error = 0
declare @cmdstr varchar(255)
--truncate table sqlerrorlogs
FROM dba..sqlerrorlogs
WHERE events LIKE '%Login Failed%'
if @@rowcount <> 0 and @@error = 0
Print ' Yeah it worked Sending email alert '
 SET @cmdstr = 'D:\dbatools\dbsecurity\vbs_cdo_sys_failedlogins.vbs' 
 EXEC master..xp_cmdshell @cmdstr

 --SET @cmd2 = 'DEL D:\dbatools\dbsecurity\failedlogins.txt' 
 --EXEC master..xp_cmdshell @cmd2

 Truncate table dba..sqlerrorlogs

 Exec sp_cycle_errorlog



Recommended Reading

How to Read Log File in SQL Server using TSQL

How to setup SQL Server alerts and email operator notifications

SQL Server Wait Stats Monitoring with PowerShell

Posting SQL Server Notifications to Slack

SQL Server High CPU Query Use Monitoring with PowerShell

get free sql tips
agree to terms

Learn more about SQL Server tools