As many of you know, a data breach is a big deal. Not only do they place your customers at risk, a data breach can also have a very negative impact on the reputation of the company you work for. As a DBA, I teach my students that we are the last line of defense against a breach. I feel that an important part of protecting the data is to investigate failed logins. However, in order to investigate them, we need to find the failed logins first. You may be asking, if investigating failed logins is important, how did I find them? There are few methods we can use, including reading the log, SQL Server Audit and using Extended Events. We will cover each of these including how to set it up and read the data.
This will be a three part series covering each of the methods to capture failed logins.
SQL Server Logs
SQL Server logs is a good place to document failed logins. However, in order to utilize logs there is a server setting that must be configured properly. That settings is the Login auditing setting. This can be found on the server properties as seen below.
There are four options for this setting. The descriptions for each of these settings is very self explanatory. The default is “Failed Logins only”. Before changing this you should probably think about how much data will be added to the log. If too much data is added, that could make finding other entries a bit more challenging. If you change this settings, you will also need to restart the SQL Server service for it to take effect.
When you are capturing the failed logins, the entry in the log will look something like this.
There is some useful information here. First of all, the login name and client. This identifies the who and host the account came from. If the client is something other than the local machine, you will see an IP address there. This IP address should be looked at carefully and if possible, try to ping it to see what would come back. When you ping the IP address, if you add the “-a” switch, you will get the host name returned.
This can obviously be used regardless of the method of collecting failed logins. Another thing to consider if choosing to go this route, is the number of logs to keep.
If you right click on “SQL Server Logs” and go to Configure, you will see the screen below.
As you can see, there are two settings, Log Files count and Log file size. Both of these should be configured in a way that will allow SQL Server to keep the log data you need.
To read the data there are a few methods you can use. You could manually review the logs every day. This can be very time consuming if you have a large number of servers you need to check.
Another option is to use xp_ReadErrorLog. These extended stored procedure can be used to read two types of logs, error logs and agent logs. Since this is a post about tracking failed logins, we will only take a look at the error logs.
While this extended stored procedure has 7 parameters, for the purpose of this post we won’t be using them all. Below is a list of the parameters. The parameters in blue text are what we will use. However, as you can see, we can narrow the results even more by use the second string parameter and the two date parameters.
- Value of error log file you want to read: 0 = current, 1 = first archive log, 2 = second archive and so on….
- Log file type: 1 or NULL = error log, 2 = SQL Agent log
- Search string 1: String one you want to search for
- Search string 2: This is also a string search criteria, however can be used to narrow down the search results more.
- Search from start time
- Search to end time
- Sort order for results: N’asc’ = ascending, N’desc’ = descending
To read the most current log we can execute the code below…
EXEC master.dbo.xp_readerrorlog 0, 1, ‘login fail’
Did you execute it? Probably a better question is…Did it work? The answer is no, it did not work.
Error executing extended stored procedure: Invalid Parameter Type
In the SQL Server world we are used to using a single quote to identify a string. However, for this procedure when we use single quotes for the string, we get the above error. This is an easy fix, use double quotes instead of single quotes as in the code sample below.
EXEC master.dbo.xp_readerrorlog 0, 1, “login fail”
You can also add the letter N just before the first single quote.
EXEC master.dbo.xp_readerrorlog 0, 1, N’login fail’
When you execute either one of these, your results will look similar to below. If the results are not in the desired order, you can move the data to Excel or use the last parameter of the extended stored procedure.
With this, a process can easily be developed to capture the failed logins for a date range and place the data in a table for later analysis.
One of the things I look for are failed logins using the SA account. Over the years there have been several times where the increased number of failed logins using the SA account has helped us identify a bigger issue.
The code below can be used to capture the failed logins for the SA account in the past 24 hours.
DECLARE @end DATETIME = GETDATE()