Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Basics of C2 Auditing

By Dinesh Asanka,

Audit your Servers

What ever the system that we are taking about, whether it is an ERP system or CRM tool or any database system auditing has become essential part of that system.. Auditing will allow you to:

  1. Detect the misuse and prevent it being happened again.
  2. Take legal actions against the people who are responsible for the misuse.
  3. Take recovery action from the point where something has gone wrong.

There are many ways in which you can setup an auditing trail and many places in which to get it up. However, we SQL Server guys (sorry, gals as well) have a somewhat easy way of doing this thanks to a new feature of Microsoft SQL Server 2000, which is called the C2 audit mode option.

What is C2

The US Department of Defense has established a set of ratings applicable to security levels of computer systems, based on their capabilities in regard to auditing and discretionary access control. Accordance with this ratings Microsoft SQL Server 2000 has C2 audit mode option. Every version of SQL Server 2000 is C2-certified (provided it is run on a C2-certified computer and network). Accordingly, SQL Server guarantees that its auditing procedures satisfy C2 requirements -- for example, storing generated data only on a NTFS partition.

C2 auditing records information goes beyond server-level events, such as shutdown or restart, successful and failed login attempts, extending it to successful and failed use of permissions when accessing individual database objects and executing all Data Definition, Data Access Control, and Data Manipulation Language statements.

The audit information contains the timestamp, identifier of the account that triggered the event, target server name, event type, its outcome (success or failure), name of the user's application and Server process ID of the user's connection and name of the database .

How it is done

I can't believe why there is no UI is given to enable this option. However, Microsoft might have fair reason for doing this. There are some serious implications if you enable this option.

Anyway it can be done via my favorite tool, SQL Query Analyzer. Before you enable (or disable) this option you must be a member of sysadmin group. Before attempting to set the 'c2 audit mode' configuration option, you must enable the 'show advanced options' configuration option. This is performed using the following command:

USE master
EXEC sp_configure 'show advanced option', '1'
RECONFIGURE
To enable the feature, set 'c2 audit mode' to 1 using the following command:
sp_configure 'c2 audit mode', 1
go
To disable the feature, set 'c2 audit mode' to 0 using the following command:
sp_configure 'c2 audit mode', 0
go

After setting the value, you must stop and restart the server for C2 audit mode to be take effect. Now whenever you perform an Insert, Select or anything other statement, it will log the event in the trace files. These files reside in the \mssql\data directory for default instances of SQL Server 2000, or the \mssql$instancename\data directory for named instances of SQL Server 2000 as AuditTrace_yyyymmddhhmmss.trc, where the second part of the name indicates date and time when the log file was created. For example, some file names on my system are audittrace_20041007153315.trc and audittrace_20041007163855.trc. The size of a log is limited to 200MB, but new traces files are generated whenever the old one is full. Also a new one is generated on the shutdown of SQL Server with another one on startup.

One thing to be aware of is that SQL Server will stop if it cannot write log entries, i.e. you log so much that you run out of disk space. In emergency situations, where no space can be immediately freed for new log files, you can restart SQL Server with the -f flag, which will disregard auditing settings.

How to View the Audit Records

There are two ways of displaying the auditing records. Easy method would be clicking on the trace file. It will open this in SQL Profiler.


The second method would be using T-SQL.

SELECT *
 FROM ::fn_trace_gettable(
   'C:\Program Files\Microsoft SQL Server\MSSQL\Data\audittrace_20041007153315.trc', default
	)
GO

Limitations

The main limitation of the auditing is that it reduces the performance of the SQL Server. This happens due to saving the every action to the file. Second limitation is the hard disk space. These auditing files grow rapidly, which will reduce the disk space. According to the C2 , if it is not able to write to the trace file, SQL Server will be shutdown.

Conclusion

If you need your server to maintain a detailed audit trail, then the C2 audit option may be a good choice. However, you might have to consider few things before configuring it to be sure that you don't cause yourself some issues over time. Be sure that you read the documentation carefully before enabling this option.

References

http://www.microsoft.com/technet/treeview/default.asp?url=/technet/security/prodtech/dbsql/sqlc2.asp
http://www.microsoft.com/technet/prodtechnol/winntas/maintain/security/c2secgde.mspx

Total article views: 26822 | Views in the last 30 days: 101
 
Related Articles
FORUM

how to enable auditing in sql server 2005

how to enable auditing in sql server 2005

FORUM

Using "Sql server" option for Configuration type

Using "Sql server" option for Configuration type

FORUM

Process Memory Enable options

Process Memory Enable options

FORUM

How to Disable/Enable Kerberos Authentication option on SQL 2008 Failover Cluster on Windows Server 2008

How to Disable/Enable Kerberos Authentication option on SQL 2008 Failover Cluster on Windows Server ...

FORUM

Audit

Audit

Tags
monitoring    
sql server 7    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones