April 14, 2008 at 4:45 pm
I've been tasked with performing auditing of "sysadmin" activity. I'm wondering how many of you might be doing this kind of auditing. If so what kind of problems/hurdles might you have run into in deploying an auditing system, and what tools (home grown and/or 3rd party) might you be using to do your auditing?
Here are my current audit requirements:
Scope:
The scope of these Audit Trail requirements applies to data that has been identified as confidential data that resides on production SQL Server machines that is access by any account that is a member of the sysadmin fixed server role.
Physical Audit Trail:
A physical electronic audit trail file or method to produce a physical file that can be queried and/or browsed when needed must be available for each SQL Server instance that contains confidential data. The electronic components (log files, programs, etc) necessary to produce an audit trail must be maintained and readily available at least 30 days and must available via and archive for at least 1 year.
Audit Requirements:
Each audit trail record will the contain WindowS LoginID (Windows account or SQL Server Login), the date and time of event, plus the type of action performed (SELECT, INSERT, UPDATE, DELETE, GRANT, REVOKE, EXEC, etc) and whether the action was successful, or unsuccessful.
Auditing will happen continuously, while a SQL Server instance is up and running, because a complete set of audit trail records can not be collected if auditing is only done periodically.
Audit only Login and Logoff attempts for logins that are a member of the sysadmin server role will be audited.
Audit all SELECT, UPDATE, INSERT and DELETE commands against all tables that are identified to containing confidential data must be logged when the command is issued by a login that is a member of the sysadmin server role.
Audit all SQL Server commands performed by any LoginID (Windows Account or SQL Server Login) that has sysadmin rights.
April 14, 2008 at 9:14 pm
A possible home grown process would be like this:
Set up a trace and configure it as per auditing requirement. Set up a daily job to have these kinds of logics:
1. stop the previous day’s trace
2. save the previous day’s trace file as AuditTrail_timestamp.trc
3. start the a new trace
4. the job starts at 12:00 AM and stopped at 11:59 PM.
5. deleted 30+ days old files
You have backups for those files upto 1 year.
April 14, 2008 at 10:07 pm
There are some really good products on the market that will do exactly what you are trying to achieve. Idera's compliance manager is one such product (there are others out there as well). This one is very easy to setup and configure and comes with a stack of Reporting Services reports as well.
April 15, 2008 at 12:09 am
This is the way we implemented it ...
http://www.sqlservercentral.com/articles/Security/3203/
maybe that can be a good starting point for you..
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
April 15, 2008 at 7:47 am
All excellent suggestions. Hopefully a few more of you will post your auditing requirements and methods. Keep the suggestions coming, I'm compiling all the options suggested so I can take a little from every option to create the best possible solution for my situation.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply