A secured database environment requires an authorization process in order to provide information access to its users. The same applies to a SQL Server instance.

The authorization/login process can either be successful or no. Even the both login outcomes are security related and should be documented (audited), the focus should be on the failed logins. These can be a result of accidentally mistyped username/password, expired credentials, or insufficient permissions. However, failed logins can also indicate malicious attempts to access confidential data hosted on SQL Server instances.

Methods for auditing failed logins in SQL Server

SQL Server provides several native methods for auditing failed logins. Some of them are easy to set up, others provide more details than the others. They all have in common that the audited information is difficult to review.

Login auditing in SQL Server Management Studio

One of the options to audit failed logins is to turn on the appropriate option in SQL Server in the Server Properties dialog (the Security node) of a SQL Server instance in Object Explorer.

The setting is on the instance level. The SQL Server instance must be running to capture failed logins. Otherwise, there will be no information available. This applies to all SQL Server native auditing techniques.

Once the option is set, failed logins are captured in SQL Server log files and shown in the Log File Viewer of SQL Server Management Studio. These files can be queried using an undocumented xp_readerrorlog extended stored procedure.

Additionally, captured failed logins can be reviewed using the Windows event viewer, in the Application sub-node of the Windows Logs node.

Even this auditing method is easy to set up, there are numerous downsides that make it inappropriate for usage. The main issue is related to native log files. They get recycled and are difficult to be saved automatically for later use. Also, whether the logs are reviewed using built-in viewers or queried, they contain numerous entries not related to failed logins and this noise is difficult to remove.

The Trace technology and auditing failed logins

SQL Server traces and Profiler can also be used for auditing failed logins, but as the feature is announced to be deprecated in future versions of SQL Server, it’s not recommended to use this approach moving forward.

Using SQL Server Audit to capture failed logins

The Audit feature in SQL Server is built on top of Extended Events. It’s the technology that will be additionally developed and expanded in future SQL Server versions. It was introduced in SQL Server 2008 and can be used on both instance and database level. Failed login auditing belongs to the instance level. Therefore, the limitation reflected in supporting database level auditing in the Enterprise and Developer editions only, does not affect our intention to audit failed logins using any SQL Server edition.

To audit failed logins in SQL Server, we’ll create a new audit object for a SQL Server instance. Afterwards, we’ll create a server audit specification object tied to the audit object. Although the same audit object can be used by multiple server audit specifications, it’s a good practice to declare one audit object per auditing event type (e.g. failed logins) to avoid noise in captured information by other audited events. Also, this makes a maintenance of an auditing structure (audit and server audit specification objects along with corresponding log files) easy.

To declare the audit object:

1. Expand the Security node in Object Explorer of SQL Server Management Studio and select the New Audit option in the context menu of the Audits sub-node

2. Type in the name for the audit object (e.g. AuditFailedLogins) and set other parameters per your needs. In this example, we’ll specify the folder where repository log file(s) will be saved. A number of log files and whether they will be rolled over and how, can be defined using the Audit File Maximum Limit section. If you don’t want to lose any captured information on failed logins, keep Maximum rollover files and Unlimited checked.

   

3. Click OK to save the newly declared audit object.

To declare the server audit specification for auditing failed logins and tie it to the previously created audit object:

1. Select the New Server Audit Specification in the context menu of the Server Audit Specifications node.
2. Type in the name for the server audit specification (e.g. FailedLoginsSpecification) and select the AuditFailedLogins audit object (previously declared) from the Audit drop down menu.

   Select the FAILED_LOGIN_GROUP value in the Audit Action Type field of the Actions grid. Note that the rest of the fields for the row cannot be set if the FAILED_LOGIN_GROUP value is set and they remain blank.

   

3. Click OK to save the server audit specification

The created set of SQL Server Audit objects is enough to capture failed logins once they occur. The information about failed logins is stored in the logs as specified in the audit object. The logs can be reviewed using the View Audit Logs option in the context menu of the audit object which opens Log File Viewer.

Over time, the number of entries in the logs can increase and basic available filtering in Log File Viewer cannot be useful. To overcome this, the information about failed logins in log files can be queried:

The LGIF value, as the condition, is used to filter out all failed logins from all logs in the specified folder, whether they contain failed logins only or not (e.g. the C:\AUDITs folder is used by other audit objects). Additionally, you can modify the query per your needs.

A downside of tracking failed logins via SQL Server Audit is that it does not provide the names of client applications used. In some cases, this information can be essential in locating the source of malicious login attempts.

To audit a client application name along with each failed login, use an Extended Events session.

Using SQL Server Extended Events to capture failed logins

Extended Events sessions can be set and used to capture failed logins as errors of Severity 14. Moreover, starting from SQL Server 2012, failed logins can be precisely pointed as the ones with the 18456 error number.

To set up an Extended Events session:

1. Expand the Management node and the Extended Events sub-node in Object Explorer. Select the New Session option in the context menu of the Sessions node.

2. Type in the session name (e.g. FailedLogins) and select the Events page. Move the error_reported item from the Event library grid to Selected events using the “>” button

   

3. As we are focused on failed logins only, click Configure to scroll to additional dialog screen where you should specify additional filters. Use the Global Fields (Actions) tab to check the additional information for the selected event: client_app_name, client_host_name, and nt_username.

   

4. Switch to the Filter (Predicate) tab to specify filter values for the event and to filter out capturing of failed logins only. Use the fields, operators and values as shown in the following screenshot:

   

5. Click OK to save the session, open the context menu of the session and enable it with Start Session

To review the auditing information of the session, expand the session node and select the View Target Data option in the context menu of an event_file node.

The application used to unsuccessfully login to the SQL Server instance audited is shown in the client_name_application field.