C2 Audit Mode

2009-01-12T19:40:00.000-08:00

Microsoft® SQL Server includes a C2 Audit Mode that can be configured through SQL Server Management Studio or with the stored procedure sp_configure. Selecting this option configures the server to record both successful and unsuccessful attempts to access statements and objects.
C2 Audit Mode always enables all event classes in the Security Audit category. For most production sites, auditing all event classes results in an unacceptable performance degradation of the SQL Server. If you use C2 Audit mode, do not enable all event classes.
For MS SQL 2005, C2 Audit Mode data is saved in a file in the \MSSQL\Data directory of default instances, and the \MSSQL$instancename\Data directory of named instances. When the audit log file reaches its size limit of 200 MB, the SQL Server creates a new file, closes the old file, and writes all new audit records to the new file. This auditing process continues until the audit data directory fills up or auditing is turned off.
Important:
C2 Audit Mode saves a large amount of event information to the log file, which can grow quickly. If the data directory in which logs are being saved runs out of space, the SQL Server shuts itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag (which bypasses auditing), or free up additional disk space for the audit log.
Enabling C2 Audit Mode
Enabling the C2 audit mode parameter in SQL Server 2005:

1. Connect to SQL Server 2005 using the SQL Server Management Studio.
2. In the Object Explorer pane, right-click a [server] , and then select Properties .
3. In the Server Properties window, select the Security page.
4. In the details pane, select the Enable C2 audit tracing check box.
5. Click OK to close the Server Properties window.

You must be a member of the sysadmin fixed server role to perform this procedure.
1. Run the stored procedure sp_configure with Advanced Options enabled followed by the RECONFIGURE command:
2. sp_configure 'show advanced options', 1;
3. RECONFIGURE
4. To enable auditing, run sp_configure with the c2 audit mode parameter set to 1:
5. EXEC sp_configure 'c2 audit mode', 1
RECONFIGURE
6. After changing the C2 audit mode setting, restart the server.
Note:
If you have enabled C2 auditing, you might want to disable login auditing on the Security tab of the SQL Server Properties window in SQL Server Enterprise Manager (described previously). If you do not disable this feature, the auditing functions record the same type of event twice, unnecessarily degrading server performance.

database-studying

C2 Audit Mode