Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

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


By:   |   Last Updated: 2013-08-26   |   Comments (5)   |   Related Tips: More > 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.

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 @[email protected], @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 @[email protected], @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


Last Updated: 2013-08-26


next webcast button


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





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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

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

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

Daniel - Very helpful!  TY


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

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
 USAGE:
   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
begin
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
End
if @@error = 0
Begin
 /*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'
END
 --else
--end
if @@error = 0
 Begin
declare @cmdstr varchar(255)
--truncate table sqlerrorlogs
SELECT *
FROM dba..sqlerrorlogs
WHERE events LIKE '%Login Failed%'
if @@rowcount <> 0 and @@error = 0
begin
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

end
 
  else 
return
End
GO


Learn more about SQL Server tools