SQL Server 2012 : SQL Server Audit (part 1) - Creating an Audit Object, Recording Audits to the File System

SQL Server Audit is a powerful security feature that can track virtually any server or database action taken by users, and log those activities to the file system or the Windows event log. SQL Server Audit helps meet the demands of regulatory compliance standards, which typically require that enterprise installations implement highly stringent security tactics that often include some form of auditing. You can work with SQL Server Audit using either SSMS or in T-SQL (as you will in most of the upcoming examples) using a new set of DDL statements and catalog views.

Note

SQL Server Audit was first introduced in SQL Server 2008, Enterprise edition only. SQL Server 2012 provides limited SQL Server Audit support in all editions of SQL Server. Specifically, server audit specifications can be created in all editions of SQL Server 2012, while database audit specifications still require Enterprise edition (or higher). We explain audit specifications shortly.

1. Creating an Audit Object

Our first step in using SQL Server Audit is to create an audit object with the CREATE SERVER AUDIT statement. When you create an audit object, you are essentially defining a destination to which SQL Server will record information about interesting events that occur. The specific events to be monitored are described by creating audit specifications, which you define after creating one or more audit objects.

An audit object can capture monitored events to either the file system or to the Application or Security event logs. The desired destination is specified after the TOCREATE SERVER AUDIT statement. For example, the statement in Example 1 creates an audit object named MyFileAudit that records all monitored events that will be associated with this audit object to files that SQL Server will create in the C:\Demo\SqlAudits directory (which must already exist, or the statement will fail). keyword in the

Example 1. Creating an audit object targeting the file system.

USE master
GO

CREATE SERVER AUDIT MyFileAudit TO FILE (FILEPATH='C:\Demo\SqlAudits')

Notice that it is necessary to first switch to the master database before you can create an audit object. If you don’t switch away from a user database to the master database before running this DDL statement, SQL Server will return the following error:

Msg 33074, Level 16, State 1, Line 1
Cannot create a server audit from a user database. This operation must be performed in the
master database.

When an audit object is first created, it is in a disabled state and will not audit any events until it is explicitly enabled. You cannot create and enable an audit in a single step using CREATE SERVER AUDIT, so the next step after creating an audit is to enable it using ALTER SERVICE AUDIT. The statement in Example 2 enables the MyFileAudit audit object you just created.

Example 2. Enabling an audit object.

ALTER SERVER AUDIT MyFileAudit
 WITH (STATE=ON)

Just as when first creating an audit object, you must switch to the master database before you can execute an ALTER SERVER AUDIT statement (which is not necessary here, as you haven’t yet switched away from master since creating the audit object).

The ALTER SERVER AUDIT statement can also be used with the MODIFY NAME clause to rename the audit object. The audit must be disabled before it can be renamed. For example, the statements in Example 3 rename the audit object MyFileAudit to SqlFileAudit.

Example 3. Renaming an audit object.

-- Rename the audit from MyFileAudit to SqlFileAudit, then rename it back
ALTER SERVER AUDIT MyFileAudit WITH (STATE=OFF)
ALTER SERVER AUDIT MyFileAudit MODIFY NAME = SqlFileAudit
ALTER SERVER AUDIT SqlFileAudit WITH (STATE=ON)
GO
ALTER SERVER AUDIT SqlFileAudit WITH (STATE=OFF)
ALTER SERVER AUDIT SqlFileAudit MODIFY NAME = MyFileAudit
ALTER SERVER AUDIT MyFileAudit WITH (STATE=ON)
GO

Once an audit object is created, you can define one or more audit specifications to monitor specific events of interest and associate those specifications with the audit object. Audited events captured by all audit specifications associated with an audit object are recorded to the destination defined by that audit object. We’ll talk about audit specifications shortly, but first let’s discuss some more general auditing options.

Auditing Options

You can specify several important options for your audit objects. These options, declared after the WITH keyword in either the CREATE SERVER AUDIT or ALTER SERVER AUDIT statements (or in some cases, both), are supported independently of what the audit destination is (that is, whether you’re recording to the file system or the event log).

QUEUE_DELAY

The QUEUE_DELAY option controls the synchronous or asynchronous behavior of audit processing. Specifying zero for this option results in synchronous auditing. Otherwise, this option specifies any integer value of 1000 or higher to implement asynchronous processing for better auditing performance. The integer value for this option specifies the longest amount of time (in milliseconds) that is allowed to elapse before audit actions are forced to be processed in the background. The default value of 1000 results in asynchronous processing in which monitored events are audited within one second from the time that they occur.

The QUEUE_DELAY setting can be specified when the audit object is created and then later changed as needed. To change this setting for a running audit object, you must first disable the audit object before making the change and then enable it again after.

