February 2020 – Dave Bland

How to Capture Failed Logins using Extended Events

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.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

Extended Events

So far we have covered two methods of capturing failed logins in previous posts. Both are viable options, depending on what you are attempting to accomplish. The third option above is the first that will also work in an Azure SQL DB. This third option is Extended Events. Unlike SQL Server Audit, there is NOT a failed login event. We will need to use the Error_Reported event instead.

When looking at the image of the error log below, you can see the error number, severity and state of a failed login. These numbers will be critical when setting up the Failed Login Extended Event session.

This is the code needed to create the Extended Event session. Although, there are several targets that can be used for storage, I think that the file is the best target to use. You will need to change the path to one that is suitable for your environment. Notice the WHERE clause, the Error, severity and state all match the above image.

CREATE EVENT SESSION [FailedLogins] ON SERVER
ADD EVENT sqlserver.error_reported(
ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.nt_username)
WHERE ([severity]=(14) AND [error_number]=(18456) AND [state]>(1)))
ADD TARGET package0.event_file(SET filename=N’C:\temp\FailedLogins.xel’)

Like the audits, the Extended Event session will need to be enabled before it can collect the desired data. To start a session, simply right click on the session and go to “Start Session”. By looking at the session in SSMS, you can easily tell of the session is enabled or not.

Once the session is started, SQL Server will create a .xel file in the destination location. Once you go to the location, you will notice something a bit unusual about the file names. Look at the huge number at the end. I have looked and looked and the only thing I can find is that this number is the number of seconds since some date in the 1600’s, but not sure if that is true or not.

Because this is an XML based document, it would be logical to think we can read the data we could simply open the document it in Notepad as you will see below. Well, that is not the case. If you try to open one of these files with Notepad, it will look something like this. Not exactly useful.

Well, if I can’t open the file with Notepad, how can I read the data. With Extended Events there are two methods. The first one is the easiest. In SQL Server Management Studio, navigate to the Extended Events and find the session. Then right click on the target and go to “View Target Data…”. Just as below.

When you view target data, it will look similar to below.

Once you are here you can view the data, sort it and even group it. One additional task you can do that I would like to mention is the ability to export the data. While Extended Events does not allow for the direct input of the data to a SQL Server database table, it be exported into a table using SSMS. Now that it is in a table the data is easier to analyze.

When viewing Extended Event data, a new menu item will appear in SSMS, Extended Events. At the bottom of this menu are the menu items that can be used to export the data. There are three options, .XEL file, Table or CSV file.

While view target data as described above is very useful, it would be something that would be very difficult to automate in any way, especially if you would like to move that data collected to a table. This is where the sys.fn_xe_file_target_read_file function comes into play. This function works very much like the sys.fn_get_audit_file function used to read SQL Audit files.

Below is the code you could use to read the XML based extended event file. Using the example we used earlier posts looking for failed logins with the SA account, this bit of code does the same.

This code breaks the reading of the data into a few steps. The first step pull the data into a temporary table with a column that has a datatype of XML. Then the second select statement breaks the XML into more readable columns that will allow us to use more traditional TSQL to read the data.

SELECT FailedLoginData = CONVERT(XML, event_data)
INTO #FailedLogin
FROM sys.fn_xe_file_target_read_file(N’C:\temp\FailedLogin*.xel’, NULL, NULL, NULL);

