Configuring a SQL Server Email Alert for SA Login Failures

SA Login 

SA Login authentication failures, essentially, should never happen. Mostly that's because we generally shouldn't be using the SA account, but rather should have dedicated application logins with appropriate privileges. Having those logins use Windows authentication instead of SQL authentication further removes SQL Server from the authentication process, which is even better.

So if the SA account isn't disabled, I want to know when there's an SA login failure.
[Generally, I want to know when there's an SA login success, as well, but we'll focus on the failures, here, because it's really low-hanging fruit.]

SQL Server Alerts

SQL Server Alerts are a very light-weight notification tool for lots of SQL Server events.  Brent Ozar has a nice run-down of some basic alerts that you ought to consider implementing; we'll add this one as another.

It's possible--and frequent--to address this problem by using login triggers.  That is certainly a valid, functional, and well-worn method.  Login triggers can introduce problems, though, especially when we have forgotten that they exist.  Moreover, this is what alerts are for, and there's something appealing to the simplicity of it all.

First, some alerts basics

Alerts use the SQL Server Agent to do their work, so it must be said at the outset that if you're using SQL Express, or if you don't have the SQL Server Agent configured, they won't work.

Alerts fire off of event IDs and severities.  So you can generate an alert for any event that shows up in the SQL error log.  Using alerts is much, much better than reading and parsing a log file using xp_readerrorlog.

Below you can see the GUI for defining an alert (we'll look at a script further down).  You can see that you can specify databases, error numbers, a severity, and a substring filter.

SA Login Failure Alert Specifics

In our case, we want to raise an alert on error number 18456, which is a login failure.  We also are only interested in SA logins.  Because this is a substring match (like '%sa%'), if we simply put SA in this field, it'll also match on login failures for the login 'FDSA_User', or any login that has 'SA' in its name.

Happily, the error text puts the login name in single quotes, so we can match on 'sa' (like '%''sa''%').  This will limit our login failure alerts to just the SA login.

SQL Alert Script

Here is what a SQL script to accomplish this will look like:

--Login Failure Alert
EXEC msdb.dbo.sp_add_alert @name=N'Login failure alert - SA', 
  @message_id=18456, 
  @severity=0, 
  @enabled=1, 
  @delay_between_responses=30, 
  @include_event_description_in=1, 
  @event_description_keyword=N'''sa''', 
  @category_name=N'[Uncategorized]', 
  @job_id=N'00000000-0000-0000-0000-000000000000';
GO

And to add a notification email to this alert, we'd do the following:

EXEC msdb.dbo.sp_add_notification @alert_name=N'Login failure alert - SA',
      @operator_name='DBAs_oncall',
      @notification_method = 1;
GO

The above assumes there is an operator with the name of 'DBAs_oncall'.  You'd want to change that to match an operator already defined for your SQL Server Agent.