Enabling Auditing feature in SQLServer 2005

Question

Did you ever use SQL Server auditing features on a production db?

How did that impact on performances, and are there differences you noticed between different versions of SQL Server.

Also how we need to enable the audit features.

Answer 1

I'm afraid there's no such a thing as "audit feature". Instead you need to build it yourself depending on what kind of requirements you have. There are many ways to do this, for example

• A trigger fires every time data in audited tables change. An example here: http://sqlserver2000.databases.aspfaq.com/how-do-i-audit-changes-to-sql-server-data.html
• using your ORM, with NHIbernate ideas here: http://stackoverflow.com/questions/15917/data-auditing-in-nhibernate-and-sqlserver

Answer 2

C2 Audit Mode may give you some of the functionality you're looking for. While it doesn't specifically record SQL Statements used to access data, it will give you a comprehensive record of successful/failed security requests on your SQL Server.

http://msdn.microsoft.com/en-us/library/ms187634.aspx

We use Idera SQL Compliance Manager to do our SOX-compliant SQL auditing. I don't particularly like it, but it seems to be one of the industry leaders, so I suppose it's as good as any, and it gets the job done. If you're looking to meet some external requirement (as in Sarbanes-Oxley), rolling your own might not fit the bill, and you could use a product like this one.

Answer 3

The C2 audit mode suffers from a variety of problems, and your question touched on only one of them - performance. C2 auditing has a tremendous performance impact, very high.

Setting the performance issue aside, it's very difficult to manage. It's not a set-it-and-forget-it configuration switch. You have to spend quite a bit of time doing configuration, setting up the logging to go to files, and then when you're done, someone else can come behind you and get rid of the audit files pretty easily. There's no way to quickly poll all of your servers and make sure C2 auditing is working correctly, or that someone isn't dropping files.

SQL Server 2008 makes compliance much easier. I'd recommend taking a look at the SQL Server 2008 Compliance portal, which has an excellent whitepaper on how to configure 2008's new compliance features. 2008's new auditor uses the xEvent handling which has dramatically lower performance requirements and it's much easier to manage. You can use 2008's Policy-Based Management to check your servers, ensure you're auditing, and help get auditing configured again when things break.

Unfortunately, one weakness is still control of the audit output files - the bad guys can simply delete the files. Another weakness is a lack of reporting - just because you've got gigs of audit data doesn't mean you can do anything with it. You still have to write your own reports in order to analyze the audit data and find out who's doing what. It's not easy - but it's a lot easier and lower-impact than SQL 2005's C2 auditing.

Answer 4

C2 Audit mode is what you are looking for, triggers seem like not the way to go for auditing. What did you end up using?

Answer 5

I have been looking for the answer if MS SQL 2005 has an audit function. My company auditor is requesting that we should be enable it. I can't imagine how it occur to them that I should enable this feature.