SELECT
EventDate = FailedLoginData.value(N'(event/@timestamp)[1]’, N’datetime’),
Message = FailedLoginData.value(N'(event/data[@name=”message”]/value)[1]’, N’varchar(100)’)

FROM #FailedLogin
WHERE FailedLoginData.value(N'(event/data[@name=”message”]/value)[1]’, N’varchar(100)’) LIKE ‘Login failed for user ”sa”%’
ORDER BY Eventdate DESC

DROP TABLE #FailedLogin

Of course you will need to update the path to the .XEL file to the path for your environment. When you run the above query, the image below shows what your data set should look like.

Now you can take the query and modify it so you can build some type of alerting.

Hopefully this can help you find failed logins.

Thanks for visiting by blog!!

How to Capture Failed Logins using SQL Server Audit

This will be a three part series covering each of the methods to capture failed logins.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

SQL Server Audit

While using the Error log to capture failed logins is configured by default, using SQL Server Audit is not and will need to be configured. Before we get too deep into how to configure audit, let’s go over a few of the basics.

SQL Server audit is broken up into two parts, Audit and Audit Specification. The Audit can be summarized as the place where are we are going to store the data. While the Audit Specification can be viewed as what we want to capture and to be stored in the Audit location.

To set up an audit…the first thing we need to set up in the Audit itself. This can be found under Security.

To create an audit all you need to do is right click on Audits and go to New Audit. When you do you will see something similar to what is below.

While this is not a post about how to create an audit there are few properties that I think are important to cover.

The first property is “On Audit Log on Failure”. Depending on your environment and audit requirements, you may need to shut the server down if the audit is not working properly. That is what this property is intended to do. As you can see there are three options. You will just need to set it to something that meets the needs of your company.

The next property is “Audit Destination”. As expected, this is where the data collected will be stored. You have three options here: file, Security Log and Application log. You will then need to pick a local of the files, assuming you picked File for the destination. Next you need pick the max number of files and files size. As well as a few other properties that are pretty straight forward. Once you have set everything, click OK and now you have your audit.

What you don’t have is the Audit Specification. There are two locations an Audit Specification can be set up. One is at the server level and the other is at the database level. In this case because we are looking for failed logins, we will need to utilize the server level audit specification.

The create an Audit Specification, simply right click on Audit Specification just below the Audit item.

You will probably want to provide a more appropriate name and you will need to pick the Audit from the drop down box. Because of this, the Audit must be created before the Audit Specification.

Which brings us to the final piece of the pie so to speak. We need to identify what data we want to capture. For the purposes of this blog post, we are only looking for failed logins. Under the “Audit Action Type” you will need to pick “Failed_Login_Group”. Once you click OK…both the audit and audit specification have been created. You will need to check them though. When both an Audit and an Audit Specification are first created they are disabled. You will need to enable them by right clicking on them.

Once both the Audit and Audit Specification have been created. Before they will start collecting data, they will both need to be enabled. You can easily see in the image below how to tell if the Audit or Audit Specification are enabled or disabled.

Now to read the data we have collected. There are two methods that can be used to read audit data. The easiest was is to use SSMS and right click on the audit. Make sure you clicking on the Audit and not the Audit Specification. Remember, the data is stored in the Audit and the Audit Specification is used to identify what you want to capture.

When you right click on the Audit and go to “View Audit Logs”, you will see something like this.

This is very much like the other log viewers we use on a regular basis, job history and Error logs. The data can be filtered, searched and exported.

This is a great way to view the audit data, however what if I want to automate that process so I can build alerts around it. Well can’t really do that using the above method. However, there is a function, sys.fn_get_audit_file, that you can use. Here is a link to Microsoft’s documentation, click here.

If you look at the documentation you will see that there are three arguments for this function. However for our purposes, we really only need to use the first one. In the sample below you will see that the we are indeed only using the first argument. The other two we just put the word Default in. As far as the filename, you can put a specific file name, however, if there is a file rollover, the code will not work. Notice I placed the start of the file name and then and asterisk. In this case we will read from all files in the C:\Test location that start with “FailedLogin” and have a file extension of .sqlaudit.

sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’,default,default)

Below is a more complete sample script. While the function returns many more columns, these are the ones I think we need. Notice the where clause is looking for an action_is that equals LGIF. This will be useful if you use the same audit to collect more than just failed logins.

SELECT event_time
, action_id
, succeeded
, server_principal_name
, server_instance_name
, statement
, file_name
, application_name
FROM sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’, default, default)
WHERE action_id = ‘LGIF’
ORDER BY event_time DESC

The code below looks for failed logins using the SA account for the past hour.

SELECT server_principal_name
, COUNT(server_principal_name) AS ‘NumberOfFailedLogins’
FROM sys.fn_get_audit_file (‘C:\Test\FailedLogin*.sqlaudit’,default,default)
WHERE action_id = ‘LGIF’
AND server_principal_name = ‘sa’
AND event_time > DATEADD(HOUR, -1, GETDATE())
GROUP BY server_principal_name

Of course you will need to change the path to the file that matches yours. If you are using an Azure SQLDB, audit as defined above is not an option.

Thanks for visiting my blog!!

How do I Capture Failed Logins Using SQL Server Log

This will be a three part series covering each of the methods to capture failed logins.

Find Failed Logins Using Error Log

Find Failed Logins Using SQL Server Audit

Find Failed Logins Using Extended Events

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.

1. 1. Value of error log file you want to read: 0 = current, 1 = first archive log, 2 = second archive and so on….
  2. Log file type: 1 or NULL = error log, 2 = SQL Agent log
  3. Search string 1: String one you want to search for
  4. Search string 2: This is also a string search criteria, however can be used to narrow down the search results more.
  5. Search from start time
  6. Search to end time
  7. 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.

Msg 22004, Level 12, State 1, Line 0
Error executing extended stored procedure: Invalid Parameter Type

Completion time: 2020-02-18T19:34:16.3226538-06:00

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 @start DATETIME = DATEADD(HOUR, -24 ,GETDATE())
DECLARE @end DATETIME = GETDATE()

EXEC master.dbo.xp_readerrorlog 0, 1, N’login failed for’, “‘SA'”, @start, @end

Hopefully this will help you find failed logins! Better yet, hopefully you don’t have very many!

Thanks for visiting my blog!!

How to connect a Database User to a Login

Over the years I have had to provide information about logins and database users, most of the time per a request of an auditor. Many times, this is very easy to accomplish because the login name matches the name of the database user account. If you look at the “New User” screen you can see that I am able to enter a different User Name.

Because of this, I can have a User Name that doesn’t match the Login Name. From an audit perspective this can create some confusion. More importantly, it can make it difficult to provide accurate information to the auditors when asked.

In order to connect a database user to a login we will need to use the SID, Security Identifier. When you create a login on a SQL Server, SQL Server creates a SID, sometimes. So what do I mean sometimes? Well, if the login is a SQL login, SQL Server will create the SID, however if the login is an Active Directory user or group, the SID will the be same as the SID in Active Directory.

In order to find the SID we will need to use Syslogins. There is quite a bit of information in this view, however for this purpose we really only need two columns, SID and Name.

SELECT sid
, name
FROM syslogins

If you run the above query your results will look similar to what is below. In this case we are going to use an account names “BlogAcct”.

In order to get the SID for a user account we would run the following query in the database with the user account. The results are just below it.

SELECT sid
, name
FROM sys.sysusers

If you thought the SID looks familiar you would be correct. The SID is the same as the login mentioned above. However, if you notice, the names are different. This login name is BlogAcct, while the User Account is Fred. Yet both have the same SID. So what does this mean? It means that the User Account Fred is mapped to the Login BlogAcct.

If you run this query in the database you are looking for the mapped logins, you will get a result set similar to the image below the query.

SELECT u.sid AS ‘DatabaseUserSID’
, u.name AS ‘DatabaseUserName’
, l.name AS ‘LoginName’
, l.sid AS ‘LoginSID’
FROM sys.sysusers u
INNER JOIN master..syslogins l
ON u.sid = l.sid

Notice that the SIDs match, but the names do not. If you were to run this code against a production database, you will probably see a much large data set. This would also return accounts that have matching names as well. If you want to only find the user accounts that have different names from the login, you will need to add a WHERE clause. Similar to below.

When you do run this, you will see something that might be unexpected. The SA login comes back as having the same SID as the DBO. This is showing that the SA login is mapped to the dbo user account for all databases.

Hopefully this might make it easier when auditors come knocking at your door looking for accounts with database access. This query will also work for AD groups as well.

Thanks for visiting my blog!

ADS: New Database

Regardless of what tool we are using, SQL Server Management Studio or Azure Data Studio, the need to create new databases is always present. Using Transact SQL is an option in both tools. What is not an option in both tools is to right click and to go “New Database”. This has been in SSMS for many years, however it is NOT present in Azure Data Studio.

Below you can see what the menu options are when you right click on databases in SSMS. The New Database option is right at the top.

Now lets move on to Azure Data Studio. When you right click on a server you will have a number of options, including new query, refresh and disconnect. What you do not see is “Delete Database”.

As with a great deal of functionality in Azure Data Studio, the ability to right click on a server and create a new database comes with the installation of an extension. In this case the extension was created by Keven Cunnane, It is pretty simple to install and cost the same as ADS, nothing. This extension is currently not in “Preview” mode. It is currently at version 1.0.0.

To install you will need to click the install button when looking at the extension documentation in Azure Data Studio.

Once you do, you will be taken to the location to download the .VSIX file. You will need to download it and save to a location where it won’t be deleted or overwritten. To install, simply click on “Install Extension from .VSIX package” on the File menu.

Once you do you will see in the lower right corner that the extension has been installed. A restart of Azure Data Studio is not required. Now when you right click server, what do you see? Well..you will see that nothing changed. You will need to right click on Databases under the connection.

Once you click “New Database” you will not see the same dialog box you will see in SSMS. What you will see is something like what is in the image below. In the box you will enter the name of the new database.

Then hit enter and assuming you have the proper rights, ADS will create the database. You will not be able to enter any of the settings we often change to accommodate the specific needs of the database or system. This includes things like file location, recovery model, file growth rate and many more. With this, you will either need to use TSQL to adjust some of the settings or use SSMS. This method of creating a database will depend on the defaults for all the settings.

Overall this is a good extension although it depends heavily on default database settings. I still prefer to use TSQL when creating a database, however this is a nice simple method to use as well.

Here is a list of a number of available extensions, click here. This list does not include all the extensions that are available but is still a nice list.

Thanks for visiting my blog!!