locked
Questions on SQL Audit RRS feed

  • Question

  • Hello!

    This document says:

    "The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events. "

    ...and it does contain the list of atomic events but only for the database-level audit (Database-Level Audit Actions) - for server-level audit there's only the list of action GROUPS (Server-Level Audit Action Groups) and the audit server operation event classes are given for each audit group instead, for example:

    SERVER_OPERATION_GROUP This event is raised when Security Audit operations such as altering settings, resources, external access, or authorization are used. Equivalent to the Audit Server Operation Event Class.

    Anyway I don't seem to understand how the class description corresponds to the contents of the audit events being registered (event 33205) so

    Q1: Is there a way to audit server-level actions instead of action groups?

    Q2: Does any documentation on how to parse the audit event 33205 exist ?

    Thank you in advance,
    Michael

    • Edited by MF47 Friday, August 30, 2019 1:03 PM
    • Moved by Tom Phillips Wednesday, September 4, 2019 3:32 PM SQL Audit question
    Friday, August 30, 2019 12:14 PM

Answers

  • Q1:  No.  You audit classes of changes and have to filter your query to actions inside a class.  A "class" is really a single message.

    • Marked as answer by MF47 Thursday, September 5, 2019 8:42 AM
    Wednesday, September 4, 2019 3:07 PM

All replies

  • I don't understand your questions.  Can you give examples of the data you are seeing?

    Wednesday, September 4, 2019 12:06 PM
  • Q1 is the theoretical question, Q2 is regarding the explanation of each field found in the 33205 event:

    I can guess, of course, what each of them means but I'd prefer to read official MS documentation on this event, as, for example, for the 4624 event:
    https://docs.microsoft.com/en-us/windows/security/threat-protection/auditing/event-4624


    • Edited by MF47 Wednesday, September 4, 2019 1:16 PM
    Wednesday, September 4, 2019 1:15 PM
  • The 33205 is a generic message from SQL Audit and includes every different type of payload.  So there is not a single description of the fields included in the XML.  It is all message specific.

    You can use the action_id value to see what class of message it is related.  In your case action_id = 'AUSC'

    select *
    from sys.dm_audit_actions
    where action_id = 'AUSC'

    Wednesday, September 4, 2019 1:42 PM
  • yes, I also use that method, but thought there could be a complete field explanation somewhere on technet (actions can be different but the fields themselves are the same as this is the single 33205 event).
    • Edited by MF47 Wednesday, September 4, 2019 1:52 PM typo
    Wednesday, September 4, 2019 1:52 PM
  • I cannot find any detailed information on the fields in the event.   MS should fix that. However, they are pretty explanatory SQL Server terms.  Do you have a specific question about something in the event?

    PS.  I don't use these events from the Windows event log.  I use the Audit file. It is easier to parse and use.

    • Edited by Tom Phillips Wednesday, September 4, 2019 2:51 PM
    Wednesday, September 4, 2019 2:50 PM
  • "Do you have a specific question about something in the event?" - no, thanks.

    "... the Audit file. It is easier to parse and use." - agree!

    What about Q1?

    Wednesday, September 4, 2019 2:56 PM
  • Q1:  No.  You audit classes of changes and have to filter your query to actions inside a class.  A "class" is really a single message.

    • Marked as answer by MF47 Thursday, September 5, 2019 8:42 AM
    Wednesday, September 4, 2019 3:07 PM
  • Thank you all for your help!

    "Q1:  No." - thank you, Tom!

    Regards,
    Michael

    Thursday, September 5, 2019 8:42 AM