SQL Server Login Auditing using SQL Server Audit Tool

Database administrator can log successful logins to SQL Server using new SQL Server auditing tool SQL Server Audit. In this SQL Server tutorial, administrators can configure an SQL Server login auditing mechanism using SQL Server Audit tool step by step.

This SQL Server audit configuration enables adminstrators to track the users logging to SQL Server instances that they're responsible for. By checking the resultant login auditing records periodically, an SQL Server administrator can proactively control database connections to his servers and prevent an unauthorized action before it harms the sensitive data of the enterprise organization.

SQL Server auditing tool enables automatic logging of server based events or events in database level into different audit destinations like Application Log, Security Log or into a text log file. Using the Log File Viewer, build-in SQL Server tool these audit records can be displayed, filtered by database admins or security admins.

Please note that if you choose Security log as audit destination and SQL Server Audit fails to write Security Log, please refer to Write Server Audit Events to the Security Log

Create SQL Server Audit

The first step for using SQL Server Audit tool is creating a new SQL Server Audit. An SQL Server Audit can be used in order to define the log file destination for all events that will be combined with this SQL Server Audit later in the following steps using the Audit Specification object. SQL Server Audit object forms a single point for recording audit logs and displaying logged audit events (here in this SQL auditing sample, the SQL Server login auditing)

Create SQL Server Audit using SQL Server Management Studio

When you are connected to the target SQL Server instance using SQL Server Management Studio, drill-down Security node of the SQL Server instance in Object Explorer window.

create new SQL Server Audit

The following Create Audit screen is displayed which enables administrators to configure SQL Server Audit. Just type a descriptive Audit name and define a queue delay in milliseconds. If you enter 0 for queue delay time, logs are immediately written to audit destination.

If you mark the "Shut down server on audit log failure" checkbox on the screen while defining the SQL Server Audit object, whenever log destination is not available or when an error occurs while writing to audit log destination the SQL Server instance is forced to shut down. This prevents possible unlogged events to occur on the SQL Server. But if you log non-vital data or events, a possible error on audit destination might cause your productive SQL Server data platform to shut down causing your applications fail without database connection.

The Audit destination determines where the login auditing records will be stored.

Log successful sql logins using SQL Server Audit tool

I choosed to record audit logs to Security Log so choosed Security Log as Audit destination. Database administrators can either choose File as audit destination to log successful login actions to a text file or Application Log to enter log records into Windows Application Log.

Create SQL Server Audit using T-SQL

Using the following T-SQL syntax, SQL Server database administrators can create the above SQL Server Audit object.


USE [master]

GO

CREATE SERVER AUDIT [SQLServerAuditName]

TO SECURITY_LOG

WITH (

 QUEUE_DELAY = 0,

 ON_FAILURE = CONTINUE

)

GO

Code

And the below T-SQL CREATE SERVER AUDIT command will create SQL Audit object with a 1 second queue delay time and write to Application log. One important option used in below sql statement is the ON_FAILURE parameter is set to SHUTDOWN instead of CONTINUE will cause SQL Server instance to shutdown when a problem occurs while writing audit logs


CREATE SERVER AUDIT [SQL Server Login Auditing]

TO APPLICATION_LOG

WITH

(

 QUEUE_DELAY = 1000,

 ON_FAILURE = SHUTDOWN

)

GO

Code

If you want to write sql login auditing logs to a file located on the server disk, you can use the following T-SQL command


CREATE SERVER AUDIT [SQL Server Audit Name]

TO FILE

(

 FILEPATH = N'C:\SQL\',

 MAXSIZE = 0 MB,

 MAX_ROLLOVER_FILES = 2147483647,

 RESERVE_DISK_SPACE = OFF

)

WITH

(

 QUEUE_DELAY = 1000,

 ON_FAILURE = CONTINUE

)

GO

Code

Create SQL Server Audit Specification

An SQL Server Audit Specification is used to combine different events and combine these events to a SQL Server Audit object. For example in our example we will only use the SUCCESSFUL_LOGIN_GROUP during the SQL Audit Specification creation and write successful logins to related SQL Audit object destination.

