SQL Server 2008 – All Action Auditing (AAA)

In SQL Server 2005, this was one of the area were there was significant enhancement from SQL Server 2000. DDL Trigger and default trace was introduced in SQL Server 2005 to provide administrator more controlled Audit without effecting the system performance. Still , the SQL Server community was not convinced with the auditing features of SQL Server 2005. For instance, DDL trigger was not able to track all the DDL Trigger events like sp_Rename. Running profiler for auditing was just can not be done because Profiler was not able to trace the only required data. So what was the solution provided in earlier versions to have a proper audit system? Nothing but go for third party tools.

In SQL Server 2008, Audit is a first class object in the server. You have complete set of DDL statement to manage Audit feature. Audit can also make the audited information entries into file , windows application log or windows security log. Security log entry benefit is that, even the administrator can not change the entries. Another good thing about this feature is , you have very granular level control. Ie. You can audit very specific action on an object for example, who is running select query on EmployeeSalary table , salary column. This was not possible in profiler.

Note : As on today, the information is that this feature will only be available in Enterprise Edition of SQL Server 2008.

There are two categories of actions for audits:
• Server-level. These actions include server operations, such as management changes and logon and logoff operations.
• Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.

1. How to configure Audit using Management Studio

Create Audit


Important

• File path : Only the folder has to be specified. The File filename has to be unique so, the system will provide the name with timestamp.
• Folder : The folder must already be existing
• You can mention the max rollover files and size.






2. Create Server Audit Specification (or Database Audit Specification as per your requirement)



Audit Action Type : There are many action type. Select the appropriate one.











3. Ensure that the Audit and Audit Specification is enabled
Select is_state_enabled,name From sys.server_file_audits
Select is_state_enabled,Name From sys.server_audit_specifications
Select is_state_enabled,Name From sys.database_audit_specifications





















The Audit file viewer report will looks like this.













4. Script to Create Audit Objects
GUI method is very simple and if somebody wants to do it by TSQL Script, its even simple. You can script the Audit objects the same way we do for any other objects. Just right click on the Audit and Server Audit specification which we have created and Script it. You can keep the script in Version control or so. Here is the script I have generated after configuring the audit process through GUI.

--Create Audit
/****** Object: Audit [Audit-20080612-194600] Script Date: 06/13/2008 09:43:25 ******/
CREATE SERVER AUDIT [Audit-20080612-194600]
TO FILE
( FILEPATH = N'D:\SQLAudit\'
,MAXSIZE = 100 MB
,MAX_ROLLOVER_FILES = 10
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = CONTINUE
,AUDIT_GUID = 'c314f405-ae9c-4f4b-947f-041070683c9d'
)
GO
--Create Server Specification
USE [master]
GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]
FOR SERVER AUDIT [Audit-20080612-194600]
ALTER SERVER AUDIT SPECIFICATION [ServerAuditSpecification-20080612-195650]
ADD (FAILED_LOGIN_GROUP),
ADD (SUCCESSFUL_LOGIN_GROUP)
GO

Important Datebase system objects Related to Auidt feature
fn_get_audit_file :Returns information from an audit file created by a server audit.
Eg.
Select Statement,* From fn_get_audit_file ('D:\SQLAudit\*',null,null)
--Will retrun all the file reports in the specified folder
sys.server_file_audits
sys.server_audit_specifications
sys.database_audit_specifications


Script to List all the Database Specification created from all the database
set nocount on
declare @My_Return int
declare @command varchar(1000)
set @command = 'use [?] Select *from sys.database_audit_specifications '
print @command
exec @My_Return = master.dbo.sp_MSforeachdb @command

Audit Trucnate Table Command
DDL Trigger in 2005 and 2008 still not able to track TRUNCATE Table Command. But Audit does that. SCHEMA_OBJECT_CHANGE_GROUP Database Specification will track your Truncate command too.

Summary
There are DDL statements to configure Audit like CREATE SERVER AUDIT AND CREATE SERVER AUDIT SPECIFICATION kind. But GUI method is pretty simple and straight forward. You also have these objects exposed through SMO. Because of the new events introduced to track the information, which is inside the engine unlike SQL Trace, the performance will be faster compare to SQL Trace. Any audit system will consume resource, but what All Action Audit (AAA) feature of SQL Server 2008 offers is, very granular level audit control with negligible performance hindrance.