Here are a few more confusing things about auditing and traces:
C2 auditing was augemented with Common Criteria auditing. The C2 security standard (a US standard) has been superceded by the Common Criteria EAL1+ standard (an international standard). There is also an extension available for CC EAL4+ compliance.
C2 Compliance and Common Critieria auditing are both implemented through SQL Trace. So it's not a choice of one or the other, it's a choice of roll-your-own vs. using what's in place.
You can configure SQL Server 2008 R2 use enable either C2 auditing or Common Criteria auditing through sp_configure. C2/Common Criteria auditing has very little in the way of configurability. It's either on or off. You can configure the location of the tracefile (defaults to SQL Server's directory) and the size of the file, but these are done through T-SQL. I don't believe these options are available thorugh SSMS. Traces are very configurable, using either SQL Server Profiler or SQL Trace. You can also configure traces using stored procedures:
sp_trace_create
sp_trace_setevent
sp_trace_setfilter
A common set of advice is to only enable C2 Auditing or Common Criteria auditing ONLY if you are specifically required to be compliant with a specific standard. Frankly, these generate a TON of information, most of which will never be used. In the cases of C2 and Common Criteria, if audit logs can't be written (due to lack of disk space), the server shuts itself down. If auditing is set to start automatically, SQL Server will refuse to come back up until there is space for audit records to be written.
If you need auditability for something other than specific compliance with C2 or Common Criteria, create a trace or set of traces which will provide the information that you need. If you set the traces to start automatically and use the option to shut down SQL Server if audit records can't be written, then be absolutely sure that you have enough disk space or you might get an unpleasant surprise.
commented on Jan 2 2012 1:55PM