An SQL Server database administrator can also choose two server level audit action groups SUCCESSFUL_LOGIN_GROUP and FAIL_LOGIN_GROUP in order to log every login attempts to SQL Server. Additionally LOGOUT_GROUP can also be included to the audit specification for tracking how long the login is connected to the database server.

Create SQL Server Audit Specification using SQL Server Management Studio

In order to create SQL Server Audit Specification, connect to target SQL Server instance using SSMS. In the Object Explorer window drill-down Security node. Right-click on SQL Server Audit Specifications and choose "New SQL Server Audit Specification..." menu option as seen in below screenshot.

create new SQL Server Audit Specification

Below you can see the Create SQL Server Audit Specification dialogue screen. Administrators are expected to provide a descriptive name for this new sql audit specification. Then the audit destination will be defined by choosing the SQL Server Audit from the Audit dropdown.

create SQL Server Audit Specification for successful login

The Actions section enables admins to define the data to be collected under this audit specification. Since this sql auditing example demonstrates only the successful logins, I've chosen only SUCCESSFUL_LOGIN_GROUP server level action group. Of course it is possible to add more than 1 action in this screen.

Create SQL Server Audit Specification using SQL Server Management Studio

Of course, it is possible to create an SQL Server Audit Specification using Transact-SQL. T-SQL developers or DBA's can execute the following sql script for creating the audit specification that is used in this tutorial.


CREATE SERVER AUDIT SPECIFICATION [SQLServerLoginAuditSpecification]

FOR SERVER AUDIT [SQLServerSuccessfulLogins]

ADD (SUCCESSFUL_LOGIN_GROUP)

WITH (STATE = OFF)

Code

Please note that if you create the Audit Specification with (STATE = ON) then the newly created specification object will be enabled automatically. Otherwise if Audit Specification is created with (STATE = OFF) option, administrators have to enable Audit Specification object manually before it can be used for SQL login auditing purpose.

How to Enable SQL Server Audit Specification

Before SQL Server login auditing can be started on the server, the audit specification should be enabled. In order to enable SQL Server Audit Specification, Management Studio can be used as shown in the following screenshot. Just right-click on the Audit Specification and choose "Enable SQL Server Audit Specification" option from context menu.

enable Server Audit Specification in SSMS

Of course TSQL can be used for enabling audit specifications too.


-- enable audit specification

ALTER SERVER AUDIT SPECIFICATION [SQLServerLoginAuditSpecification] WITH (STATE = ON)

-- disable audit specification

ALTER SERVER AUDIT SPECIFICATION [SQLServerLoginAuditSpecification] WITH (STATE = OFF)

Code

How to Enable SQL Server Audit

In order to enable SQL Server Audit and start logging SQL login auditing records, developers or admins should enable SQL Server Audit object. It is possible to enable Audit by right-click on the SQL Audit object within the Object Explorer window under Security > Audits nodes. Then by choosing the "Enable Audit" command option from menu

enable Audit in SQL Server Management Studio

If you want to use Transact-SQL, the following command can be used to enable or disable SQL Server Audit object


-- enable SQL Server Audit

ALTER SERVER AUDIT [SQL Server Audit Name] WITH (STATE = ON)

-- disable SQL Server Audit

ALTER SERVER AUDIT [SQL Server Audit Name] WITH (STATE = OFF)

Code

Display SQL Server Audit Logs using Log File Viewer

Database administrators can display the audit log records using the SQL Server tool, Log File Viewer.

After database administrators launch SQL auditing solution for login auditing, now the audit results are ready to be displayed using various log viewer tools. Although it is possible to check audit log entries using Windows Event Viewer, the best tool for displaying sql audit logs is SQL Server Log File Viewer.

Drill-down the SQL Server instance in the Object Explorer window of SQL Server Management Studio until you reach the related SQL Audit record.
You can follow : SQL Server instance > Security > Audits > Audit name
Then right click on the SQL Audit and choose View Audit Logs menu option to start Log File Viewer tool.

view Audit logs in SQL Server 2008

Here is a sample view from Log File Viewer displaying SQL Server successful login auditing logs

Display successfull login audit logs

It is a requirement to log failed login attempts to a SQL Server by database admins, you can find a solution similar to one illustrated here at Failed Login Attempts Auditing using SQL Server Audit Tool