2

I have some audits and audit specifications on a SQL Server 2012 instance and I want to reapply the audit and audit spec definition every evening in case the DBAs change it. I have measured the time taken to do this on one of my Dev boxes, and it's quite short, but there's still a window there where some audit data could get lost. From a quick test I did to measure the time taken:

AuditRecreate_ms
----------------
23

(1 row(s) affected)

AuditSpecRecreate_ms
--------------------
16

(1 row(s) affected)

It's possible with SQLTrace because you can create a new trace and then delete the old one, and no audit data gets lost. I'm trying to move away from SQL Trace but retain an uninterrupted audit but at the same time make sure the audit definitions do not change. Any ideas here?

  • 1
    > It's possible with SQLTrace because you can create a new trace and then delete the old one - and why can't you do that with an audit, exactly? – Aaron Bertrand Apr 25 '14 at 13:36
  • @AaronBertrand of course you can, thanks. My mistake. – Mark Allison Apr 25 '14 at 14:38
4

I want to reapply the audit and audit spec definition every evening in case the DBAs change it

Engaging in an arms race against the rightful admins is only going to cause delay and confusion.

  • Explain to DBAs the role and purpose of the audit, ask them not to change it
  • Use the audit itself to detect tampering
  • Follow up upon detecting tampering with administrativia to get the responsible party questioned/rewarded/punished depending on situation

If you cannot enforce above, it means by definition that you are not be able to enforce an audit, so why bother?

As for specifically the question, this advice applies always, to any situation: do not blindly re-apply a template 'in case it changed', only apply it if it had changed.

Ask your DBAs to help you, do not ask us to help you against your DBAs.

  • All valid points but I'm in a sensitive environment where there is a trust no-one policy. I don't make the decisions, I'm trying to implement a solution. I think the only solution here is to use the audit itself to detect tampering - I will have to figure out a way to do that. I could use the create_date and modify_date from sys.server_audits and sys.server_audit_specifications amd compare them to when the audit was first set up. – Mark Allison Apr 25 '14 at 10:16
  • Tampering is detected by looking into the audit itself for records that indicate modification of the audit. If the audit is properly defined, any audit change should be recorded and can be used as proof of tampering. An audit incapable of such would not pass C2. – Remus Rusanu Apr 25 '14 at 10:22
  • 3
    As for how to modify an audit w/o loosing records: you define and start a new audit and then you drop the old one. This way there is an overlap that guarantees no gaps. – Remus Rusanu Apr 25 '14 at 10:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.