By: K. Brian Kelley | Comments | Related: > 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...
Define what you're going to filter on. Set the log to Application and enter 18456 for the event ID like so:
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:
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:
Next Steps
- Learn how to turn on failed login monitoring in SQL Server.
- See how you can use extended events to capture failed login information.
- Read how you can configure alerts for critical events (like failed logins - Severity 20).
About the author
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
