locked
How do I prevent SQL admins from stopping a trace job RRS feed

  • Question

  • I have recently created a trace job that that captures certain security related events on my SQL databases. The trace file rolls over every hours and is written to a network share drive where the data is then parsed and processed so that the events can be monitored.

    The problem I have is that the database administrators / sysadmins have the ability to stop the trace job and even modify the trace file data. This defeats the purpose of this exercise as the DBAs are usually the main guys that need to be monitored.

    Is there anybody who can assist me in recommending how I can lock down this trace job and perhaps assign only one user with the ability to stop a trace job or make any changes to it?

    Wednesday, July 11, 2012 5:30 PM

Answers

  • The solution for a high security environment is to use SQL Server Audit (probably Enterprise Edition) instead of trace. Then configure audit to Write SQL Server Audit Events to the Security Log http://msdn.microsoft.com/en-us/library/cc645889.aspx

    Keep sysadmins out of the local administrators group and don't give them access to the security log. They can still stop the audit, but that action will be audited.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by amber zhang Wednesday, July 18, 2012 1:47 AM
    Wednesday, July 11, 2012 10:07 PM

All replies

  • It is very difficult to stop persons who are sysadmin in their realm from doing what they feel like. At best you can have some fraud detection, for instance a program that monitors that the current roll-over file stays open and is not tampered with.

    But the best is to make sure that you are the only admin on the server. Then again, who says that you are to be trusted? :-)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, July 11, 2012 5:44 PM
  • Renesh,

    It is difficult but if you have audit track on sysadmin people that might help you for their activity.


    Rama Udaya.K ramaudaya.blogspot.com ---------------------------------------- Please remember to mark the replies as answers if they help and un-mark them if they provide no help.

    Wednesday, July 11, 2012 8:55 PM
  • The solution for a high security environment is to use SQL Server Audit (probably Enterprise Edition) instead of trace. Then configure audit to Write SQL Server Audit Events to the Security Log http://msdn.microsoft.com/en-us/library/cc645889.aspx

    Keep sysadmins out of the local administrators group and don't give them access to the security log. They can still stop the audit, but that action will be audited.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    • Marked as answer by amber zhang Wednesday, July 18, 2012 1:47 AM
    Wednesday, July 11, 2012 10:07 PM