SQL Server Audit feature – Introduction

In the previous parts of this series, we described two SQL Server auditing features – Change Tracking and Change Data Capture. We showed their characteristics, how to enable them, how to read the results, and listed their advantages and disadvantages

In this article, we will present the third SQL Server auditing feature introduced in SQL Server 2008 – SQL Server Audit

The SQL Server Audit feature

The feature is built on top of Extended Events and audits server and database events and groups of events. Database level auditing is supported only by the Enterprise and Developer editions. In SQL Server 2012, all editions support server level audits

The feature audits events on two levels – the server-level and database-level. Each audit level is configured independently, which provides flexibility and fine-grained auditing

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

“An audit is the combination of several elements into a single package for a specific group of server actions or database actions. The components of SQL Server audit combine to produce an output that is called an audit” [1]

The audit information can be saved in a binary file, Windows event log, or SQL Server event log. The output file is also called a target. As the events pile up, the target files can grow large, so it’s recommended to regularly review and archive them. To prevent audit information leak, make sure that access to these files is granted only to privileged users

Audit components

The following components must be configured and enabled for auditing

A SQL Server audit object is an object created on the audited SQL Server instance. It defines the target for the server-level and database-level audited events. Multiple audits can be defined on a single SQL Server instance, each audit will store the audit information into its own target file. The target type (a binary file, security log, or application log) and path are defined. The audits don’t specify what is recorded – no event types, objects and databases are specified here

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, and on all databases on the instance. Filtering by database-level actions is not possible. One Server audit specification can be created for one audit specification

“The server audit specification collects many server-level action groups raised by the Extended Events feature. You can include audit action groups in a server audit specification. Audit action groups are predefined groups of actions, which are atomic events occurring in the Database Engine. These actions are sent to the audit, which records them in the target.” [1]

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

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 on MSDN [2]

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. This specification provides more audit information about the event and finer filtering than a server-level specification

Like in the server-level specifications, 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)

Some of the action groups on the server-level and database-level are identical, but they are raised in different scenarios. For example, if the DATABASE_LOGOUT_GROUP events are audited on the server-level, the event is raised whenever a user logs out of any database on the audited SQL Server instance. When audited on the database-level, the same event is raised only when a user logs out of the specific database

Changes of the SQL Server Audit feature can also be audited on both server and database levels using AUDIT_CHANGE_GROUP

Database-level actions provide more granular filtering than server-level ones. You can find the complete list of all database-level audit action groups and events on MSDN [2]

The results

All target types (a binary file, SQL Server event log, and Windows event log) 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

The audit information contains the time of the action, the session_id (SPID), the database, server, and object names (where applicable) affected by the action. The host name and/or IP address of the machine where the event occurred are not captured

The SQL Server Audit feature provides more details about the SQL Server instance and database events than the Change Tracking and Change Data Capture features. The feature provides the information about who, what and when, and enables granular filtering of the audited events. It also tracks execution of the SELECT statement. Three different output files types are available, all stored in the central location for easier management

The feature doesn’t provide the host name or IP address of the machine that raised the event, and for some actions (e.g. SELECT, and UPDATE), the captured information doesn’t provide the details about the data affected by the statement

In the next part of the auditing SQL Server data changes series, How to set up and use SQL Server Audit, we’ll show how to create the feature components, enable auditing, and read the results

All articles in this series:

1. What is SQL Server Change Tracking and how to set it up?
2. How to read SQL Server Change Tracking results
3. SQL Server Change Data Capture (CDC) – Introduction
4. How to enable and use SQL Server Change Data Capture
5. How to analyze and read Change Data Capture (CDC) records
6. SQL Server Audit feature – Introduction
7. How to set up and use SQL Server Audit
8. How to analyze and read SQL Server Audit information
9. Auditing SQL Server data changes – the centralized solution

References:

[1] MSDN – SQL Server Audit (Database Engine)
[2] MSDN – SQL Server Audit Action Groups and Actions

Useful resources:

Auditing database-level objects in SQL Express 2012
MSDN – SQL Server Audit Records
MSDN – Create a Server Audit and Server Audit Specification