Server and Database Auditing in SQL Server 2008

SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing an instance of SQL Server or a SQL Server database involves tracking and logging events that occur on the system. This feature enables Database Administrators to implement a strategy that they can use to meet the specific risks of their database environments.

SQL Server Audit Components
An AUDIT will be a combination of several elements into a single package for a specific group of server actions or database actions. A DBA can record server audit action group’s per-instance and either database audit action groups or database audit actions per database. The audit event will occur whenever the auditable action is encountered by the system. The four important components of SQL Server Audit’s are SQL Server Audit, Server Audit Specifications, Database Audit Specification and Target.



SQL Server Audit
The SQL Server Audit object collects a single instance of server or database-level actions and groups of actions to monitor. The audit captures data at the SQL Server instance level. However, DBA can configure multiple audits per SQL Server instance level. Once the audit is defined DBA also needs to specify the location for the output of results, which is also called as destination. By default when the audit is created it will be in the disable state. The audit destination receives data from audit only once the audit is enabled.

Server Audit Specification
The Server Audit Specification object is also part of an SQL Server audit. A DBA can create one server audit specification per audit, because both are created at the SQL Server instance scope. The server audit specification collects many server-level action groups raised by the Extended Events feature. Audit action groups are predefined groups of actions in SQL Server 2008; these are atomic events which are exposed by the Database Engine. These actions are sent to the audit which records them in the target. Users in the sysadmin role can tamper with any audit component on the SQL Server.
Database Audit Specification
The Database Audit Specification object is also part of an SQL Server Audit. A DBA can create one database audit specification per SQL Server database per audit. The database audit specification collects database level audit actions raised by the Extended Events feature. You can also add either audit action groups or audit events to a database audit specification. Audit events are the atomic actions that can be audited by the SQL Server engine. However, Audit action groups are predefined groups of actions and both are at the SQL Server database scope. These actions are sent to the audit which records them in the target. Users in the db_owner role can tamper with any audit specifications in a database.

Target
The results of an audit are sent to a target which can be a file, Windows Security event log, or the Windows Application event log. The important thing to be noted is that writing to the Security log is not available on Windows XP. A DBA has to review the logs and archive them periodically to make sure that the target has sufficient space to write additional records. If a DBA wants to write it to the windows security log then the SQL Server service account needs to be added to the Generate Security Audit Policy. By default, Local System, Local Service and Network Service are part of this policy. The settings can be configured by using the security policy snap in secpol.msc

How to use SQL Server Audit
A DBA can use SQL Server Management Studio (SSMS) or Transact SQL to define an audit. Once the audit is created it needs to be enabled as by default SQL Server doesn’t enable it. Until the audit is enabled target will not receive entries. You can use the Event Viewer utility in Windows to read the windows security or windows application event log. You can use Log File Viewer utility in SQL Server Management Studio or the FN_READ_AUDIT_FILE inbuilt function in SQL Server to read the target file.

The process for creating and using the audit feature is:

1. Create an audit and define the target where audit information will be captured
2. You can create either a server or database audit specification
3. Enable the audit specification
4. Finally, enable the audit. By default SQL Server doesn’t enable the audit when created
5. Analysis the audit events which were captured by using the Windows Event Viewer, Log File Viewer or by using the inbuilt function FN_READ_AUDIT_FILE

Conclusion
The Audit feature of SQL Server 2008 allows Database Administrators to enable Database and Server level audits on SQL Server Instances.