SQL Server 2008 Auditing
Once a year you get that email or knock on the door from the auditor. It didn’t used to be this way, but Enron and other scandals have changed the way we track our user’s actions. I still wake up in cold sweats with memories of the audits I used to have to do yearly. Previously, in order to get rudimentary SQL Server logging it took writing complicated triggers that fire when certain actions are performed. Even then there were significant limitations to the data that you could collect. You couldn’t write a trigger against a select event. SQL Server 2008 changes all of this with the introduction of SQL Server Audit.
Who’s it for? DBA’s and IT Pros who need to audit activity on SQL Servers.
When does it ship? Auditing is part of SQL Server 2008 and will ship as part of the SQL Server 2008 product.
What does it do? Improves compliance and security by allowing you to customize audit activity on your SQL Server Data. SQL Server 2008 provides the tools and processes you need to effectively enable, store and view audit data stored on SQL Server 2008.
What are the components of SQL Audit? There are several components that you create/configure that makes up a single package that audits a server or database actions. These include:
· SQL Server Audit: This is the object that collects server or database audit actions and groups of actions to monitor. When you create an audit you choose where the output of the audit results are stored as well as the maximum file size. You can have multiple audits per SQL Server instance
· Server Audit Specification: This objects belongs to the Audit you created above. You can create one server audit specification per SQL Server per audit. This specification collects many server-level action groups raised by the Extended Events feature.
· Audit Action Groups: Audit Action Groups are predefined groups of actions exposed by the Database Engine. An example here would be the SERVER_ROLE_MEMBER_CHANGE_GROUP which will raise an audit event whenever a login is added or removed from a fixed server role.
· Database Audit Specification: This object like the Server Audit Specification belongs to a SQL Server Audit. You can have one database audit specification per database, per audit. You can add either audit action groups or audit events to a database audit specification.
· Audit Events: Audit Events are specific database actions that can be audited by the SQL Server. A good example of an audit action is the ability to capture and audit when a SELECT statement is run against a specific table in a database.
· Target: The results of an audit are sent to a target. The target can be a file, the Windows Security Event log, or the Windows Application event log.
What is the process for creating an audit? The basics of creating and using an audit includes:
· Create the audit and define the location of the audit data.
· Create a server audit specification or a database audit specification, map it to the audit created above. Enable the audit specification (these are created disabled by default).
· Enable the audit (also created disabled by default)
· Read the audit events using the Windows Event View, the log file Viewer in SQL Management Studio, or the FN_READ_ADUIT_FILE TSQL function.
Anything I need to be aware of that could cause problems?
· When you create an audit, you have the ability to shutdown a server if a particular audit fails. If this occurs the MSG_AUDIT_FORCED_SHUTDOWN event is written to the log. If you need to bypass an audit-induced shutdown, you can start SQL Server in Single User mode using the –m flag.
· There are also considerations that you need to be aware of when attaching a database with an Audit already defined. If you attach a database that has an existing audit specification and specifies a GUID that does not exist on the server, no audit events will be recorded. To correct this, you can alter the audit specification and point it to an existing audit or use the CREATE SERVER AUDIT WITH GUID command to create a new audit with the GUID from the other server.
· If you use database mirroring and have a database audit specification defined you must do the following:
- The mirror server must have an audit with the SAME GUID to enable the audit. Use the CREATE SERVER AUDIT WITH GUID command.
- The mirror server service account must have appropriate permissions to the location where you are writing the audit information.
- If you are writing events to the Windows Event log, the security policy on the mirror server must allow for service account access to the security or event log.
Get started