Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auditing "sysadmin" access to SQL Server 2005 Expand / Collapse
Author
Message
Posted Monday, April 14, 2008 4:45 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 5:48 PM
Points: 78, 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.



Post #484698
Posted Monday, April 14, 2008 9:14 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, January 08, 2014 2:49 PM
Points: 1,279, Visits: 2,203
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.
Post #484748
Posted Monday, April 14, 2008 10:07 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 9:21 PM
Points: 2,842, Visits: 2,423
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.




Post #484752
Posted Tuesday, April 15, 2008 12:09 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:27 AM
Points: 6,997, Visits: 8,411
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


Don't drive faster than your guardian angel can fly ...
but keeping both feet on the ground won't get you anywhere

- 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"


Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me
Post #484773
Posted Tuesday, April 15, 2008 7:47 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, May 15, 2012 5:48 PM
Points: 78, 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.


Post #485028
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse