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
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
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.
- 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
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.
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 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
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
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
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
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
Job Name: Alert-LoginFailureDetected
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
SELECT '*** SendAlert=' + @SendAlert
EXEC msdb.dbo.sp_start_job N'Alert-LoginFailureDetected-Send-Alert'
WAITFOR DELAY '00:00:15' -- Give Send Alert a chance to finish
SELECT '*** Alert Message recently sent. No more checks will be made until ',
dateadd(s, @iTimeRangeInSeconds, @dtLastRun )
- @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
PRINT ' High number of failed login alerts detected'
DECLARE @EmailRecipients varchar(255)
DECLARE @vcSubject varchar(255)
DECLARE @vcMessage varchar(255)
SET @EmailRecipients = 'firstname.lastname@example.org'
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
@profile_name = 'Default',
@recipients = @EmailRecipients,
@subject = @vcSubject,
-- Stored procedure used in SQL Server 2000
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
Click Response in left pane
- Check > Execute Job
- Select Job "Alert-LoginFailureDetected"
- For SQL Server 2000 set "Delay between responses" to 0 seconds
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
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.
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.