none
SQL Audit - Failed Logins - not collecting failure from sqlcmd RRS feed

  • Question

  • Fellow SQLers

    I have been setting up a SQL Audit at the server level. I have included a Failed_Login_Group

    To test something outside of SSMS, I choose Sqlcmd.

    If I connect  by using the standard sqlcmd using servername, userid, and enter a INVALID password, it of course failes. The SQL Audit captures the failure.

    Now, if I , still using SQLCMD, connect to an instance with a good password, and user has a default db that is good, all is fine - no errors.  Then still using the same connection window, I enter USE, then a db name that the id DOES NOT have access to, then enter GO and enter, it fails as expected. But, SQL Audit does not pickup the error!! 

    Using SQLCMD, the failure is caught at the instance level when it cannot get to the instance. But it is not caught when going to another db that does not have permissions.

    When I do this exact test using SSMS, it captures at both failures.


    Any suggestions would be appreciated.

    MG


    Friday, July 5, 2019 2:26 PM

Answers

  • Using SQLCMD, the failure is caught at the instance level when it cannot get to the instance. But it is not caught when going to another db that does not have permissions.

    Because that is not a failed login. I don't think there is an even to capture this, but I don't know SQL Audit by heart.

    When I do this exact test using SSMS, it captures at both failures.

    I would guess, because Intellisense tries to log in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Monday, July 8, 2019 3:14 PM
    Friday, July 5, 2019 8:58 PM

All replies

  • Using SQLCMD, the failure is caught at the instance level when it cannot get to the instance. But it is not caught when going to another db that does not have permissions.

    Because that is not a failed login. I don't think there is an even to capture this, but I don't know SQL Audit by heart.

    When I do this exact test using SSMS, it captures at both failures.

    I would guess, because Intellisense tries to log in.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by mg101 Monday, July 8, 2019 3:14 PM
    Friday, July 5, 2019 8:58 PM
  • Hi Erland,

    You are correct.

    This goes back to intellisense issue I replied to on the forum for item "SQL Security - Logon
    Failures repeating ".  I thought SQLCMD should be doing what intellisense was doing but it does not.

    MG

    Monday, July 8, 2019 3:14 PM