By: Aaron Bertrand | Updated: 2012-01-17 | Comments (15) | Related: > Security
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:
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'[email protected]',
@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 = 1;
GO
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:
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 = ''[email protected]'',
@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:
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 FailedLoginNotificationQueue;
GO
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 = '[email protected]',
@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:
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 = '[email protected]',
@subject = @subject,
@body = @message;
END
DROP TABLE #t;
UPDATE dbo.LastCheck SET [Date] = @now;
END
GO
EXEC dbo.CheckForFailedLoginEvents;
GO
A simple execution yields the following e-mail:
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
- Decide which login events you want to monitor, and which approach you want to implement.
- Test your solution by attempting various login attempts that will trigger your alert.
- Review the following tips and other resources:
About the author
Aaron Bertrand (@AaronBertrand) is a passionate technologist with industry experience dating back to Classic ASP and SQL Server 6.5. He is editor-in-chief of the performance-related blog, SQLPerformance.com, and also blogs at sqlblog.org.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2012-01-17
