SQL Server Audit is a powerful security feature available in SQL Server 2008 and 2008 R2 (Enterprise edition only) 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. This feature helps meet the demands of regulatory compliance standards, which typically require that our enterprise installations implement highly stringent security tactics that often include some form of auditing. You can work with SQL Server Audit using either SQL Server Management Studio or in T-SQL using a new set of DDL statements and catalog views.
We’ll mostly cover only the T-SQL DDL auditing statements and catalog views, In SQL Server Management Studio, you’ll find SQL Server Audit support beneath the Security nodes at both the server and database levels.
Creating an Audit Object
Our first step to using SQL Server Audit is to create an audit object with the CREATE SERVER AUDIT statement. When we create an audit object, we 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 we 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 TO keyword in the CREATE SERVER AUDIT statement. For example, the following statement 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:\SqlAudits directory (which must already exist, or the statement will fail):
USE master
GO
CREATE SERVER AUDIT MyFileAudit TO FILE (FILEPATH='C:\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 following statement enables the MyFileAudit audit object we just created:
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 we 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 following statements rename the audit object MyFileAudit to SqlFileAudit:
ALTER SERVER AUDIT MyFileAudit WITH (STATE=OFF)
ALTER SERVER AUDIT MyFileAudit MODIFY NAME = SqlFileAudit
ALTER SERVER AUDIT SqlFileAudit WITH (STATE=ON)
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 following statements increase the time span for asynchronous processing of our MyFileAudit audit object by specifying the QUEUE_DELAY option with a value of 60000 milliseconds (one minute). The audit object is temporarily disabled while the change is made.
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) or SHUTDOWN (which 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 following statements tell SQL Server to stop the auditing process for all audit specifications associated with the MyFileAudit audit object if an error is encountered while recording audits:
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 unique 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.
Recording Audits to the File System
The TO FILE clause is used to record audits to the file system, as we’ve just specified for our 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[kws4] 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 zero, 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, while older audit files are deleted 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.
Recording Audits to the Windows Event Log
You can also create audit objects that are recorded to the Windows event log. To send audit entries to either the Application or Security event logs, specify TO APPLICATION_LOG or TO SECURITY_LOG. For example, this statement creates an audit object that is recorded to the Application event log:
CREATE SERVER AUDIT MyEventLogAudit TO APPLICATION_LOG
Auditing Server Events
You create a server audit specification to monitor events that occur at the server level, such as failed login attempts or other actions not associated with any particular database. As already described, we associate our specifications to an audit object configured for recording to either the file system or the event log.
Use the CREATE SERVER AUDIT SPECIFICATION statement to create a specification that monitors one or more server-level events for auditing. The FOR SERVER AUDIT clause links the specification with an audit object that defines the destination, and ADD clauses list the server-level audit action groups to be monitored. Similarly, the ALTER SERVER AUDIT SPECIFICATION statement can be used to ADD additional action groups to be monitored or DROP existing ones that should no longer be monitored.
Unlike audit objects, audit specifications can be created and enabled at the same time using CREATE SERVER AUDIT SPECIFICATION with the STATE=ON option. The following statements create and enable a server audit specification that records all successful logins and failed login attempts to the file system (to the path C:\SqlAudits, as defined by the audit object MyFileAudit created earlier in the section):
CREATE SERVER AUDIT SPECIFICATION CaptureLoginsToFile
FOR SERVER AUDIT MyFileAudit
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
WITH (STATE=ON)
After executing this statement, all login attempts made against the server (whether or not they succeed) will be audited to the file system. If we later decide to also audit password changes and to stop auditing successful logins, we can alter the specification accordingly (as with audit objects, audit specifications must be disabled while they are being changed):
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=OFF)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile
ADD (LOGIN_CHANGE_PASSWORD_GROUP),
DROP (SUCCESSFUL_LOGIN_GROUP)
ALTER SERVER AUDIT SPECIFICATION CaptureLoginsToFile WITH (STATE=ON)
You’ll find a complete list of server-level action groups that can be monitored for auditing in SQL Server Books Online. There are more than thirty-five action groups, including backup and restore operations, changes in database ownership, adding or removing logins from server and database roles, or creating, altering, or dropping any database object—just to name a few.
Auditing Database Events
A database audit specification is conceptually similar to a server audit specification. Both specify events to be monitored and directed to a designated audit object. The primary difference is that database audit specifications are associated with actions against a particular database, rather than server-level actions.
Note Database audit specifications reside in the database they are created for. You cannot audit database actions in tempdb.
The CREATE DATABASE AUDIT SPECIFICATION and ALTER DATABASE AUDIT SPECIFICATION statements work the same as their CREATE and ALTER counterparts for server audit specifications that we just examined. Like server audit specifications, database audit specifications can be created in an enabled state by including the clause WITH (STATE=ON).
About fifteen database-level action groups can be monitored for auditing, such as changes in database ownership or permissions, for example. You can find the complete list in SQL Server Books Online. In addition, you can monitor for specific actions directly on database objects, such as schemas, tables, views, stored procedures, and so on. The seven database-level audit actions are SELECT, INSERT, UPDATE, DELETE, EXECUTE, RECEIVE, and REFERENCES.
For example, the following statement creates and enables a database audit specification in the MyDB database named CaptureDbActionsToEventLog:
USE MyDB
GO
CREATE DATABASE AUDIT SPECIFICATION CaptureDbActionsToEventLog
FOR SERVER AUDIT MyEventLogAudit
ADD (DATABASE_OBJECT_CHANGE_GROUP),
ADD (SELECT, INSERT, UPDATE, DELETE
ON SCHEMA::dbo
BY public)
WITH (STATE=ON)
The FOR SERVER AUDIT clause specifies that the monitored events should be directed to the server object MyEventLogAudit, which we created earlier to record audits to the application event log. The first ADD clause specifies DATABASE_OBJECT_CHANGE_GROUP, which watches for DDL changes made to any database object. This effectively audits any CREATE, ALTER, or DROP statement made against any object (table, view, and so on) in the database. The second ADD clause audits any DML action (SELECT, INSERT, UPDATE, or DELETE) made against any object in the dbo schema by any public user (which is every user).
You get very fine-grained control with database audit specifications. The ON clause in the preceding statement causes every object in the dbo schema to be audited, but it could just as easily be written to audit DML operations on specific tables if desired. Similarly, rather than auditing all users by specifying the public role in the BY clause, individual users and roles can be listed so that only
DML operations made by those particular users are audited.
Viewing Audited Events
After you enable your audit objects and audit specifications, SQL Server takes it from there. Audits for each monitored event
declared in your audit specifications are recorded automatically to the destinations you’ve specified in your audit objects. After accumulating several audits, you’ll want to view them, of course.
Audits recorded to the event log can be examined using the Event Viewer (available from Administrative Tools in Control Panel). Audits recorded to the file system are not stored in plain text files that can simply be viewed in Notepad. Instead, they are binary files
that you can view in one of two ways. One way is from inside SQL Server Management Studio. Right-click the desired audit object beneath the Security node at the server instance level (not the Security node at the database level) in the Object Explorer, and then choose View Audit Logs.
Each audit entry contains a wealth of detailed information about the event that was captured and recorded. This includes date and time stamp, server instance, action, object type, success or failure, permissions, principal name and ID (that is, the user that performed the audited action), database name, schema name, object name, the actual statement that was executed (or attempted), and more.
Alternatively, you can use the new table-valued function (TVF) named sys.fn_get_audit_file. This function accepts a parameter that points to one or more audit files (using wildcard pattern matching). Two additional parameters allow you to specify the initial file to process and a known offset location to start reading audit records from. (Both of these parameters are optional but must still be
specified using the keyword default.) The function then reads the binary data from the file(s) and formats the audit entries into an ordinary table that gets returned back. For example:
SELECT event_time, database_name, schema_name, object_name, statement
FROM sys.fn_get_audit_file('C:\SqlAudits\*.sqlaudit', default, default)
Here are some abbreviated results from this query:
event_time database_name schema_name object_name statement
----------------------- ------------- ----------- ----------- ------------------------------------
:
2011-07-01 19:33:19.381 MyDB dbo CREATE TABLE TestTable(TestId...
2011-07-01 19:33:45.789 MyDB dbo TestTable INSERT INTO [TestTable] value...
2011-07-01 19:33:45.789 MyDB dbo TestTable SELECT * FROM TestTable
2011-07-01 19:33:45.789 MyDB dbo TestTable UPDATE [TestTable] set [TestI...
2011-07-01 19:33:45.789 MyDB dbo TestTable UPDATE [TestTable] set [TestI...
2011-07-01 19:33:45.789 MyDB dbo TestTable DELETE [TestTable] WHERE [Te...
2011-07-01 19:33:45.789 MyDB dbo TestTable DELETE [TestTable] WHERE [Te...
:
You can of course easily filter and sort this data using WHERE and ORDER BY, as well as INSERT the data elsewhere. The sys.fn_get_audit_file function represents an advantage that auditing to the file system has over auditing to the Windows event log, as there is no equivalent function provided for querying and manipulating audits in the event logs.
Querying Audit Catalog Views
Finally, SQL Server provides a number of catalog views that you can query for information about the audits and audit specifications running on any server instance. These audit catalog views are listed below.
| Catalog View Name |
Description |
| sys.server_file_audits |
Returns all of the audit objects that are recorded to the file system |
| sys.server_audit_specifications |
Returns all of the server-level audit specifications |
| sys.server_audit_specification_details |
Returns detailed monitored event information for all of the server-level audit specifications |
| sys.database_audit_specifications |
Returns all of the database-level audit specifications |
| sys.database_audit_specification_details |
Returns detailed monitored event information for all of the database-level audit specifications |
| sys.dm_server_audit_status |
Returns the status of each audit object |
| sys.dm_audit_actions |
Returns every audit action that can be reported on and every audit action group that can be configured |
| sys.dm_audit_class_type_map |
Returns a table that maps the class_type
field in the audit log to the class_desc field in sys.dm_audit_actions |
Lenni's Technology Blog 