In Sql Server 2000, C2 auditing is a US government standard that monitors database security. When it is enabled, a trace will be created to collect all Security Audit related events, 21 or so of them. You can find what those events are by browsing through them in Profiler. By default, the trace file will be saved at your default data folder. It is named in the format of audittraceYYYYMMDDHHMMSS.trc. Its file size is 200 mb, and rolls over automatically when that limit is reached.

In Sql Server 2000, C2 is off by default and you cannot enable it using Enterprise Manager. You will have to do it using sp_configure and set the “c2 audit mode” property. It needs a server restart for it to take into effect.

C2 auditing still exists in Sql Server 2005, and you can enable it through Management Studio. Just right click on the server, pick properties, then go to Security tab to enable it. It works the same way as C2 in Sql Server 2000. A service restart is needed in order for it to take into effect. Although you cannot enable C2 for Sql Server 2000 in Enterprise Manager, you can enable it with Sql Server Management Studio.

Starting with Service Pack 2 for Sql Server 2005, in addition to C2, Sql Server 2005 can also use Common Criteria Compliance. You can pick this option by looking at the property page of the server, security tab. You can also do it with script by changing a new parameter using sp_configure. The new parameter is called “common criteria compliance enabled”. Again, a service restart is needed for it to take into effect. Common Criteria is a standard developed by a few countries and adapted by ISO.

Simply making the above change does not make the server Common Criteria compliant. You will also need to run a trace to audit security events, just like C2. The audit script is available here.