Get Alerts for Specific SQL Server Login Failed Events

Problem

In a recent MSSQLTips.com question, a user asked how they could get an alert whenever a login failed due to the account being locked out. I thought this was an interesting problem, and immediately suggested some off-the-cuff ideas. Following through on my ideas, however, was not a simple one-step process, as each idea is a little involved.

Solution

As with many problems, there are several ways to solve this issue. My initial suggestions were to set up an alert with SQL Server Agent, set up an audit specification, or to manually consume the error log periodically. I quickly realized that the audit solution was not practical, both because there is no inherent alerting there, and because it requires Enterprise Edition. So my suggestions have been slightly revised to:

  • A SQL Server Agent alert, using the WMI event AUDIT_LOGIN_FAILED
  • Event Notifications, also using AUDIT_LOGIN_FAILED
  • Manually consuming the error log

All three solutions will use SMTP e-mail, via database mail, as the alert mechanism. So, it is assumed that you already have database mail set up, with an active profile and a valid SMTP server configured. If you don’t already have database mail configured, please review this previous tip, “Setting up Database Mail for SQL 2005” – in spite of the title, the steps remain unchanged in SQL Server 2008, 2008 R2 and 2012. Note that in all cases it may take a few minutes before the e-mail arrives in your inbox.

All three solutions will also demonstrate capturing state 5 events (which occur when an attempt from the local machine ). State 10 (account lock-out) is a much tougher one to reproduce, especially in a stand-alone virtual machine. But you should just be able to swap out the state values and otherwise utilize the same solution – no matter which login failed event(s) you’re trying to capture (for a list of login states and what they usually mean, see my blog post, “Troubleshooting Error 18456“).

SQL Server Agent

In order to use an alert that sends an e-mail with accurate information, SQL Server Agent must be running, a mail profile must be enabled, and tokens replacements must be toggled on. Tokens are used to substitute information in the body of a job step, so that – for example – an e-mail alert can contain information such as the server name and the actual error message. You can set these properties (highlighted below) using Management Studio by right-clicking SQL Server Agent, selecting Properties, and moving to the Alert System screen:

SQL Server Agent > Properties > Alert System

You can create an alert that monitors for the WMI event AUDIT_LOGIN_FAILED, and I will show two ways to send an e-mail in response to this event (but only if the state is 5). One is to notify an operator, the other is to create a job.

For the first case, you need to create an operator, then set up the alert, then set up the notification. You can do this with the following script:

USE [msdb]GO
EXEC msdb.dbo.sp_add_operator 
   @name = N'Operator1', 
   @enabled = 1, 
   @pager_days = 0, 
   @email_address = N'username@domain.com', 
   @category_name = N'[Uncategorized]'
