Alerts for when Login Failures Strike

By Guest Authors on 14 July 2008 | Tags: Administration , Security

When repeated SQL Server login failures occur, a DBA should investigate. It could just be someone repeatedly typing in the wrong password. Worst case is a virus attack flooding your network with connection requests. Receiving an e-mail while login failures are occurring allows DBAs to investigate and fix the issue as soon as possible. So how is DBA notified of login failures without flooding their inbox?

A limit on how often a response is executed is controlled by the setting "delay between responses". This setting only limits how often an alert can execute a response. An occasional login failure is not uncommon. I prefer to receive an alert message only after several login failures occur within a specified length of time. These additional criteria require the use of a stored procedure and two SQL agent jobs.

The first job is started as a response for a login failure alert. After detecting repeated failures, it starts a second job that sends an e-mail alerting the DBA team of the problem. Then it changes the alerts "delay between responses" setting from zero seconds to a specified time. In addition, before checking login failures, it sees if a previous e-mail alert was sent. If it was sent within the specified time, it will not send another until the time has expired. This keeps notifications to a minimum.

Continually receiving an e-mail about the same issue is distracting especially when trying to investigate the issue. By controlling how often an alert is sent, provides you time to investigate the issue before receiving more messages. Using e-mail to send the message permits the use of e-mail addresses for individuals, groups, and pagers. This allows you to control who will receive them and on which device they will be notified.

This article includes all the steps for setting up a login failure alert for repeated occurrences. Instructions are provided for SQL Server 2000 and 2005. The only difference between them is the stored procedure used to send the e-mail.

Setup

Event Log
Instance Setting
Create Database
E-mail Setup
Login Failure Stored Procedure
Create Job - Alert-LoginFailureDetected
Create Job - Alert-LoginFailureDetected-Send-Alert
Define Alert
Testing

Event Log

Event log service and SQL Server Agent must be running.

Failed logins are written to the windows application log by the event log service. SQL Server Agent reads the windows application log. It then compares the events in the log with the SQL Server alerts. A response is triggered when both a matching alert is found and its "wait between responses" setting has been reached.

Instance Setting

Enable Audit Failed Login if not already done so.

Right-click the instance > Properties > Security
Login Auditing > Fail Logins Only > Enabled
Click OK

By default, it is enabled on SQL Server 2005 but it should be verified.

Restart the instance to activate it. You might need to schedule an off-hour time to perform this restart.

Create Database

Create the Admin database with recovery mode simple.

Whenever possible you should create user objects in a database other than MASTER. By keeping them separate from MASTER, you prevent any conflict with system objects of the same name. Moreover, you make them recoverable through a simple database restore.

In this article, the database Admin will contain a table for tracking login failures and a stored procedure (or two depending on the version of SQL Server).

E-mail Setup

Use e-mail to deliver alert messages. The recipient's addresses can be an e-mail account, pager, or e-mail group (preferred choice). The database mail profile named "Default" is used by the SQL Server 2005 send mail job in this article.

If e-mail has already been setup on the instance, you can skip this step.

SQL Server 2005 instances use Database Mail. For assistance in setting this up refer to SQL Server books online or this article.

SQL Server 2000 instances use a CDO stored procedure to send e-mail. A file attached to this article contains a sample CDO store procedure 'usp_cdosendmail'. In the stored procedure code, change the SMTPSERVER.COM value to your company's SMTP server. Create it in the Admin database. Then test this store procedure by sending yourself an e-mail.

Login Failure Stored Procedure

Create the stored procedure 'usp_AlertLoginFailed' in the Admin database. The SQL for creating this stored procedure is in this article's file attachment.

This stored procedure counts all login failures during a specified length of time and sets a flag when an e-mail alert needs to be sent. After setting the flag, the alert will ignore any additional login failures for that same specified length of time. This prevents an alert from repeated starting the job 'Alert-LoginFailureDetected.

When executed, this stored procedure will create and populate the table 'dbo.tblAlertLoginFailed' in the Admin database. It will contain a history of the login failures that triggered the alert. By counting the rows in this table for a specified time, the stored procedure decides whether to an e-mail is required or not. There are two parameters used to customize its execution in each instance. For a list of these a parameters and their descriptions, see the Alert Job 'Alert-LoginFailureDetected' below.

Creat Job - Alert-LoginFailureDetected

This job executes the stored procedure 'usp_AlertLoginFailed' when the alert is triggered. After calling the stored procedure, the variable @SendAlert returns the value 'YES' when an e-mail needs to be sent. This allows additional code to be executed by the job after an e-mail is sent. For example, you might want to start up a SQL Profile trace to capture additional login information. Instructions for creating and executing a SQL Profile trace are out of scope for this article.

The sample SQL checks for the last time the job 'Alert-LoginFailureDetected-Send-Alert' was used to send an e-mail. If it has not been sent within the specified time, the stored procedure is called to determine if an e-mail alert message should be sent. This SQL starts up the e-mail alert job when three login failures are detected within the last 60 minutes. No additional e-mail alerts will be sent for the next 60 minutes.

Job Name: Alert-LoginFailureDetected

Command:

