This editorial was originally published on Dec 8, 2008. It is being re-run as Steve is away at DevConnections.
I got an email from someone a few weeks ago and found it interesting. This person was wondering about auditing and how people deal with it in a world where we increasingly worry about security. The main questions in the email were:
In this time of PII data and sensitive data in nature,I would like to know is C2widely used and who uses it? Are there 3rd Party Tools that allow for the auditing of SQL Server activity? If such tools exist, what are they and what are there features to include reporting?
With SQL Server 2008, there are a tremendous number of new features that really make auditing easier, but I doubt very many of you are using them, or even using SQL Server 2008. However I am sure that many of you are doing some types of auditing with previous versions because of your own requirements. My curiosity was piqued as well, so I wanted to drop a note out there and see what types of auditing the rest of you may be doing.
In my career I've been required to audit for a variety of reasons, though the regulations haven't been the onerous on me. Typically I've been able to use triggers and selectively store off information using custom, home-grown solutions at my jobs. I looked at a few solutions that would generate the triggers, but never thought they were worth the money since I could write code to generate my own triggers and I needed selective auditing, not every field that changed.
Years ago I looked at Lumigent's solutions for auditing when they first came out. This was years ago as the company was evolving from a log reading tool company to a more general auditing and compliance company. They had a good solution, but it was too expensive for the company for which I was employed. I'm not sure how successful they've been, but they are still in business and seem to be doing well.
C2 auditing was one of those things that sounded like a great feature when it was introduced. Get every event that occurs on your server and you'll be sure to cover all your auditing needs. After all, this is what the Department of Defense has specified as a standard, so it must be a good idea, right?
I'm not so sure, though I know that it's a good idea in places. Those people that get access to some sensitive areas, like the places where we have missles stored probably ought to be audited.
For most of us, however, I think that we need less auditing, but more than we tend to implement. SQL Server 2005 has a default trace and some good basic auditing capabilities and SQL Server 2008 adds more. I think there's still room for improvement, and this is definitely something I'd like to see better implemented in frameworks so developers and DBAs build it into every application they produce.