The Case for Auditing Your SQL Server Login Failures

Login failures are something we expect to see every once in a while, and it may even become something that we ignore completely unless we have some sort of auditing system to count exactly how many have occurred recently. I am writing this post to provide you a few tips and tricks when it comes to login failures on your SQL Server instance and to help build peace of mind that the login failures that are occurring are resolved or addressed as soon as possible.

There are many ways to monitor and audit log in failures in SQL Server, the primary tool I would recommend is the built in logging to the error log for login failures – you can get to it through the security tab of the server properties GUI. We typically see and recommend logging failed logins only.

If you need to make this change, please keep in mind that you will need to restart your SQL service before it is implemented. Once your login failures are being captured by the error log, it is relatively easy to build a script and then automations to determine if there is an issue that should be looked into further.

This script below provides an aggregated list of all the log in failures from the past 6 error logs, grouping by distinct login names and login sources. It also provides the count of recent failures as well as the min and max range of the failures.

IF OBJECT_ID('tempdb..#results') IS NOT NULL
	DROP TABLE #results;

CREATE TABLE #results (
	logdate datetime,
	Processinfo NVARCHAR(30),
	text NVARCHAR(200),
	);

DECLARE @count tinyint = 0;

WHILE (@count <=5)
BEGIN
	INSERT INTO #results
	EXEC sp_readerrorlog @count, 1, 'Login failed';
	Set @count = @count +1
END


SELECT DISTINCT text
, COUNT(text) as num_login_failures
, MAX(logdate) as max_range
, MIN(logdate) as min_range
FROM #results 
GROUP BY text
ORDER BY text

You can modify the script to meet your needs, one beneficial route you could take could be to add a where clause like ‘WHERE text NOT LIKE ‘%XXX.XXX.%’ where the XXX.XXX is your default subnet mask. This is a relatively quick way to identify whether your SQL instance is open to the internet, as anything outside of your default IP range shouldn’t be possible otherwise. The way I would use this script is to periodically audit what logins are failing, how many, from what server, and when. These questions can all help paint a picture to show if a login may be compromised, is being misused, or if it was forgotten and just left on a deprecated process running forever.

In conclusion, login errors can go unnoticed unless you have a tool that automatically points them out to you. Using this blog post you should have the beginnings of a manual process to regularly review login failures, and turning this into a weekly email would only require some minor modifications. Please be sure to audit your login failures, as they can tell a story for what is happening in your environment.