DECLARE @SendAlert char(3)
DECLARE @iTimeRangeInSeconds int

SET @iTimeRangeInSeconds = 3600


-- Get the last time the alert message was sent
DECLARE @dtLastRun datetime
SELECT top 1 @dtLastRun = 
       CAST(CAST(run_date AS char(8)) + ' ' +      -- Convert run_date to DateTime data type
        STUFF(STUFF(                                  -- Insert : into Time
         RIGHT('000000' +                             -- Add leading Zeros
          CAST(run_time AS varchar(6))  ,6)  , 3, 0, ':'), 6, 0, ':') AS datetime)     
  FROM msdb.dbo.sysjobs A, msdb.dbo.sysjobhistory B 
 WHERE A.job_id = B.job_id AND B.run_status = 1
   AND A.name = 'Alert-LoginFailureDetected-Send-Alert'
 ORDER BY 1 DESC

-- Check if the last alert sent was within the specified time frame
SELECT 'Job Last Run Date', @dtLastRun 

IF @dtLastRun  is null OR
    dateadd(s, -@iTimeRangeInSeconds, current_timestamp ) > @dtLastRun 
BEGIN

 EXEC admin.dbo.usp_AlertLoginFailed 
         @iFailureCount=3,
         @iTimeRangeInSeconds=@iTimeRangeInSeconds ,
         @SendAlert=@SendAlert  OUTPUT
 
  SELECT '*** SendAlert=' + @SendAlert 
  IF @SendAlert='YES'  
   BEGIN
    EXEC msdb.dbo.sp_start_job N'Alert-LoginFailureDetected-Send-Alert' 
    WAITFOR DELAY '00:00:15'     -- Give Send Alert a chance to finish
   END

END

ELSE
 BEGIN
  SELECT '*** Alert Message recently sent. No more checks will be made until ', 
    dateadd(s, @iTimeRangeInSeconds, @dtLastRun ) 
 END

Parameters:

@iFailureCount - Send e-mail when this number of login failures occurred within the specified time range.
@iTimeRangeInSeconds - It has two uses. First, it specifies how many seconds in the past to check for login failures. Second, after sending an e-mail, update the alert setting "delay between responses" to this value. The alert will then wait for the specified number of seconds before responding to additional login failures.
@EmailRecipients - List each e-mail address that will receive the message and separate each address by a comma.
@SendAlert - This variable returns the value "YES" after sending an e-mail. Use this variable to execute other stored procedures or jobs in response to repeated login failures.

Create Job - Alert-LoginFailureDetected-Send-Alert

This job is started by the job 'Alert-LoginFailureDetected' when an e-mail needs to be sent to the DBA team alerting them of repeated login failures. The SQL contains the e-mail stored procedures used for both SQL Server 2000 and 2005. Delete the one you do not need. Set the variable @EmailRecipients to your DBA teams e-mail address.

Create Job: Alert-LoginFailureDetected-Send-Alert

Command:

PRINT ' High number of failed login alerts detected'
DECLARE @EmailRecipients varchar(255)
DECLARE @vcSubject varchar(255)
DECLARE @vcMessage varchar(255)


 SET @EmailRecipients  = 'myteam@yourdomain.com'
 SET @vcSubject = @@ServerName + ': High Volume of login Failure Alerts Detected'
 SET @vcMessage = 'Please check the event logs for the user login that attempted to login.'

 PRINT 'Alert E-mail sent '
 
 -- Stored procedure used in SQL Server 2005 
 EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'Default',
    @recipients = @EmailRecipients,
    @subject = @vcSubject,
    @Body =@vcMessage

 -- Stored procedure used in SQL Server 2000
 EXEC admin.dbo.usp_cdosendmail
      @From =@@SERVERNAME,
      @To =@EmailRecipients,
      @Subject =@vcSubject,
      @Body =@vcMessage

Parameters:

@EmailRecipients - List each e-mail address that will receive the message and separate each address by a comma.

Define the Alert

Start up SQL Server Agent if it is not running.

Open the plus to the left of SQL Server Agent

Right-click Alerts > select New Alert

Enter or select the following alert settings

On General left pane

Name: Login Failed
Severity: 014 - Insufficient Permission

Click Response in left pane

Check > Execute Job
Select Job "Alert-LoginFailureDetected"
For SQL Server 2000 set "Delay between responses" to 0 seconds

Click OK

Testing the Alert

The alert is now active and monitoring for login failures. Test this alert by generating login failures. Connect to the instance using an invalid SQL login. After three login failures, you should receive an e-mail. Then no more e-mails for another 60 minutes.

Create this alert on each instance you want real-time notification of login failures.

Conclusion

Now when a user or connection command is using the wrong id or password you are ready. This alert will keep you informed when repeated login failures occur without flooding your e-mail inbox or pager with messages. I hope that you will never experience a SQL Server login attack but now you will know about it while it is happening.

Resources

Download Scripts

About the Author

For the past five years, David Bird has been primarily working as a SQL Server DBA. He has over 20 years of IT experience working as a programmer or DBA on mainframes, UNIX, and Windows. Being a programmer at heart, David is always looking for ways to automate tasks whenever possible.