1

Is there a standardised way to store auditing information constantly to a table for further analyse?

  • Do you mean specifically from SQL Server Audit specifications, or arbitrary auditing data you are collecting on your own? – Aaron Bertrand Oct 6 '16 at 13:09
  • I mean something like AUD$ at Oracle DB. SQL Server audit specifications to meet the companies auditing policies. – r0tt Oct 6 '16 at 13:58
2

This really depends on what type of information you want to audit. There are a number of ways to collect audit data.

I did an article that overviews audit options here.

But in summary:

  • Successful/Failed logins: This is an instance level setting and is saved to the logs.
  • C2 Auditing and Common Criteria Compliance: This goes a bit overboard in my opinion. It audits a LOT of information. You'll know if you actually need it.
  • Audit columns: These are columns in your table that give you information. CreateDate & ModifiedDate for example
  • Triggers: These are good if you want to log specific information when doing an insert, update or delete on a table. There can be a lot of overhead here and they can be tricky to get right.
  • Tracing: Otherwise known as profiler. This is an older technology that has been deprecated but is still hanging on and will for who knows how long. You can collect all kinds of information here about what was run, by whome, how long did it take, who logged in, who logged out, just about anything happening on your instance. Remember though that the more you collect the more overhead.
  • Extended Events: This is the replacement for traces. Basically the same but with less overhead and more options for data you can collect.
  • Audits: These can be created at a database or server level. They are based on Extended Events but have a different set of information. For example shutting down the instance can be logged. These are meant for when you need to collect audit information in the traditional sense. The data that is collected for an auditor.

You can get a bit more detail from the link above and it has further links in at least a few places for even more detail.

  • Great overview thank you - the tool used to pick up the audit in my case is qradar - offical webside gives some information on how to attach SQL Server. – r0tt Oct 6 '16 at 13:56
  • It also uses the output from Audits so you might look into those a bit. I've got a walkthrough on the same site. sqlshack.com/reviewing-sql-server-audit – Kenneth Fisher Oct 6 '16 at 14:02
  • I'd also add Change Data Capture as an alternative to triggers for auditing of changes – Andrew Bickerton Oct 6 '16 at 14:59
0

I don't know if there is a 'standardised' way of storing auditing information into a table, but here is an answer I posted on a particular way to do it.

  • right, same topic... – r0tt Oct 6 '16 at 13:29

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.