What is C2 Audit tracing in SQL Server


In this article I will discuss the C2 audit trace option in SQL Server.C2 is security-auditing level defined by the US Government’s Trusted Computer Security Evaluation Criteria (TCSEC) program. Microsoft added the C2 audit mode option to address government requirements that certain contractors document database activity and possible security policy violations. So what does this audit trace do and how can we enable\disable the audit trace.

This audit captures the following information on the SQL server.
1) End User Activity (All SQL commands, logins and logouts)
2) Security Events (Grant/Revoke/Deny, login/user/role add/remove/configure)
3) Utility Events (Backup/Restore/ Bulk Insert/BCP/DBCC commands)
4) Server Events (Shutdown, Pause, Start)

Let us now see how we can enable this audit trace
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'1'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL Server

Once you have restarted the SQL Server, you will find a new trace has kicked off. If you run sys.traces on the server you will find an audit trace that is running
c2If you browse to the location of the trace and open the trace file and view the properties of the trace then you will see as below
c2_1So how can we stop this trace. If we try the traditional way of stopping a trace then it will result in an error. Snapshot below.
C2_3Follow the below steps to stop this trace and remove its definition from the server
Step 1: Run the below code

EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Step 2: Restart the SQL server

Few important points that we have to keep in mind before implementing this audit trace
1) This is resource consuming . Implement with caution on a busy OLTP system.
2) The max size of the trace file is set to 200 Mb. You cannot change this.
3) Once the size is breached, a new trace file generated.
4) If the directory in which logs are being saved runs out of space, SQL Server will shut itself down. If auditing is set to start automatically, you must either restart the instance with the -f flag, or free up additional disk space for the audit log.
5) You cannot filter out events from this trace property, it captures everything.

Advertisements

Tagged: , , , , ,

2 thoughts on “What is C2 Audit tracing in SQL Server

  1. yadav@SQL Database audit chicago May 13, 2014 at 10:50 am Reply

    very detailed article indeed Bodhisatya . Also i would also i like to have a query that “When C2 auditing is enabled in sql server and the drive holding the auditing log file runs out of disk space”
    Which turn out that the server shutdowns and restarts and the c2 auditing is automatically disabled

    Like

  2. AJ August 14, 2014 at 2:33 pm Reply

    Hi,

    I have a server that i can clearly see an audit trace running with exactly the file sizes you mentioned, but i cannot turn it off as the server says the c2 audit trace feature is already off, I have restarted the server but it is still running. I was wondering if you could help me resolve this issue?

    Kind Regards,

    AJ

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: