Ahmad Yaseen
SQL Server Audit Specification

Using the SQL Server Audit Feature to Audit Different Actions

April 22, 2019 by

In the previous article of the SQL Server Audit series, SQL Server Audit Feature Components, we discussed the three main components of the SQL Server Audit feature; the SQL Server Audit, the Server Audit Specifications, and the Database Audit Specifications in details. In this article, we will show how to use the SQL Server Audit feature to track, log and review the different operations performed at both the SQL Server and the database levels.

SQL Server Instance Level Actions

SQL Server provides us with a number of server-level audit action groups that can be tracked and audited using the SQL Server Audit feature. These action groups include:

  • AUDIT_CHANGE_GROUP: This event is raised when any audit or audit specification is created, modified or deleted
  • BACKUP_RESTORE_GROUP: This event is raised when a backup or restore command is issued
  • DATABASE_CHANGE_GROUP: This event is raised when a database is created, altered, or dropped
  • DATABASE_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP statement is executed on database objects, such as schemas
  • DBCC_GROUP: This event is raised when any DBCC command is executed
  • FAILED_LOGIN_GROUP: This event is raised when a principal tried to log on to SQL Server and failed

Starting from SQL Server 2012, SQL Server provides us with the ability to create user-defined audit events, that can be integrated with ant application and allow it to write a customized event using sp_audit_write procedure. In order to track and audit the event written by an application, the USER_DEFINED_AUDIT_GROUP should be selected in either the Server or the Database Audit Specification.

Assume that you are requested to track and audit the action groups described previously. To do that, you need to configure and enable the Server Audit Specification below, that is bind to a SQL Server Audit:

SQL Server Audit Specification

To check how the Server Audit Specification can be used to audit server-level actions, we will perform number of actions at the SQL Server instance, as below:

Failed Login

Once these operations are performed, you will see that the SQL Server Engine tracked and logged these actions to the audit target specified in the SQL Server Audit. Whatever target specified in the SQL Server Audit, you can simply view the audit logs by right-clicking on the SQL Server Audit, under the server-level Security node, and choose the View Audit Logs options, as shown below:

View Audit Logs

In the displayed Log File Viewer, you can review all the tracked actions that are performed by the server principals, caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:

SQL Server Audit Log File Viewer 1

For example, the previously executed DBCC command, the performed database backup operation, the newly created database and the failed login attempt are audited and logged by the SQL Server Audit built-in feature, with complete details about the action date time, the operation type, the principal name, the server name, the database name, if the operation is completed successfully or failed, the executed statement and other useful information, as shown clearly below:

SQL Server Audit Log File Viewer 2

SQL Server Database Level Actions

SQL Server provides us also with a number of database level audit actions and action groups that can be tracked and audited using the SQL Server Audit feature. These actions and action groups include:

  • DATABASE_PRINCIPAL_CHANGE_GROUP: This event is raised when a database user is created, altered, or dropped from a database
  • SCHEMA_OBJECT_CHANGE_GROUP: This event is raised when a CREATE, ALTER, or DROP operation is performed on a schema
  • SELECT: This event is raised when a SELECT statement is executed
  • INSERT: This event is raised when an INSERT statement is executed
  • DELETE: This event is raised when a DELETE statement is executed

Assume that we are requested to design an audit solution to track the list of database-level actions and action groups described previously. To achieve that, we need configure and enable the database Audit Specification below, that is bind to a SQL Server Audit. Recall that when you select to audit an action group, you will not be able to specify the properties of the audited object. On the other hand, choosing an action to be audited will require you to specify the class of the audited object, the name of the object(s) to be audited and the name of the principal(s) that will be audited on the selected action, as shown below:

Database Audit Specification

To see how the Database Audit Specification can be used to audit the database-level actions, let us perform the operations below:

From the Log File Viewer, you can see that all the performed actions will be caught by the SQL Server Audit feature and logged to the SQL Server Audit target, as below:

Database Audit Specification result 1

For example, the previously executed CREATE USER statement, the newly created database schema and the executed SELECT, INSERT and DELETE statements are audited and logged by the SQL Server Audit built-in tool, with useful details that describe when the action is performed, the type of that operation, the name of the principal who performed this change, the server name from which the command executed, the database name, whether this operation is completed successfully or failed, the executed statement and more useful information, as shown below:

Database Audit Specification result 2

Reading Audit Data File

If the SQL Server Audit is configured to write the audit data to a flat file, with SQLAUDIT extension, the data inside that file cannot be read directly from the file. If you open the audit file using any text editing tool, you can see only useless data, as the audit data is written to the file in binary, as shown below:

Audit file data

In order to view the audit data that is written to the target flat file, the fn_get_audit_file() table-valued function should be used. This function accepts three parameters:

  • File Pattern: that specifies the path or the path with file name for the audit file set to be read. You can read all the audit files in a specific folder by specifying the path of the folder with the asterisk (*)
  • Initial File Name: that specifies the path and name of a specific file in the audit file set to start reading audit records from
  • Audit record Offset: that specifies a known location in the specified initial_file_name. If this parameter is provided, the function will start reading at the first record of the buffer immediately following the specified offset

The fn_get_audit_file function can be used within a SELECT statement that makes it more flexible to filter the audit data retrieved from the file to limit the number of retrieved columns or rows. For example, rather than displaying all the audit file content, you can use the fn_get_audit_file function to read the actions performed during a specific period of time, a specific type of actions, the action performed by a specific principal, on a specific database and so on, as in the T-SQL script below:

And the result in our example will be like:

fn_get_audit_file result

Important Considerations

  • The SQL Server Audit file should be secured and protected from any unauthorized access. This can be achieved by writing the file to a secure location on a different server with limited permission to the auditors only, or simply writing the audit logs to the Windows Security log
  • When a database, with a Database Audit Specification configured on it, is attached to a new instance that has no SQL Server Audit, has SQL Server Audit that has a different GUID, or the new SQL Server instance does not support the SQL Server Audit feature, this Database Audit Specification will be orphaned and will not record any event. This situation can be corrected by binding the Database Audit Specification to an existing SQL Server Audit, or create a new SQL Server Audit and bind that Database Audit Specification to it
  • When a database, with Database Audit Specification, configured on it, participates in high availability solution such as Database Mirroring or Always On Availability Group, the mirrored and secondary replicas should contain a SQL Server Audit with the same GUID as in the Principal and the Primary servers. In addition, the SQL Server service account should have permission on the folder where the audit log files will be written
  • If the SQL Server Audit is configured to shut down in case of any failure writing the audit logs to the specified target, using ON_FAILURE=SHUTDOWN option, the SQL Server instance will not be able to start normally. In this case, the SQL Server instance can be started in minimal configuration mode using the -f flag in the startup parameters. In addition, the database administrator can fix the audit issue by starting the SQL Server instance in Single User mode using the -m flag in the startup parameters
  • For better performance, audit only the events that you really need to track

In the next article, in this series, we will show how to audit the SQL Server using the system temporal table feature. Stay tuned!

Table of contents

SQL Server Audit Overview
Implementing a manual SQL Server Audit
Creating a SQL Server audit using SQL Server Extended Events and Triggers
Auditing by Reading the SQL Server Transaction Log
Change Data Capture for auditing SQL Server
Creating a SQL Server audit using SQL Server Change Tracking
SQL Server Audit Feature Components
Using the SQL Server Audit Feature to Audit Different Actions
Performing a SQL Server Audit using System-Versioned Temporal Tables
Perform a SQL Server Audit using ApexSQL Audit
SQL Server Auditing Best Practices

Ahmad Yaseen

Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience.

He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs

View all posts by Ahmad Yaseen
Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

Auditing

About Ahmad Yaseen

Ahmad Yaseen is a SQL Server database administration leader at Aramex International Company with a bachelor’s degree in computer engineering as well as .NET development experience. He is a Microsoft Certified Professional with a good experience in SQL server development, administration, performance tuning, monitoring and high availability and disaster recovery technologies. Also, he is contributing with his SQL tips in many blogs View all posts by Ahmad Yaseen

1,717 Views