Introduction
Auditing is the monitoring and recording all user actions on a database. You can base auditing on individual actions, such as database backup, change of user logins, insert etc. or on combination of factors.
Why Audit?
Though auditing has become a popular buzzword, many DBAs and developers still do not appreciate the requirement for auditing. Properly implemented, auditing can achieve the below:
1. Ensure accountability for all actions – in particular server, database, schema, and table operations.
2. Discourage users from inappropriate actions due to their accountability.
3. Assist the investigation of suspicious activity.
4. Notify an auditor of the actions of an unauthorized user.
5. Monitor and gather data about specific database activities.
6. Detect problems with an authorization or access control implementation.
7. Ensure compliance with legal requirements, such as:
· Sarbanes-Oxley Act
· Health Insurance Portability and Accountability Act (HIPAA)
· International Convergence of Capital Measurement and Standards: a Revised Frame work
· Japan Privacy Law
· European Union Directive of Privacy and Electronic Communications
· Data Protect Act (UK)
Auditing in SQL Server
Auditing is not a new a feature in SQL Server 2008. Though the path is different in SQL Server 2008, there are options to implement auditing in previous SQL Server versions. In addition to using DML and DDL triggers, there is also a C2 level of Auditing. You can also use SQL Server Profiler to gather auditing information as well as using replication for data auditing.
Although there are several methods to assist in auditing, prior to SQL Server 2008 there was no unified tool that shipped with SQL Server. SQL Server 2008 introduces a new Auditing feature which is a server object that users can configure.
Auditing in SQL Server 2008
Firstly, create a new Audit and configure the audit file location. You can configure this in two ways, by using SQL Server Management Studio (SSMS) or by using T-SQL.
Audit Configuration Using SSMS:
There is a new Audit node in SSMS.
By right clicking Audit and selecting New Audit, you can create a new audit as below:
The default name is Audit plus date and time
Queue delay specifies the amount of time in milliseconds that can elapse before audit actions are forced to be processed. A value of 0 indicates synchronous delivery. The default minimum value is 1000 which is equal to 1 second.
Shut down server on audit log failure will shut down the SQL Server. This feature is valuable auditing is essential on an operating SQL Server.
Audit is where the audit is created. There are three options for the destination; File, Security Log and Application Log. Unfortunately, you can select only one of them, you cannot set multiple options. If you want to have multiple options you will need to create multiple audits.
The rest of the settings apply only when you select the File option as the Audit destination and these options are self-explanatory.
After creating the audit, you will need to enable it. Just right click it select Enable.
Audit Configuration Using T-SQL:
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:54:33 ******/
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N’Audit-20091125-113200′)
DROP SERVER AUDIT [Audit-20091125-113200]
GO
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:54:33 ******/
CREATE SERVER AUDIT [Audit-20091125-113200]
TO SECURITY_LOG
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)
GO
The above script demonstrates how to create an Audit to Security Log with the shut down option on and the below code creates an audit with a binary file.
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
IF EXISTS (SELECT * FROM sys.server_audits WHERE name = N’Audit-20091125-113200′)
DROP SERVER AUDIT [Audit-20091125-113200]
GO
USE [master]
GO
/****** Object: Audit [Audit-20091125-113200] Script Date: 11/25/2009 11:57:07 ******/
CREATE SERVER AUDIT [Audit-20091125-113200]
TO FILE
( FILEPATH = N’C:Audit’
,MAXSIZE = 2048 MB
,MAX_ROLLOVER_FILES = 2147483647
,RESERVE_DISK_SPACE = OFF
)
WITH
( QUEUE_DELAY = 1000
,ON_FAILURE = SHUTDOWN
)
GO
The audit information in the above scripts are saved in the sys.server_audits system table.