August 2019 – Welcome to our SQL World!

Auditing an instance of the SQL Server Database Engine or an individual database involves tracking and logging events that occur on the Database Engine. SQL Server audit lets you create server audits, which can contain server audit specifications for server level events, and database audit specifications for database level events. Each audit level is configured independently, which provides flexibility and fine-grained auditing.

All editions of SQL Server support server level audits. All editions support database level audits beginning with SQL Server 2016 (13.x) SP1. Prior to that, database level auditing was limited to Enterprise, Developer, and Evaluation editions.

SQL Server audit uses Extended Events to help create an audit. The overhead of the feature is lighter than of auditing using SQL traces, but the overall impact depends on how busy the database is and how many events you want to audit.

The audit information can be saved to the event logs or to audit files. The output file is also called a target.

Audit components

The following components must be configured and enabled for auditing.

SQL Server Audit

The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit is at the SQL Server instance level. You can have multiple audits per SQL Server instance.

When you define an audit, you specify the location for the output of the results. This is the audit destination. The audit is created in a disabled state, and does not automatically audit any actions. After the audit is enabled, the audit destination receives data from the audit.

Server Audit Specification

A Server Audit Specification is created for an existing SQL Server audit object that specifies the target type and location. It defines the events that will be audited on the server-level. The audited events occur on the SQL Server instance level. One Server audit specification can be created for one audit specification.

The server-level events are divided into groups by event type, such as: BACKUP_RESTORE_GROUP, DBCC_GROUP, SUCCESSFUL_LOGIN_GROUP, etc.

For example, an event from the DATABASE_CHANGE_GROUP is raised every time a database is created, altered, or deleted. FAILED_LOGIN_GROUP events are raised whenever a non-existing user or an existing user using a wrong password tries to connect to the audited SQL Server instance.

You can find the complete list of all server-level audit action groups and actions at SQL Server Audit Action Groups and Actions.

Database Audit Specification

A Database Audit Specification is also created for the existing SQL Server audit object. Similar to a Server audit specification, it defines what is audited, but on the database-level.

The events in the database-level specifications are divided into groups by event type. Besides database-level audit action groups, there are database-level individual actions that provide auditing of specific actions on tables, views, stored procedures, functions, extended stored procedures, queues, and synonyms. These individual actions are: SELECT, UPDATE, INSERT, DELETE, EXECUTE, RECEIVE, and REFERENCES. They can be configured on a schema (when all schema objects are audited), or on a database (all database objects are audited).

Target

The results of an audit are sent to a target, which can be a binary file, SQL Server event log, and Windows event log. All target types contain the same information. The Windows event logs can be read using the Windows Event Viewer. The SQL Server event logs can be read using SQL Server Log File Viewer. The .sqlaudit files can be read using the fn_get_audit_file function or SQL Server Log File Viewer.

As the events pile up, the target files can grow large. Logs must be reviewed and archived periodically to make sure that the target has sufficient space to write additional records.

To prevent audit information leak, make sure that access to these files is granted only to privileged users.

Permissions

To create, alter, or drop a Server Audit or Server Audit Specification, server principals require the ALTER ANY SERVER AUDIT or the CONTROL SERVER permission. To create, alter, or drop a Database Audit Specification, database principals require the ALTER ANY DATABASE AUDIT permission or the ALTER or CONTROL permission on the database.

Set-up

The SQL Server Audit feature can be set up using either T-SQL, or SQL Server Management Studio options.

To configure the feature using SQL Server Management Studio:

To create a SQL Server Audit object, in Object Explorer expand the Security folder. Right-click the Audits folder and select New Audit….

In the Create Audit dialog, specify the audit name, audit destination, and file path. The options available are:

Queue delay – sets the number of milliseconds before the audit information is processed into a target file. When set to 0, the process is synchronous.

Audit destination can be a file (a *.sqlaudit file), security log, or application log.

Maximum rollover files – the number of files kept in the system. When the maximum number is reached, the new files overwrite the oldest ones. The default value is unlimited.

Maximum files – the number of files kept in the system. When the maximum number is reached, the old files will not be overwritten, and storing new audit information will fail.

Maximum file size (MB) sets the size of the target file. When the specified size is reached, a new file is created. The default value is unlimited.

Click ‘OK’.

Right-click the created audit and select Enable Audit.

To modify an existing audit, disable the audit first (right-click the audit and select Disable Audit), as the changes will not be accepted otherwise.

T-SQL to create the server audit above and enable it:

