mssqltips logo

Quickly Find Failed SQL Server Logins Using the Windows Event Log

By:   |   Updated: 2016-08-04   |   Comments   |   Related: More > Security

Problem

My SQL Server is configured to track failed logins (see the tips in the Next Steps section below). I can sift through my SQL Server Error Logs, but this is a pain because there is so much data in the logs. Is there an easier way and will that way work for my security team who is trying to collect this information automatically?

Solution

Yes, there is a better way. If you are auditing for failed logins, not only does SQL Server write the events to its error log, but it also writes the events to the Application event log for the system it's running on. Therefore, as long as you have permission to do so, you can view the Application event log for the system and find the events. You're looking for event ID 18456 with a source of MSSQLSERVER for a default instance or MSSQL$<named instance name> for a named instance. This information is what your security team needs.

Using Custom Event Viewer Views for Failed SQL Server Logins

You can be overloaded by events in the Application event log, too. One trick you can use is to build a Custom View. This way, the Event Viewer will sift through the events based on a predefined filter you've configured. To start, make sure you are in Event Viewer running under a login that has the ability to access the Application event log for the server where the SQL Server is installed. This could be Event Viewer running on your workstation.

Next, right-click on Custom Views and choose Create Custom View...

Creating a custom view

Define what you're going to filter on. Set the log to Application and enter 18456 for the event ID like so:

Defining the custom view

Then click OK and give the view a name. If you have SQL Server installed where the Event Viewer is connected to and you've had failed logins which were audited, you should see them in the appropriate pane:

Displaying the failed logins

I mentioned creating this view on your workstation and here's why: when you connect to another computer, you can use the custom view there, too. If I create the custom view on my workstation, I only have to create it once. I can then connect to any SQL Server where I'm interested in looking for failed logins and then click on my custom view. It may take a few moments, but Event Viewer will retrieve the events and display the filtered result.

Using PowerShell to find Failed SQL Server Logins

As you might guess, there is a PowerShell cmdlet which retrieves events: Get-EventLog. Here's how to run the cmdlet local to the system where the event log is. Yes, you can run the cmdlet against a remote computer using -ComputerName but there are caveats to doing so that's beyond this tip.

However, if it is running locally, we can still leverage this to generate a report which could be emailed daily which is scheduled via SQL Server Agent or Task Scheduler, for instance. But before we get too far ahead, let's see what the command looks like:

$failedLogins = get-eventlog -LogName Application `
    | Where-Object { $_.eventid -eq 18456 };

This will retrieve all failed login events in the Application event log. However, this format is hard to read. Also, it's for all dates. Let's filter the events for yesterday and use regular expression matching to pull out the event time, the failed login, where the attempt came from, and the reason for the failure. Here's the code:

$today = $(get-date).date;
$yesterday = $($today).AddDays(-1);

$failedLogins = get-eventlog -LogName Application `
    -Before $today -After $yesterday `
    | Where-Object { $_.eventid -eq 18456 } `
    | Where-Object {$_.Message -match "user '(?<login>.+)'.+Reason:.(?<reason>.+).+\[CLIENT:(?<client>.+)\]" } `
    | foreach {new-object psobject `
        -prop @{ time=$_.TimeGenerated;login=$matches['login']; client=$matches['client']; reason=$matches['reason'] } }
$failedLogins | Format-Table -Property time,login,client,reason;

Save the code off as a PowerShell script. Now, if we execute the script we'll see the events filtered accordingly with the key pieces of information retrieved from the event information:

PowerShell output of failed logins
Next Steps


Last Updated: 2016-08-04


get scripts

next tip button



About the author
MSSQLTips author K. Brian Kelley K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

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.






download

























get free sql tips

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.



Learn more about SQL Server tools