Failed Login Attempts Auditing using SQL Server Audit Tool

To log failed login attempts to SQL Server database instance is a best practice for database administrators to keep data platform safe and secure from unauthorized users. This tutorial includes solution to SQL Server Audit login failed attempts in order to track failed logins for an SQL Server instance.

The new SQL Server auditing tool named SQL Server Audit comes build-in with SQL Server 2008 version or later. I'll try to demonstrate how to manage SQL Server 2008 auditing for login attempt failed because of non-defined user or wrong password, etc. As I mentioned before, tracking failed login attempts to a DBA's database servers is a vital procedure that can prevent future security problems.

Security administrators or database admins can combine failed login attempts with successful login auditing described at SQL Server tutorial SQL Server Login Auditing using SQL Server Audit Tool for a more general SQL Server logging auditing solution.

Create SQL Server Audit

The SQL Server Audit tool requires the creation of a Server Audit record first as the first step in SQL auditing process. The Server Audit defines where the log files will be stored in the Security logs or in Application logs, or in text files on a disk folder.

A database administrator can create audit in SQL Server using SQL Server Management Studio or by executing T-SQL "Create Server Audit" command. Let's start the easiest visual method, and create audit with SSMS wizard as follows.

create new audit in SQL Server Management Studio

Then in the Create Audit dialog screen provide the required details like audit log destination as a log file on a server disk as in this tutorial.

Audit configuration for new Failed Login Attempts

After you press OK button, the audit in SQL Server will be created in disabled status. Of course it is possible to create an audit log in SQL Server using Transact-SQL command "Create Server Audit" as follows.


USE [master]

GO



CREATE SERVER AUDIT [FailedLoginAttempts]

TO FILE (

 FILEPATH = N'D:\SQLServerAudit',

 MAXSIZE = 0 MB,

 MAX_ROLLOVER_FILES = 2147483647,

 RESERVE_DISK_SPACE = OFF

) WITH (

 QUEUE_DELAY = 1000,

 ON_FAILURE = CONTINUE

)

GO

Code

The last step is enabling audit in SQL Server. Just right-click on the SQL Server Audit entry and choose "Enable Audit"

enable SQL Server Audit using SSMS

Create SQL Server Audit Specification

The second important step for managing auditing in SQL Server is creating the audit specification object. Audit Specification defines which events will be logged for the related audit. Actually Audit Specification provides a mapping environment between server level or database level events and the SQL Server Audit record which defines the log destination.

Again to create Server Audit Specification, SSMS is the best way to proceed.

create new Server Audit Specification using SSMS

The event selection is managed in this screen as follows. For auditing failed login attempts, I chose only the FAILED_LOGIN_GROUP server action group as seen below.

create SQL- Srver Audit Specification for failed_login_group

As usual for database admins who prefers to work with T-SQL commands, the "Create Server Audit Specification" command can be used. Here is a sample SQL script.


USE [master]

GO



CREATE SERVER AUDIT SPECIFICATION [SQLServerFailedLoginAttempts]

FOR SERVER AUDIT [FailedLoginAttempts]

ADD (FAILED_LOGIN_GROUP)

GO

Code

And of course, the created audit specification should be enabled. Otherwise, events selected will not be logged in audit destination.

Enable failed_login_group Audit Specification

Read Server Audit Logs

SQL Server Audit logs can be read via two methods: one by using Log File Viewer within the SQL Server Management Studio and the other method is using T-SQL codes by selecting audit data with sys.fn_get_audit_file system security function.

In order to read SQL Server Audit logs in SSMS, administrators or SQL Server security admins can launch Log File Viewer as follows:

Log File Viewer for SQL Server Audit logs

Here is the Log File Viewer screen displaying the selected SQL Audit logs

list failed login attempts to SQL Server

In order to display Server Audit logs using T-SQL codes where system security function sys.fn_get_audit_file is used, the following SQL script can be used as a sample template.

 
SELECT * FROM sys.fn_get_audit_file (

 'D:\SQLServerAudit\FailedLoginAttempts_*.sqlaudit',

 default,default);

Code

fn_get_audit_file system function to read audit data

SQL Server database administrators or security admins can only select the required columns in order to make a daily report that will be sent automatically to their email addresses via SQL Server Reporting Services report subscription method. Here is a simplified representation of the audit file defined in the Server Audit creation screen that is read from the stored folder using T-SQL fn_get_audit_file system function.


SELECT event_time, server_instance_name, server_principal_name, statement

FROM sys.fn_get_audit_file (

 'D:\SQLServerAudit\FailedLoginAttempts_*.sqlaudit',

 default,default)
 
WHERE succeeded = 0

Code

One last note for the naming conventions of the Server Audits. If you name the SQL Server Audit so that the name includes spaces in it that is the case for "SQL Server Sample Audit", SQL Server will create the audit file by replacing the space characters with "%5" string. If you browse the file folder where the audit file is created, you will see the audit log file is named by the replaced audit name plus the audit's unique identity, etc.


declare @audit sysname = 'SQL Server Audit Name'



SELECT * FROM sys.fn_get_audit_file (

 'D:\SQLServerAudit\' + REPLACE(@audit,' ','%5') + '_*.sqlaudit',

 default,default);

Code