CREATE SERVER AUDIT [Audit_Data_Changes]
TO FILE
(      FILEPATH = N'C:\Temp\Audit\'
       ,MAXSIZE = 10 MB
       ,MAX_ROLLOVER_FILES = 5
       ,RESERVE_DISK_SPACE = OFF
)
WITH
(      QUEUE_DELAY = 1000
       ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [Audit_Data_Changes] WITH (STATE = ON)
GO

Demo

Server Audit Specification

To create a SQL Server Audit Specification, in Object Explorer expand the Security folder. Right-click the Server Audit Specifications folder and select New Server Audit Specification ….

In the Create Server Audit Specification dialog, specify the specification name, select the audit object from the drop-down list of existing audits, and specify the events you want to audit. In this example, we will audit Failed SQL Server instance logins by selecting FAILED_LOGIN_GROUP as the action type.

Right-click the created specification and click Enable Database Audit Specification.

T-SQL to create the above server audit specification and enable it:

CREATE SERVER AUDIT SPECIFICATION [Failed_Login_Server_Audit]
FOR SERVER AUDIT [Audit_Data_Changes]
ADD (FAILED_LOGIN_GROUP)
WITH (STATE = ON)
GO

After the server audit specification is enabled, every failed login attempt to the SQL Server instance will be audited and reported.

Database Audit Specification

To create a Database Audit Specification, in Object Explorer expand the database you want to audit in and expand the Security folder. Right-click the Database Audit Specifications folder and select New Database Audit Specification ….

In the Create Database Audit Specification dialog, specify the specification name, select the audit object from the drop-down list of existing audits, and specify the events you want to audit. In this example, we will audit INSERT, UPDATE and DELETE action types on a Table.

When specifying an individual action on a database-level, it’s necessary to select the object class it affects (a database, object, or schema), the object, object names and principal names where applicable.

Right-click the created specification and click Enable Database Audit Specification…

T-SQL to create the above database audit specification and enable it:

CREATE DATABASE AUDIT SPECIFICATION [DML_TAT_TIME_Database_Audit]
FOR SERVER AUDIT [Audit_Data_Changes]
ADD (DELETE ON OBJECT::[dbo].[TAT_TIME] BY [dbo]),
ADD (INSERT ON OBJECT::[dbo].[TAT_TIME] BY [dbo]),
ADD (UPDATE ON OBJECT::[dbo].[TAT_TIME] BY [dbo])
WITH (STATE = ON)
GO

After the database audit specification is enabled, every INSERT, UPDATE and DELETE actions on the [dbo].[TAT_TIME] table will be audited, and reported.

How to Analyze and Read SQL Server Audit Information

In our case the audit information is saved to the audit file (*.sqlaudit file) as shown below.

Let’s see what information is captured and how to read it when it’s saved in a *.sqlaudit file.

The sys.fn_get_audit_file function returns information from an audit file.

The following table describes the audit file content that can be returned by this function.

Column Name	Description
event_time	Date and time when the auditable action is fired.
action_id	ID of the action. Example:- UP – UPDATE LGIF – LOGIN FAILED
succeeded	Indicates whether the action that triggered the event succeeded. 1 = success 0 = fail
session_id	ID of the session on which the event occurred.
database_principal_id	ID of the database user context that the action is performed in.
object_id	The ID of the entity on which the audit occurred. This includes the following:- Server objects, Databases, Database objects, Schema objects. Returns 0 if the entity is the Server itself or if the audit is not performed at an object level. For example, Authentication.
class_type	The type of auditable entity that the audit occurs on.
session_server_principal_name	Server principal for session.
server_principal_name	Current login.
database_principal_name	Current user.
server_instance_name	Name of the server instance where the audit occurred.
database_name	The database context in which the action occurred. Is nullable. Returns NULL for audits occurring at the server level.
schema_name	The schema context in which the action occurred. Is nullable. Returns NULL for audits occurring outside a schema.
object_name	The name of the entity on which the audit occurred. This includes the following:- Server objects, Databases, Database objects, Schema objects. Is nullable. Returns NULL if the entity is the Server itself or if the audit is not performed at an object level. For example, Authentication.
statement	TSQL statement if it exists.
file_name	The path and name of the audit log file that the record came from.

Let’s see what audit information is saved to our audit file:-

DECLARE @audit_name NVARCHAR(1000) = 'Audit_Data_Changes';
DECLARE @audit_log_file NVARCHAR(4000)

SELECT @audit_log_file  =CONCAT(log_file_path, name,'*') 
FROM     sys.server_file_audits 
WHERE    name = @audit_name AND is_state_enabled =1 
ORDER BY audit_id DESC                                      

SELECT
ac.name,ac.class_desc,alf.event_time,alf.action_id,
alf.succeeded,alf.session_id,alf.database_principal_id,alf.object_id,
alf.class_type,alf.session_server_principal_name,
alf.server_principal_name,alf.database_principal_name,
alf.server_instance_name,alf.database_name,alf.schema_name,
alf.object_name,alf.statement,alf.file_name
FROM
sys.fn_get_audit_file(@audit_log_file, DEFAULT, DEFAULT) alf
LEFT JOIN sys.dm_audit_actions ac ON ac.action_id =alf.action_id
AND ac.action_in_log =1
ORDER BY event_time DESC;

Output

When analyzing the audit events in any target file, a large range of information is available – the time the event occurred, who did it, what server, database and object were affected, etc. The information that is not provided is the host name and/or IP address of the machine where the event occurred. This is useful in situations when users use the same SQL Server user to connect to the database.

Summary

The feature provides the server-level and database-level auditing, pre-defined groups and activities, and audits both DDL and DML changes. The auditing is more granular as it can be specified for each statement individually, for specific users, and objects. The feature is lightweight and provides minimal overhead.