locked
Some actions not capture by the SQL Audit RRS feed

  • Question

  • I have a server audit specification that track some actions (for PCI) including FAILED_LOGIN_GROUP, LOGIN_CHANGE_PASSWEORD_GROUP etc. I see in the audit file many recent login fails audits (which means the audit is active) but when changing a SQL Login password or creating \ changing properties of existing login, I see no new records in the audit destination (which is the security log). Any idea why? How should I generate test code to trigger this audit action group to verify it is active?

    Sunday, August 4, 2013 4:35 PM

Answers

  • I just tested the LOGIN_CHANGE_PASSWORD_GROUP audit without any problems. I created a SQL Server Authentication login. Then I changed the password using ALTER LOGIN. The log contained a record with Action ID RESET PASSWORD and the statement ALTER LOGIN ...

    So... make sure your audit and server audit specification are enabled.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, August 5, 2013 4:52 PM
  • Hi Gal1,

    SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see the article ”Write SQL Server Audit Events to the Security Log” which will guide you to configure it.

    And then run your command by changing a SQL Login password or creating \ changing properties of existing login, then refresh the records in Event View(Where you read the security log), you will see the latest records.

    Write SQL Server Audit Events to the Security Log”.

    http://technet.microsoft.com/en-us/library/cc645889.aspx

    Thanks

    Candy Zhou


    • Edited by Candy_Zhou Wednesday, August 7, 2013 6:43 AM edit
    • Marked as answer by Allen Li - MSFT Tuesday, August 13, 2013 11:09 PM
    Wednesday, August 7, 2013 6:43 AM

All replies

  • I just tested the LOGIN_CHANGE_PASSWORD_GROUP audit without any problems. I created a SQL Server Authentication login. Then I changed the password using ALTER LOGIN. The log contained a record with Action ID RESET PASSWORD and the statement ALTER LOGIN ...

    So... make sure your audit and server audit specification are enabled.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty

    Monday, August 5, 2013 4:52 PM
  • Hi Gal1,

    SQL Server cannot write to the Windows Security log without configuring additional settings in Windows. For more information, see the article ”Write SQL Server Audit Events to the Security Log” which will guide you to configure it.

    And then run your command by changing a SQL Login password or creating \ changing properties of existing login, then refresh the records in Event View(Where you read the security log), you will see the latest records.

    Write SQL Server Audit Events to the Security Log”.

    http://technet.microsoft.com/en-us/library/cc645889.aspx

    Thanks

    Candy Zhou


    • Edited by Candy_Zhou Wednesday, August 7, 2013 6:43 AM edit
    • Marked as answer by Allen Li - MSFT Tuesday, August 13, 2013 11:09 PM
    Wednesday, August 7, 2013 6:43 AM