In the previous articles of the SQL Server Audit series (see the TOC at the bottom), we discussed the concept of the SQL Server Audit, why we need an audit solution to track and log the different events performed in our SQL Server instances and databases, and went through the different methods that can be used to audit the SQL Server databases. In this article, we will discuss the SQL Server Audit feature components and how to configure it.

SQL Server Audit Overview

The SQL Server Audit feature was introduced the first time in SQL Server 2008, available only in the enterprise edition, as a complete, secure and easy to manage auditing solution to track the changes performed at both the SQL Server instance and database levels, with the minimal performance impact on the audited server.

SQL Server Audit is built using the SQL Server Extended Events feature, that makes it lighter and easier to audit different types of operations and changes performed at the different SQL Server levels. Rather than spending the time configuring the Extended Event session to create a SQL Server Audit solution, you can use the SQL Server Audit feature directly with the minimal required configuration effort and the audit records will be written automatically to the Windows application log, Windows security log or a separate flat file.

The SQL Server Audit feature consists of three main components that combine together to produce a complete SQL Server Audit solution. These components are:

• The SQL Server Audit feature (required)
• The Database Audit Specification (optional)
• The Server Audit Specification (optional)

Let’s discuss the SQL Server Audit components in detail:

SQL Server Audit

The parent component of the SQL Server Audit feature, that contains both the Server Audit Specifications and the Database Audit Specifications, is the SQL Server Audit.

SQL Server Audit is used to define where the audit information will be stored in the audit, if it will be performed in synchronous or asynchronous mode, how to handle the audit file rollover, and what action will be performed in case of audit failure. All this information will reside in the master system database.

Started from SQL Server 2012 and onwards, the SQL Server Audit is available in all SQL Server editions, with the ability to create more than one audit per each SQL Server instance. To create a new SQL Server Audit using the SQL Server Management Studio tool, connect to the SQL Server instance, and right-click on the Audit node under the Security node and choose New Audit option, as shown below:

In the Create Audit window, you will be requested to provide:

• A unique indicative name for the SQL Server Audit
• The maximum amount of time, in milliseconds, that the system will wait before any audit is processed. The queue delay value can be 0, to process the audit information synchronously or up to 1000 milliseconds to process the audit information asynchronously
• The action that will be taken when the SQL Server Engine is not able to perform the audit logging process. SQL Server Audit provides you with three options: ignoring the audit log issue and continue working, shutting down the SQL Server instance and prevent any action to be performed without being audited and written to the audit log, or keeping the SQL Server instance online and enforce any tracked action to be failed, in order not to miss any auditing information when the audit target is not available

• The Audit destination, that can be a Flat file, Security Log or Application Log as below:

Take into consideration that the Security Logs are more secured and access restricted than the Application Logs. In addition, the Application Log events will be overwritten automatically when it reaches its maximum size, that may result in audit data loss.

If you go with the recommended approach of storing the audit logs to a flat file, you will be asked to provide the following information:

• The location where the SQL Server Audit files will be created
• The maximum number of audit files that will be used before rolling over the existing audit log files
• The maximum limit for the number of audit log files
• The maximum size that the audit log file can reach before creating a new file
• If a disk space will be reserved for these audit log files

The name of the SQL Server Audit file will be generated automatically, in order to make sure that each audit log file will be assigned a unique name. The unique name of the audit log file will be made up of the Audit name, the Audit GUID, the Partition number and the Time Stamp.

Starting from SQL Server 2012, you have the option to add a filter that will be used to evaluate the audit log records before writing these log records to the target audit destination. You can use any field, except for the name and the offset of the audit log file, in the filter expression, as below:

Another option for creating the SQL Server Audit is using CREATE SERVER AUDIT T-SQL statement, by providing the same settings mentioned previously, as in the T-SQL script below:

When the SQL Server Audit is created, it will be in disabled state. To start writing the audit log records to the specified target, the SQL Server Audit should be enabled as below:

Take into consideration that you cannot perform any change on the SQL Server Audit when it is enabled. To change any setting, you should disable it first, perform the change then enable it again. In addition, to be able to CREATE, ALTER or DROP the SQL Server Audit, the user should be granted ALTER ANY SERVER AUDIT permission or be member of the SYSADMIN fixed server role. On the other hand, to be able to write data to the specified file location, the SQL Server service account should have Write permission on that path. In order to write the audit logs to the Windows Security log, the SQL Server service account to be added to the Generate security audits policy.

