SQL Server 2008 introduces a new feature called SQL Server Audit. Auditing an instance of SQL Server involves tracking and logging events that occur on the system. This feature enables Database Administrators to implement a strategy that they can be used to meet the specific risks of their SQL Server instances.
Server Audit Specifications
The Server Audit Specification object is also part of a 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.
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 a target will not receive entries. You can use the Event Viewer utility of windows to read the windows security or windows application event log. You can also use the Log File Viewer utility of 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 audit information will be captured
- You can create either a server or database audit specification
- Enable the audit specification
- Finally, enable the audit. 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 SQL Server 2008 Instance using SQL Server Management Studio
2. In the Object Explorer, Click on Security then Right Click Audits. In the popup window select the New Audit… option to create a new audit.
3. SQL Server 2008 will open up a new window, Create Audit, you need to provide the details as shown in 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 below snippet once the Audit is enabled successfully
6. You can right click the SQLServerAuditing audit and then select the option View Audit Logs to view the logs:
