Create C2 Native Auditing for SQL Server

Audit Trace Procedure
1. Enable C2 Auditing from DB Options.
2. Restart SQL Server.
3. Check Location of Audit Logs in MSSQL folder in C:\
4. Create AuditDB database with log and datafile in location D:\
5. Create temp_trc table in AUDITDB database using below command :

use auditdb
SELECT IDENTITY(int, 1, 1) AS RowNumber, * INTO temp_trc FROM fn_trace_gettable(‘C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\audittrace000001.trc’,default) where DatabaseName=’APPDB’and ApplicationName NOT LIKE ‘%%Repl-LogReader%%’ and TextData NOT LIKE ‘%%select%%’ and TextData NOT LIKE ‘%%FETCH%%’ and textdata NOT LIKE ‘%%merge%%’ and ApplicationName NOT LIKE ‘%%HostReplication%%’;
6. Create a Table audit_trc using below command.

use auditdb
select * into audit_trc from temp_trc;

7. Create folder “d:\tracelog”, Copy the files audit_trace.bat and audit_trace.sql to the server.
8. Create Task schedule for running audit traces every 8 hrs daily.

Audit Log Retention Procedure
1. Copy file Clean_Audit_Trace.sql and Clean_Audit_Trace.bat to “c:\tracelog” on Server.
daily (the script delete’s rows older than 1 days).

2. Schedule Clean_Audit_Trace.bat to run as batch job daily at 12:00 AM


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s