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
If 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
So 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.
Follow 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.
Applied SQL... 