Server Audit Specifications

The Server Audit Specifications is a SQL Server feature that is used to track and collect different types of changes that are performed at the SQL Server instance level and raised by the Extended Events feature. These server-level actions are grouped together in the shape of predefined groups of actions that are called Audit Action Groups. These action groups include creating or modifying a server login, successful or failed login attempt, DBCC actions and so on.

Starting from SQL Server 2012 and onwards, you can create a Server Audit Specifications using all SQL Server editions.

To be able to create a Server Audit Specifications, a SQL Server Audit should be created on the SQL Server instance, with the ability to create only one Server Audit Specification per each SQL Server Audit, as both will be created at the SQL Server instance level. Users with ALTER ANY SERVER AUDIT permission, or members of SYSADMIN fixed server role, can CREATE, ALTER or DROP server audit specifications and bind them to any audit.

The Server Audit Specification can be created using the SQL Server Management Studio, by browsing the Server Audit Specifications under the Security node, and choose the New Server Audit Specification option as shown below:

From the Create Server Audit Specification window, provide a unique and meaningful name for the Server Audit Specification then assign this Server Audit Specification to an existing SQL Server Audit, in order to write the tracked events to the specified target, as below:

After binding the Server Audit Specification to a SQL Server Audit, you need to specify the groups of actions that will be tracked and logged to the target destination. SQL Server provides us with variant types of Server level action groups to be audited as shown below:

After specifying the groups of actions, you plan to audit, the Server Audit Specification will be created, but in disabled mode. To start auditing the selected actions, you need to enable the created Server Audit Specification as below:

Server Audit Specification can be also created using the CREATE SERVER AUDIT SPECIFICATION T-SQL statement, as in the script below:

Database Audit Specifications

The Database Audit Specification is an auditing feature that can be used to collect different types of actions, performed at the database level and raised by the Extended Events feature. The tracked database level events can be included as single audit events or in the shape of predefined audit action groups.

To be able to CREATE, ALTER or DROP the Database Audit Specification, the user should be a member of the SYSADMIN fixed server role or have ALTER ANY DATABASE AUDIT SPECIFICATION on the database that will be audited. In addition, in order to create a Database Audit Specification, a SQL Server Audit should be created on the SQL Server instance, where you can create only one Database Audit Specification per each SQL Server database per each SQL Server Audit.

A Database Audit Specification can be created only in the SQL Server Enterprise edition. The Database Audit Specification can be created by browsing the Security node under the database to be audited, then right-clicking on the Database Audit Specifications and choose the New Database Audit Specification option, as shown below:

From the Create Database Audit Specification window, you will be requested to provide a unique meaningful name for the Database Audit Specification, bind it to an existing SQL Server Audit and the list of audit actions or action groups that will be audited at that database, as shown below:

SQL Server provides us with variant types of database-level actions and action groups that can be audited, as shown below:

When a single action is selected, you will be requested to provide the class, the schema and the name of the object that will be audited by the selected action, in addition to the database users or roles that will be tracked using that action.

Again, the Database Audit Specification will be created in Disabled mode and should be enabled manually in order to start tracking the audit actions, as below:

A Database Audit Specification can be also created using the CREATE DATABASE AUDIT SPECIFICATION T-SQL command, as in the script below:

Summary

For now, we are familiar with the SQL Server Audit feature components. In the next article, we will discuss how to use the SQL Server Audit feature to audit the different actions performed at the SQL Server instance and database levels. Stay tuned!

Table of contents

• Author
• Recent Posts

Ahmad Yaseen

Ahmad Yaseen is a Microsoft Big Data engineer with deep knowledge and experience in SQL BI, SQL Server Database Administration and Development fields.

He is a Microsoft Certified Solution Expert in Data Management and Analytics, Microsoft Certified Solution Associate in SQL Database Administration and Development, Azure Developer Associate and Microsoft Certified Trainer.

Also, he is contributing with his SQL tips in many blogs.

View all posts by Ahmad Yaseen

Latest posts by Ahmad Yaseen (see all)

• Azure Data Factory Interview Questions and Answers - February 11, 2021
• How to monitor Azure Data Factory - January 15, 2021
• Using Source Control in Azure Data Factory - January 12, 2021