SQL Server 2005 – How to enable C2 Auditing

C2 Audit Mode can be configured by sp_configure or sql server management studio. Using this option you can configure sql server to record both failed and successful attempts to access sql server database and objects.  To track issues related to security policy violation and system changes C2 audit mode can be used.

In real scenario, changes are unavoidable to keep a track on such data change SQL Server introduces C2 audit functionality. It keeps track on each and every change related to SQL Server like DDL change, DML statements and audit level changes.

It’s good to have auditing mode on your database server to identify the changes done by other users.
Using this we can detect mistakes and prevent misuse of the database server only limitation is storage it takes lots of space due trace and performance of the SQL Server degrades.

Lets go through the steps involved to setup the C2 Auditing on existing test environment
Execute below command to enable C2 Audit mode on SQL Server 2005.

sp_configure ‘c2 audit mode’,1

Execute to disable C2 Audit mode
sp_configure ‘c2 audit mode’,0

Need to restart sql server services to take effect after changing the settings.
After setting C2 Audit mode, it creates trace file under default Data directory for the SQL Server 2005 with filename audittraceyyyymmddhhmmss.trc
Trace file location — C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\ audittrace20111010165303.trc

Now lets execute some DDL commands to check whether its getting capture in trace file or not.

use test
create table ct1
(col1 varchar(20) )

This above command should be capture in trace file which is already set.

Now open trace file to confirm. To open trace we have two options available in SQl Server 2005
1. Using Profiler
2. T-SQL command

Using Profiler

• Click on Start – Run
• Now type Profiler in Run option and hit enter, it will open profiler window
• Click on File — Open — Trace File (to load the existing trace file in profiler window).
• Select the Trace file to check the change
You can see the detail information regarding the change like login name, command and time of execution
and NT user name etc.

Using T-SQL Commands
• Alter option to view trace data using SSMS

We can use below command to check the trace data in table format using SSMS.

Select * from fn_trace_gettable(‘C:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace20111010165303.trc’,default)
where DatabaseID = 16

C2 Audit mode provides good option to keep a eye on daily changes done by user on production. Limitation in C2 audit is when trace file grows out of limit it shutdown sql server so apply carefully.

Leave a Reply




7 − = 6