DECLARE @namespace NVARCHAR(255)
   = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + COALESCE
   (
       CONVERT(NVARCHAR(32), SERVERPROPERTY('InstanceName')),
       N'MSSQLSERVER'
   
EXEC msdb.dbo.sp_add_alert 
   @name = N'Login Failed : State 5', 
   @message_id = 0, 
   @severity = 0, 
   @enabled = 1, 
   @delay_between_responses = 0, 
   @include_event_description_in = 1, 
   @category_name = N'[Uncategorized]', 
   @wmi_namespace = @namespace, 
   @wmi_query = N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 5'
EXEC msdb.dbo.sp_add_notification
   @alert_name = N'Login Failed : State 5', 
   @operator_name = N'Operator1', 
   @notification_method = 1GO

Note that if you want to monitor multiple states with the same alert, you can change the @wmi_query to:

@wmi_query = N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 5 OR State = 6'

Now if you attempt to log in with an invalid username, you will receive this e-mail:

E-mail resulting from an alert to an operator

The reason I would prefer to create a job in this case is that I can have much more control over the content of the e-mail. If you notice above, the e-mail tells us that a login failed, but doesn’t tell us anything about the actual error message (e.g. the login name, the type of authentication, or the host where the login attempt originated). For more control, you can create a job that uses tokens to translate WMI and other server-level data into an e-mail. You can drop the existing alert as follows:

USE [msdb]GO
EXEC msdb.dbo.sp_delete_alert
   @name = N'Login failed : State 5'

Now here is a sample script to create a job, and then re-create the alert so that it points at the job:

USE [msdb]GO
DECLARE
   @job_id BINARY(16
EXEC msdb.dbo.sp_add_job
   @job_name = N'Mail on login failed : State 5',
   @enabled = 1,
   @description = N'Send e-mail on WMI event',
   @category_name = N'[Uncategorized (Local)]',
   @owner_login_name = N'sa',
   @job_id = @job_id OUTPUT
-- WMI exposes several tokens we can take advantage of:
DECLARE @cmd NVARCHAR(MAX) = N'DECLARE @msg NVARCHAR(MAX) = '
   + '''From job: Login failed for $(ESCAPE_SQUOTE(WMI(LoginName)))'
   + '. Full error message follows:' + CHAR(13) + CHAR(10)
   + '$(ESCAPE_SQUOTE(WMI(TextData)))'';
 EXEC msdb.dbo.sp_send_dbmail
  @recipients = ''username@domain.com'',
  @profile_name = ''default'',
  @body = @msg,
  @subject = ''There was a login failed event '
           + 'on $(ESCAPE_SQUOTE(A-SVR)).'';'
-- msdb is used as the database for the job step; this prevents 
-- any cross-database issues with executing sp_send_dbmail.
EXEC msdb.dbo.sp_add_jobstep
   @job_id = @job_id,
   @step_name = N'Step 1 - send e-mail',
   @step_id = 1,
   @on_success_action = 1,
   @on_fail_action = 2,
   @subsystem = N'TSQL',
   @database_name = N'msdb',
   @command = @cmd
EXEC msdb.dbo.sp_update_job
   @job_id = @job_id,
   @start_step_id = 1
EXEC msdb.dbo.sp_add_jobserver
   @job_id = @job_id,
   @server_name = N'(local)'
DECLARE @namespace NVARCHAR(255)
   = N'\\.\root\Microsoft\SqlServer\ServerEvents\' + COALESCE
   (
       CONVERT(NVARCHAR(32), SERVERPROPERTY('InstanceName')),
       N'MSSQLSERVER'
   
EXEC msdb.dbo.sp_add_alert
   @name = N'Login failed : State 5',
   @enabled = 1,
   @category_name = N'[Uncategorized]',
   @wmi_namespace = @namespace,
   @wmi_query = N'SELECT * FROM AUDIT_LOGIN_FAILED WHERE State = 5',
   @job_id = @job_id

Once the job and alert are set up, you should once again be able to trigger an e-mail by attempting to connect to your SQL Server instance using a login that doesn’t exist. Here is the new e-mail with much more complete information:

E-mail resulting from an alert to a job

In either of these cases, if you don’t get the e-mail after a few minutes, it could be for several reasons. To troubleshoot, check the following things:

  • Double-click the alert in Object Explorer, and check the History tab. If the Event Count is 0, the alert may not be enabled correctly, or something else might be going on. Check the SQL Server error log – if nothing shows up there, then consider trying again after running a profiler trace watching for the Exception event.
  • Check that the mail profile is sending standard e-mails correctly, to the same address as indicated in the operator or the job. You can do this by right-clicking Database Mail in Object Explorer, choosing “Send Test E-Mail…”, and sending an e-mail to the correct address using the same profile. It could be an issue with the SMTP server, the database mail subsystem, or the recipient’s mailbox. Try a different recipient if none of the other avenues pan out.
  • For the job solution, check that SQL Server Agent is enabled (if the server has restarted, Agent may be set to start manually), that the job is enabled, and view the job history – the job may have started, but failed for other reasons.

Event Notifications

My good friend Jonathan Kehayias (@SQLPoolBoy) helped out a great deal in my understanding of Event Notifications, and helped me work through this example. At a high level, Event Notifications is a lightweight architecture allowing you to respond to certain DDL and trace events using Service Broker. Since it uses the same underlying architecture as the WMI alerts, we can use the same AUDIT_LOGIN_FAILED event to capture these events as they happen. First we need to set up a queue, a service, and the event notification itself:

USE [msdb]GO
CREATE QUEUE FailedLoginNotificationQueueGO
CREATE SERVICE FailedLoginNotificationService
    ON QUEUE FailedLoginNotificationQueue 
   ([http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]GO
CREATE EVENT NOTIFICATION FailedLoginNotification
    ON SERVER WITH FAN_IN
    FOR AUDIT_LOGIN_FAILED
    TO SERVICE 'FailedLoginNotificationService', 'current database'GO

Now we can create the activation procedure that will get called whenever one of these AUDIT_LOGIN_FAILED events occurs. We have to use RECEIVE to pull any new events off the queue, and some XQuery magic to parse the XML event data, but otherwise the logic is pretty straightforward. Again we’re going to use msdb to avoid cross-database issues with calling sp_send_dbmail:

USE [msdb]GO
CREATE PROCEDURE [dbo].[ProcessFailedLoginEvents]
WITH EXECUTE AS OWNER
AS 
BEGIN
   SET NOCOUNT ON
   DECLARE
       @message_body XML,
       @message NVARCHAR(MAX),
       @subject NVARCHAR(255) = 'There was a login failed event on ' + @@SERVERNAME
   WHILE (1 = 1)
   BEGIN
       WAITFOR 
       (
           RECEIVE TOP(1) @message_body = message_body
               FROM dbo.FailedLoginNotificationQueue
       ), TIMEOUT 1000
       IF (@@ROWCOUNT = 1)
       BEGIN
           IF (@message_body.value('(/EVENT_INSTANCE/State)[1]', 'int') = 5)
           BEGIN
               SELECT @message = 'From Event Notification: Login failed for user '
                   + @message_body.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(128)' )
                   + '. Full error message follows:' + CHAR(13) + CHAR(10)
                   + @message_body.value('(/EVENT_INSTANCE/TextData)[1]', 'varchar(4000)'  
               EXEC msdb.dbo.sp_send_dbmail
                   @profile_name = 'default', 
                   @recipients = 'username@domain.com',
                   @subject = @subject,
                   @body = @message           END
       END
   END
END
GO
ALTER QUEUE FailedLoginNotificationQueue
WITH ACTIVATION
(
   STATUS = ON,
   PROCEDURE_NAME = [dbo].[ProcessFailedLoginEvents],
   MAX_QUEUE_READERS = 1,
   EXECUTE AS OWNER
GO

Note that if you want to capture multiple events, you can just change the IF conditional as follows:

 IF (@message_body.value('(/EVENT_INSTANCE/State)[1]', 'int') IN (5, 6))

With the queue activated, you can again generate an e-mail by attempting to login with invalid credentials. You should see the following e-mail:

E-mail resulting from an Event Notification

Like above, if you don’t get the e-mail after a few minutes, it may be because there is a problem with database mail or the SMTP server, but it may also be that the activation procedure is not firing. One step to diagnose this would be to add simple logging to the activation procedure. Remus Rusanu also has some troubleshooting tips.

Parsing the SQL Server error log

This is my least favorite solution, but it’s probably the easiest among these three to grasp and to troubleshoot. Basically we’re just going to look at the error log every n minutes, check to see if there are any new login failed / state 5 events since the last time we checked, and send an e-mail. First we need a simple table to log the current date/time (we’ll start with it being 1900-01-01 so that we capture all login failed events on first run):

USE [msdb]GO
CREATE TABLE dbo.LastCheck
(
   [Date] SMALLDATETIME
GO
INSERT dbo.LastCheck SELECT '19000101'GO

Now the following stored procedure first determines when this check was last made, then dumps the current error log into a #temp table, and removes any rows from before the last check. If any remain, it joins the two parts of the error message for each login event (we know that they get recorded with the same timestamp, and the possibility of other collisions are highly unlikely), assembles an e-mail message, sends it off, then updates the last check.

USE [msdb]GO
ALTER PROCEDURE dbo.CheckForFailedLoginEvents
WITH EXECUTE AS OWNER
AS
BEGIN
   SET NOCOUNT ON
   DECLARE 
       @LastCheck DATETIME, 
       @now       DATETIME = SYSDATETIME(),
       @message   NVARCHAR(MAX) = N'From manual labor: Login failed event(s).',
       @subject   NVARCHAR(255) = N'Login failed event(s) on ' + @@SERVERNAME
   SELECT 
       @LastCheck = [Date] FROM dbo.LastCheck
   CREATE TABLE #t
   (
       LogDate DATETIME,
       ProcessInfo VARCHAR(50),
       [Text] NVARCHAR(4000)
   );
   INSERT INTO #t(LogDate, ProcessInfo, [Text])
       EXEC MASTER..xp_readerrorlog  0, 1, N'State: 5'
   INSERT INTO #t(LogDate, ProcessInfo, [Text])
       EXEC MASTER..xp_readerrorlog  0, 1, N'Login Failed'
   DELETE #t WHERE [LogDate] < @LastCheck
   IF EXISTS (SELECT 1 FROM #t)
   BEGIN
       SELECT @now = DATEADD(SECOND, 1, MAX([LogDate])) FROM #t 
       SELECT 
           @message += CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10) 
           + CONVERT(CHAR(10), e.LogDate, 120) + ' ' 
           + CONVERT(CHAR(8), e.LogDate, 108) + CHAR(13) + CHAR(10) 
           + e.[Text] + CHAR(13) + CHAR(10) + x.[Text]
       FROM #t AS e INNER JOIN #t AS x
       ON e.LogDate = x.LogDate
       WHERE e.[Text] LIKE 'Error%' AND x.[Text] LIKE 'Login%'
       ORDER BY e.LogDate, e.[Text]
       EXEC msdb.dbo.sp_send_dbmail
           @profile_name = 'default', 
           @recipients = 'username@domain.com',
           @subject = @subject,
           @body = @message   END
   DROP TABLE #t
   UPDATE dbo.LastCheck SET [Date] = @nowEND
GO
EXEC dbo.CheckForFailedLoginEventsGO

A simple execution yields the following e-mail:

E-mail resulting from manual stored procedure call

Since the dbo.LastCheck table was updated with the last instance that was reported, executing the stored procedure again immediately should not yield another e-mail (unless your server is being hammered with invalid login requests).

Now, you can schedule this stored procedure as a separate job, as part of some other maintenance job, or just run it manually – and you can run it as frequently or infrequently as you like.

Conclusion

I’ve shown three ways to receive an e-mail alert when logins fail with a certain state (or states). I think they each have their merits, depending on the practices and policies already in place in your environment, and your familiarity with the different technologies used. You should be able to expand any of these solutions to cover other login failed events, in addition to any WMI or DDL events, or events that appear in the SQL Server error log. There are also 3rd party products out there that can help with this; for example, Jason Hall blogged about how SQL Sentry Event Manager can be used to capture and display WMI event alerts.

Next Steps

Related Posts

Leave a Reply

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