SQL Server Blog

The Case for Auditing Your SQL Server Login Failures

This post is part of our SQL Server security blog series, 30 SQL Server Security Checks in 30 Days. We’re publishing a new security check every day in the month of June. Visit our sp_CheckSecurity page to learn about our free SQL Server tool you can download and run to check your own server.

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)
	INSERT INTO #results
	EXEC sp_readerrorlog @count, 1, 'Login failed';
	Set @count = @count +1

, COUNT(text) as num_login_failures
, MAX(logdate) as max_range
, MIN(logdate) as min_range
FROM #results 

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.

David Seis
Article by David Seis
David Seis is a Support Technician that joined Straight Path in the summer of 2021. His goal is to work in an area that he believes helps others, and to do it to the best of his ability. He loves to help, solve problems, and make things easy for others to understand. Because of this he is lovingly known as a “Good helper” among friends and family. Outside of work, he has a growing passion for cybersecurity, specifically helping protect those who are most vulnerable to fraud, scams, and phishing. He looks forward to learning more about each of his clients, helping minimize issues, and ensure that any problems are solved quickly.

Subscribe for Updates


Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Share This