SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing a SQL Server database involves tracking and logging events that occur on the database. This feature enables Database Administrators to implement a strategy that they can use to meet the specific risks of their SQL Server 2008 databases.
Database Audit Specification
The Database Audit Specification object is also part of a 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 modify any audit specifications in a database.
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 the 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 listed below:
- Create an audit and define the target where the audit information will be captured
- You can create either a server or database audit specification
- Enable the audit specification. By default SQL Server doesn’t enable the audit when created
- Analyse 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
Configure Server Audit in SQL Server 2008
1. Connect to a SQL Server 2008 Instance using SQL Server Management Studio.
2. In the Object Explorer, Click on Security, Right Click Audits, In the popup window select New Audit… option to create a new audit:
3. SQL Server 2008 will open up an new window Create Audit, you need to provide the details as shown in the image below:
Audit Name: – Provide the name of the audit.
Queue Delay (in milliseconds): – Amount of time in milliseconds that can elapse before the audit action is forced to be processed. A value of 0 indicates synchronous delivery. The default value is 1000 (one second).
Shut down server on audit log failure: – If this option is checked, it will force a server shut down when the instance which is trying to write to the target cannot write data to the audit target. However, the login which is issuing SHUTDOWN should have permission else the function will fail and an error message will be raised. This should be only checked in case this audit failure could compromise the security or integrity of the system.
Audit Destination: – The available options are File, Security Log and Application Log.
File Path: – Provide the folder location where the audit data is written when the audit destination is a file.
Maximum rollover files: – You can specify the maximum number of audit files to be retained by the file system. The default setting is unlimited.
Maximum file size: – You can specify the maximum size in MB for the audit file. The minimum size of the file should be 1 MB and the maximum is 2,147,483647 TB. The default setting is unlimited.
Reserve disk space: – Specifies that space is pre allocated on the disk equal to the specified maximum file size. This setting can only be used if MAXSIZE is not equal to UNLIMITED. The default setting is OFF.
4. Once the Audit is created it needs to be enabled. This can be done as shown in the image below:
5. You will see a dialog box with the success message as shown in the image below once the Audit is enabled successfully:
6. You can right click the SQLServerAuditing and then select the option View Audit Logs to view the logs:
