locked
SQL Server Audit - parsing event id 33205 RRS feed

  • Question

  • Hello!

    I enabled auditing of the INSERT operation on PRINTERS table in one of my databases (to Security log) and can't understand the following: when I execute the first INSERT after connecting to the db the event 33205 contains all information it should have, but any subsequent insert operations produce events 33205 that contain blank database_principal_name, object_name and statement fields:


    Is this behaviour by design?

    Thank you in advance,

    Michael

    Thursday, February 4, 2016 2:50 PM

Answers

  • I would not say you must ignore these events, instead, you need to understand them as system events, not always tied to a statement. For example, you will see this type of event when SQL Server itself starts and starts the server audits in the system, or if one of these audits stop for any reason.

    When an event is not including information about DB-scoped entities (i.e. database_principal_id), it means that it is a system-wide event, not bound to any database in particular.

    In the case of the audit being started, it is a system event, and it is handled separately from the ALTER/CREATE SERVER AUDIT DDL call that enabled it (you will need to have an audit logging audit changes events already running in order to see them).

    I hope this information helps,

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by MF47 Tuesday, February 9, 2016 8:59 AM
    Monday, February 8, 2016 6:01 PM

All replies

  • Hi Michael,

    Could you please verify that if your SQL Server Service account is granted SeAuditPrivilege (also known as “Generate Security Audit”) privilege? Also make sure that  SQL Server Service is restarted and SQL Server computer is rebooted after granting that permission to service account, then check if the issue still persists.

    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support



    Friday, February 5, 2016 3:52 AM
  • Hi Lydia,

    "Could you please verify that if your SQL Server Service account is granted SeAuditPrivilege (also known as “Generate Security Audit”) privilege? Also make sure that  SQL Server Service is restarted and SQL Server computer is rebooted after granting that permission to service account, then check if the issue still persists." - yes, of course - I did it - otherwise there would be no 33205 events at all!

    Regards,

    Michael

    Friday, February 5, 2016 8:09 AM

  • Hi Michael,

    I test the scenario as yours, and everything works as expected.  Any subsequent insert operations produce events 33205 that contain correct database_principal_name, object_name and statement.

    Please make sure that your system meets the requirements (as described in the following article) for writing SQL Server server audits to the Windows Security log.
    https://msdn.microsoft.com/en-us/library/cc645889.aspx?f=255&MSPPError=-2147217396



    Thanks,
    Lydia Zhang


    Lydia Zhang
    TechNet Community Support


    Friday, February 5, 2016 9:30 AM
  • Hi Lydia,

    Thank you for your help!

    "Please make sure that your system meets the requirements" - it does meet the requirements. I think SQL Server may be logging some "fantom transactions" - for the lack of the better term, for example:

    I've changed yesterday (4 02 2016) audit destination from Security log to audit file - ! I HAVE NOT executed any sql commands yesterday  - I've only changed audit destination. Today - 5 02 2016 - I've added to rows to my PRINTERS table and seen what have been logged:

    The last two rows are correct, but the first row dates back to 4.02.2016 when I did not run INSERT statement.

    Regards,

    Michael


    • Edited by MF47 Friday, February 5, 2016 10:42 AM
    Friday, February 5, 2016 10:41 AM
  • Note that the action id of the row you don't expect is AUSC. I don't know what that means, but it probably does not mean INSERT.

    Friday, February 5, 2016 5:30 PM
  • AUSC event is "Audit Session Change".

    Very likely in this case most likely it is indicating that the audit session was started.

    This type of event will always be logged whenever the audit session is starting or is being stopped. It would help you find out if somebody stopped/restarted the audit.

    -Raul Garcia

    SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    Saturday, February 6, 2016 12:02 AM
  • Thank you all for the clarification on AUSC! But I still don't understand how to interpret several "blank" events in the Security log - they contain the same server_principal_name (Testcompany\Administrator) but the other database_principal_id (=0, whilst the "real" events have database_principal_id field = 1).

    May I just ignore the events where the Statement field is empty?

    Regards,

    Michael

    Monday, February 8, 2016 7:23 AM
  • I would not say you must ignore these events, instead, you need to understand them as system events, not always tied to a statement. For example, you will see this type of event when SQL Server itself starts and starts the server audits in the system, or if one of these audits stop for any reason.

    When an event is not including information about DB-scoped entities (i.e. database_principal_id), it means that it is a system-wide event, not bound to any database in particular.

    In the case of the audit being started, it is a system event, and it is handled separately from the ALTER/CREATE SERVER AUDIT DDL call that enabled it (you will need to have an audit logging audit changes events already running in order to see them).

    I hope this information helps,

    -Raul Garcia

     SQL Security


    This posting is provided "AS IS" with no warranties, and confers no rights.

    • Marked as answer by MF47 Tuesday, February 9, 2016 8:59 AM
    Monday, February 8, 2016 6:01 PM
  • Thank you all very much for your help!

    Regards,

    Michael

    Tuesday, February 9, 2016 8:59 AM