SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auditing "sysadmin" access to SQL Server 2005


Auditing "sysadmin" access to SQL Server 2005

Author
Message
G. Larsen
G. Larsen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 95
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.



Vivien Xing
Vivien Xing
SSCommitted
SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)SSCommitted (1.9K reputation)

Group: General Forum Members
Points: 1874 Visits: 2204
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.
happycat59
happycat59
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4779 Visits: 3225
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.



ALZDBA
ALZDBA
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12310 Visits: 8926
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


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


- How to prevent a sore throat after hours of presenting ppt ?


press F1 for solution, press shift+F1 for urgent solution :-D


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me Alien but most of the time this is me Hehe
G. Larsen
G. Larsen
SSC-Enthusiastic
SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)SSC-Enthusiastic (106 reputation)

Group: General Forum Members
Points: 106 Visits: 95
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.



Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search