Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Find All Failed SQL Server Logins


By:   |   Updated: 2017-07-03   |   Comments (3)   |   Related: More > Security

Problem

You are a SQL DBA responsible for many instances of SQL Server running many versions of SQL Server from 2005 to 2016. You get a requirement from the IT security department to get a list of all failed login attempts from last week from all servers.  You need a consistent method to capture this data from all of these servers and in this tip we will cover one approach.

Solution

The method for solving this problem involves a stored procedure that will be compiled and executed on the master database of each SQL Server instance. 

The stored procedure declares two table variables @EnumErrorLogs and @ErrorLogInfo. The first, @EnumErrorLogs will store the list of the SQL Server error log values like; archive number, change date and file size.  The second, @ErrorLogInfo will store the message lines from the logs. 

We will use the system stored procedure sp_enumerrorlogs to fill the first table and then we will loop over each of these files using sp_readerrorlog system stored procedure to fill the second table. We will then filter this data to find login information and specifically where there were failures for the last 7 days.

Code to Find Failed Logins

If you want to go back further than 7 days, you change the 7 values below to match your needs.

CREATE PROC usp_GetFailedLoginsListFromLastWeek
AS
BEGIN
   SET NOCOUNT ON

   DECLARE @ErrorLogCount INT 
   DECLARE @LastLogDate DATETIME

   DECLARE @ErrorLogInfo TABLE (
       LogDate DATETIME
      ,ProcessInfo NVARCHAR (50)
      ,[Text] NVARCHAR (MAX)
      )
   
   DECLARE @EnumErrorLogs TABLE (
       [Archive#] INT
      ,[Date] DATETIME
      ,LogFileSizeMB INT
      )

   INSERT INTO @EnumErrorLogs
   EXEC sp_enumerrorlogs

   SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
   FROM @EnumErrorLogs

   WHILE @ErrorLogCount IS NOT NULL
   BEGIN

      INSERT INTO @ErrorLogInfo
      EXEC sp_readerrorlog @ErrorLogCount

      SELECT @ErrorLogCount = MIN([Archive#]), @LastLogDate = MAX([Date])
      FROM @EnumErrorLogs
      WHERE [Archive#] > @ErrorLogCount
      AND @LastLogDate > getdate() - 7 
  
   END

   -- List all last week failed logins count of attempts and the Login failure message
   SELECT COUNT (TEXT) AS NumberOfAttempts, TEXT AS Details, MIN(LogDate) as MinLogDate, MAX(LogDate) as MaxLogDate
   FROM @ErrorLogInfo
   WHERE ProcessInfo = 'Logon'
      AND TEXT LIKE '%fail%'
      AND LogDate > getdate() - 7
   GROUP BY TEXT
   ORDER BY NumberOfAttempts DESC

   SET NOCOUNT OFF
END                     

Example of how to use the stored procedure

We execute the procedure:

exec usp_GetFailedLoginsListFromLastWeek

We get the following failed logins from last week list (as it was recorded on my server)

NumberOfAttempts Details
---------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3                Login failed for user 'NT SERVICE\ReportServer$SQL2K14'. Reason: Failed to open the explicitly specified database 'ReportServer$SQL2K14'. [CLIENT: <local machine>]
3                Login failed for user 'NT SERVICE\ReportServer$SQL2K14'. Reason: Failed to open the explicitly specified database 'ReportServer$SQL2K14'. [CLIENT: 10.1.22.32]
2                Login failed for user '222'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
1                Login failed for user '222222'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
1                Login failed for user '11'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
1                Login failed for user '111'. Reason: Could not find a login matching the name provided. [CLIENT: <local machine>]
   

Repeat these steps on all reported SQL Servers.

SQL Server Setting to Capture Failed Logins

Make sure the server security auditing property is set to monitor Failed Logins only or Both Failed and Successful Logins.

To set for just "Failed Logins" do the following:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
     N'Software\Microsoft\MSSQLServer\MSSQLServer', 
     N'AuditLevel',
     REG_DWORD, 2
GO
   

For both "Failed and Successful Logins" do the following:

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE',
     N'Software\Microsoft\MSSQLServer\MSSQLServer', 
     N'AuditLevel', 
     REG_DWORD, 3
GO
   

These settings can also be changed in SSMS, by right clicking on the instance name, select Properties and go to the Security page.  These options are under the Login Auditing section on this page.

ssms login auditing
Next Steps
  • If you liked this solution you can compile and use this procedure in order to report failed logins.
  • The procedure was tested on SQL Server 2014 Standard Edition, but should work for SQL Server 2005 and later.


Last Updated: 2017-07-03


get scripts

next tip button



About the author
MSSQLTips author Eli Leiba Eli Leiba is a senior application DBA, a teacher and a senior database consultant with 19 years of RDBMS experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, July 03, 2017 - 5:08:29 PM - Morden Kain Back To Top

One thing I see wrong with the above, is if you are checking to see when the attempts are made.  All you get back is that someone/something tried accessing X times in X days.  This can cause false alarms.  Here is my prefered method of looking over the logs:

if object_id('tempdb..#tmpErrorLog') is not null drop table #tmpErrorLog;
create table #tmpErrorLog (
    LogDate datetime2,
    ProcessInfo varchar(512),
    Txt varchar(max)
);

insert #tmpErrorLog (LogDate, ProcessInfo, Txt)
exec xp_readerrorlog;

-- This line is for login errors...
delete from #tmpErrorLog where charindex('Error: 18456',Txt) <> 0;

-- Get rid of the "Backup" lines.
delete from #tmpErrorLog where ProcessInfo = ('backup');

-- Do not need NULL lines.
delete from #tmpErrorLog where Txt is null;

-- Get rid of "Unsafe assembly"s and App Domains
delete from #tmpErrorLog where charindex('Unsafe assembly', Txt) <> 0;
delete from #tmpErrorLog where charindex('AppDomain', Txt) <> 0;

-- Get rid of all the DBCC CHECKDBs as we log that information in a table
delete from #tmpErrorLog where charindex('DBCC CHECKDB',Txt) <> 0;
delete from #tmpErrorLog where charindex('transactions rolled',Txt) <> 0;
delete from #tmpErrorLog where charindex('Recovery completed',Txt) <> 0;

declare
    @Today datetime2 = convert(varchar,getdate(),111) + ' 23:59:59.999999',
    @Yesterday datetime2 = convert(varchar,getdate() - 3, 111)
;

select * from #tmpErrorLog
where LogDate between @Yesterday and @Today
order by LogDate desc;


Monday, July 03, 2017 - 3:46:19 PM - Greg Robidoux Back To Top

Hi Julia,

this process is using the native SQL Server Error Logs for the source of the login failures. 

It is just using a system stored procedure to read through the error logs to pull out the failed login information.

-Greg


Monday, July 03, 2017 - 3:32:43 PM - Julia Back To Top

I am just wondering why this stored procedure is better than using native SQL ERRORLOG - I load those daily and can report off those across all servers.

 


Learn more about SQL Server tools