The statements in Example 4 increase the time span for asynchronous processing of our MyFileAudit audit object by specifying the QUEUE_DELAY option with a value of 60,000 milliseconds (one minute). The audit object is temporarily disabled while the change is made.

Example 4. Setting the queue delay for an audit object.

ALTER SERVER AUDIT MyFileAudit WITH (STATE=OFF)
ALTER SERVER AUDIT MyFileAudit WITH (QUEUE_DELAY=60000)
ALTER SERVER AUDIT MyFileAudit WITH (STATE=ON)

ON_FAILURE

You can use the ON_FAILURE option to determine what course of action SQL Server should take if an error occurs while recording audited events. The valid settings for this option are CONTINUE (which is the default, and simply ignores failed attempts to audit), FAIL_OPERATION (which fails database operations that, in turn, fail to audit their associated events), or SHUTDOWN (which forcibly stops SQL Server, and requires that the login be granted the SHUTDOWN permission). This option can be specified when the audit object is created and then later changed as desired. As with QUEUE_DELAY, a running audit object must be temporarily disabled while the change is made.

The statements in Example 5-17 tell SQL Server to shut down if an error is encountered while recording audits to the MyFileAudit object. In that event, SQL Server will not restart until the problem that is preventing auditing is resolved.

Example 5-17. Setting an audit object to shut down SQL Server if it fails to audit events.

ALTER SERVER AUDIT MyFileAudit WITH (STATE=OFF)
ALTER SERVER AUDIT MyFileAudit WITH (ON_FAILURE=SHUTDOWN)
ALTER SERVER AUDIT MyFileAudit WITH (STATE=ON)

AUDIT_GUID

By default, all audits are assigned an automatically generated globally unique identifier (GUID) value. In mirroring scenarios, you need to assign a specific GUID that matches the GUID contained in the mirrored database, and the AUDIT_GUID option allows you to do that. Once an audit object is created, its GUID value cannot be changed.

STATE

The STATE option is valid only with the ALTER SERVER AUDIT statement. It is used to enable or disable an audit object, using the keywords ON and OFF. (As mentioned earlier, an audit object cannot be created in an enabled state.) As demonstrated earlier with the QUEUE_DELAY option, the STATE option cannot be combined with other audit options in an ALTER SERVER AUDIT statement.

When a running audit is disabled using STATE=OFF, an audit entry is created, indicating that the audit was stopped, when it was stopped, and which user stopped it.

2. Recording Audits to the File System

The TO FILE clause is used to record audits to the file system, as you’ve just specified for the MyFileAudit audit object. When you audit to the file system, you can specify several file options, as described here.

FILEPATH

Use the FILEPATH option to designate where in the file system SQL Server should create the files that record monitored events being audited. This can be either a local path or a remote location using a Universal Naming Convention (UNC) path to a network share. The directory specified by this path must exist, or an error will occur. Moreover, you need to make sure the appropriate permissions are granted on each directory you’ll be using, especially network shares. You cannot control the file names used for the files created by SQL Server Audit. Instead, the file names are generated automatically based on the audit name and audit GUID.

MAXSIZE

The MAXSIZE option specifies how large an audit file is permitted to grow before it is closed and a new one is opened (known as “rolling over”). The maximum size is expressed as an integer followed by MB, GB, or TB for megabytes, gigabytes, or terabytes. Note that you cannot specify a value less than one megabyte.

MAXSIZE can also be specified as UNLIMITED (which is the default value). In this case, the audit file can grow to any size before rolling over.

MAX_ROLLOVER_FILES

The MAX_ROLLOVER_FILES option can be used to automatically groom the file system as auditing data accumulates over time. The default value is UNLIMITED, which means that no cleanup is performed as new audit files are created. (This will eventually, of course, fill the disk.) Alternatively, you can provide an integer value for this option that specifies how many audit files are retained in the file system as they roll over, whereas older audit files are deleted automatically.

MAX_FILES

The MAX_FILES option (new in SQL Server 2012) forces you to manually groom the file system. When the specified number of files has been created, SQL Server will begin generating errors for any action being audited. It will not roll over, and it will not automatically delete old files as auditing data accumulates over time. Use this option when you want to ensure that manual attention is given to maintaining audit files, and SQL Server never deletes any audit files automatically.

RESERVE_DISK_SPACE

The default setting for the RESERVE_DISK_SPACE option is OFF, which means that disk space is dynamically allocated for the audit file as it expands to record more and more events. Performance can be improved (and disk fragmentation reduced) by preallocating disk space for the audit file at the time it is created. Setting this option to ON will allocate the amount of space specified by the MAXSIZE option when the audit file is created. MAXSIZE must be set to some value other than its default UNLIMITED setting to use RESERVE_DISK_SPACE=ON.