January 7, 2016 at 10:54 am
Currently, we are required to run a server-side trace covering a large number of trace items in the Security Audit category (based on matching up the sys.trace_events category_id to the category_id in sys.trace_categories) on all of our servers.
As server-side traces are on the way out, I'm looking to replace the traces with server audits and where, and if needed, database audits. From looking at the information on MSDN on server audits, I think I'm going to be able to get most, if not all the required items in the audits, but being lazy, I'm hoping someone has already done some of the grunt work for me...
Namely, has anyone made up a list showing which trace events match up to which audit events, or which trace events don't have an equivalent audit event?
I've already looked at using Extended Events, and a goodly selection of the events in the trace don't match up to any XE items (checked using the various scripts published to help convert a trace to an XE session)
Thanks
Jason A.
January 7, 2016 at 8:01 pm
Hi,
You might found this very interesting, where if you already have a trace going, you can simply use Jonathan script to convert that to extended events. You should get a better throughput (less overhead) compare to server-side trace.
https://www.sqlskills.com/blogs/jonathan/converting-sql-trace-to-extended-events-in-sql-server-2012/
January 8, 2016 at 6:17 am
I've actually looked at and tested that script to see if it would cover what I need to audit with XEs. The problem is, right up in the comments on the script where it lists the various Audit type events that aren't in XEs, is a list of most of the events I'm required to audit...
Kind of annoying that MS didn't provide some way, even an undocumented way, to equate various Trace Audit events to Server Audit types, similar to what there is for Trace events to XEs...
Or if they did, I haven't found it yet.
January 8, 2016 at 11:57 am
It was easier than I thought, going through the MSDN list of server and database audit action groups and cross-referencing them to the trace audit items I'm required to have...
Now to test it out, and get the OK from our IA guys to use it when we go to SQL 2014 this year...
January 8, 2016 at 12:30 pm
I started, but never finished.
I'd love an article on this if you do the work. I've been thinking of talking on audits, so maybe I'll work on it as well.
January 10, 2016 at 6:33 pm
Great that you got it working, I'm working on the same on my side, but haven't got the time to get round it yet 😀
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply