September 27, 2007 at 3:25 pm
I have been given a requirement to audit access to particular databases.
We need to log anyone making a connections to these database, and maybe deeper levels of audits, like code that was run, objects accessed, objects changed, etc.
Does anyone know how to do this in SQL Server 2000 or 2005, with or without a third-part tool?
Does anyone have any experience with implementing C2 auditing in a production environment?
Does anyone have any experience with implementing Lumigent Audit DB in a production environment?
October 7, 2007 at 9:05 pm
In 2005 you have all sorts of event triggers (look up Event Notifications) for logins and changes.
For data changes, you really should use Profiler with a server side trace. It will capture what you need and then you can just save those files off to tape, maybe set up an archive job to delete old ones.
C2 would work, but it generate LOTS of data. In testing it was overwhelming, so I'd recommend you just audit what you need. The C2 audit catches everything, even stuff you might not want.
October 8, 2007 at 11:31 am
I agree with Steve here that a well-designed server trace is the way to go. C2 compliance mode is basically a server trace with almost everything toggled. My experience matches his: it's brutal on the SQL Server. I think I saw an estimate that it caused the SQL Server to take a 20-30% hit on performance.
In 2005 DDL triggers are nice and can be implemented as well, however, they can be easily disabled by someone with the appropriate rights and then re-enabled. DDL triggers are good to prevent the accidental modification to production, but I would look at them as a first tier approach and back them up with traces.
K. Brian Kelley
@kbriankelley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply