How to set up and use SQL Server Audit

In the previous part of the SQL Server auditing methods series, SQL Server Audit feature – Introduction, we described main features of the SQL Server Auditfeature – its main characteristics, what events it can audit and where the audit information is stored. We also explained two levels of auditing – the database-level and server-level, and three components necessary for using the feature – the SQL Server audit object, database audit specification, and server audit specification

In this article, we will show how you can set up and use the feature

How to set up the SQL Server Audit feature?

Requirements

To be able to create, modify, delete, and enable server audit objects, a user must be granted the ALTER ANY SERVER AUDIT or CONTROL SERVER permission on the SQL Server instance

USE master;
GO
GRANT ALTER ANY SERVER AUDIT TO AuditConfigurationLogin

Or

USE master;
GO
GRANT CONTROL SERVER TO AuditConfigurationLogin

To be able to configure database audit specifications, a user must be granted the ALTER ANY DATABASE AUDIT, ALTER, or CONTROL permissions on the audited database (AdventureWorks in this example)

USE AdventureWorks;
GO
GRANT ALTER ANY DATABASE AUDIT TO AuditConfigurationLogin

Or

USE AdventureWorks;
GO
GRANT CONTROL TO AuditConfiguration;

Or

USE AdventureWorks;
GO GRANT ALTER TO AuditConfiguration;

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:

1. To create a SQL Server Audit object, expand the Security folder in Object Explorer
2. Expand the SQL Server Logs folder

3. Select New Audit

   

4. In the Create Audit dialog, specify the audit name, audit destination, and 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

   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

   

   An audit destination can be a file (a *.sqlaudit file), security log, or application log. For writing into a file and application log, no specific permissions are needed. To be able to write into a security log, the following requirements must be met. Otherwise, there will be an error and no events will be recorded

   “The audit object access setting must be configured to capture the events. The best way to do this varies depending on your operating system.

   • In Windows Vista and Windows Server 2008, use the audit policy tool (auditpol.exe). The audit policy program exposes a variety of sub-policies settings in the audit object access category. To allow SQL Server to audit object access, configure the application generated setting.
   • For earlier versions of Windows, the audit policy tool is not available. Use the security policy snap-in (secpol.msc) instead. When available, the audit policy is preferred because you can configure more granular settings.

   The account that the SQL Server service is running under must have the generate security audits permission to write to the Windows Security log. By default, the LOCAL SERVICE and the NETWORK SERVICE accounts have this permission. This step is not required if SQL Server is running under one of those accounts.”[1]

5. 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 [AdventureWorksAudit_DDL_Access] TO FILE 
(      FILEPATH = N'D:\TestAudits\'
      ,MAXSIZE = 10 MB
)
WITH 
(      QUEUE_DELAY = 1000
      ,ON_FAILURE = CONTINUE
)
ALTER SERVER AUDIT [AdventureWorksAudit_DDL_Access]WITH (STATE = ON)
GO

1. To create a database audit specification, expand the database you want to audit in Object Explorer
2. Expand its Security folder

3. Click New Database Audit Specification

   

4. 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:
   • Access to all objects in the database by selecting SCHEMA_OBJECT_ACCESS_GROUP as the Audit Action Type

   • Schema changes on all database objects by selecting SCHEMA_OBJECT_CHANGE_GROUP as the Audit Action Type

   

   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, and object names where applicable

   

5. 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 [AW_DDL_Access_dbSpec]
FOR SERVER AUDIT [AdventureWorksAudit_DDL_Access] 
ADD (SCHEMA_OBJECT_ACCESS_GROUP) ,
ADD (SCHEMA_OBJECT_CHANGE_GROUP)
WITH (STATE = ON)
GO

After the audit object and database audit specification are enabled, every select executed on any table, will be audited, and reported

The row with action_id = SL shows a SELECT statement (i.e. access event) to the Person.Address table

The row with action_id = CR shows a new table created [2]

1. To create a server audit specification, expand the Security folder in Object Explorer
2. Right-click Server Audit Specifications

3. Select New Server Audit Specification

   

4. 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

   • Databases created, dropped, and modified by selecting DATABASE_CHANGE_GROUP as the action type

   

   Note that only one server audit specification can exist per an audit object. If you try to create the style=”margin:0px auto;display:block” second server audit specification for the same audit, you’ll get the following error message:

   

5. 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 [SQL2012_FailedLogins_DbChanges]
FOR SERVER AUDIT [AdventureWorksAudit_DDL_Access]
ADD (FAILED_LOGIN_GROUP),
ADD (DATABASE_CHANGE_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

The row with action_id = LGIF shows a login failed event. The row action_id = DR shows a DROP event – in this example of the newtest2 database

Configuring and enabling the SQL Server Audit feature and its components can be done via T-SQL and SQL Server Management Studio options. The auditing is more granular than with SQL Server Change Tracking and Change Data Capture. The events are divided into groups and only a whole group of events can be audited. In the next part of this series, we will show how to read the audited information

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

Downloads

Please download the script(s) associated with this article on our GitHub repository.

Please contact us for any problems or questions with the scripts.

References:

[1] MSDN – Write SQL Server Audit Events to the Security Log
[2] SQL Server Audit action_id List
[3] MSDN – SQL Server Audit Action Groups and Actions

Useful resources

MSDN – SQL Server Audit Action Groups and Actions
MSDN – CREATE SERVER AUDIT (Transact-SQL)