CODEWITHSUNDEEP
asp.netsql-server
balaweblog
balaweblog

Reputation: 15470

Enabling Auditing feature in SQLServer 2005

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.

Upvotes: 9

Views: 6828

Answers (5)

David Atkinson
David Atkinson

Reputation: 5899

Redgate's new free tool, DLM Dashboard, uses DDL triggers to stored changes to a separate database, which is in turn regularly polled by the dashboard service thereby minimizing any performance impact.

This records schema changes and not (yet) data changes.

Upvotes: 1

sstewart
sstewart

Reputation: 51

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?

Upvotes: 2

Brent Ozar
Brent Ozar

Reputation: 13274

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.

Upvotes: 4

SqlRyan
SqlRyan

Reputation: 33914

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.

Upvotes: 1

mmiika
mmiika

Reputation: 10350

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

Upvotes: 0

Related Questions