locked
sa account password is changed. RRS feed

  • Question

  • Hi All,

    Couldn't able to login with sa account, seems someone having admin account have changed this sa password.

    Is there any way to determine who and when have changed this password.

    It is known that SQL can't log this information in its log, is there any other ways. Please suggest.


    Grateful to your time and support. Regards, Shiva

    Thursday, September 17, 2015 8:28 AM

Answers

  • You can configure SQL Server Audit and track the LOGIN_CHANGE_PASSWORD_GROUP event. "This event is raised whenever a login password is changed by way of ALTER LOGIN statement or sp_password stored procedure. Equivalent to the Audit Login Change Password Event Class." See SQL Server Audit:

    https://msdn.microsoft.com/en-us/library/cc280386.aspx


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

    Thursday, September 17, 2015 3:43 PM
    Answerer

All replies

  • By default, SQL Server does not keep track of login password changes. When the question initially came up with a user, I thought that perhaps it might be in the default trace or in the system_health extended event session. No such luck. So I was in search of an alternate way to keep track of these events, if not retroactively, at least going forward.

    https://www.mssqltips.com/sqlservertip/2708/tracking-login-password-changes-in-sql-server/


    Grateful to your time and support. Regards, Shiva

    Thursday, September 17, 2015 8:45 AM
  • You can configure SQL Server Audit and track the LOGIN_CHANGE_PASSWORD_GROUP event. "This event is raised whenever a login password is changed by way of ALTER LOGIN statement or sp_password stored procedure. Equivalent to the Audit Login Change Password Event Class." See SQL Server Audit:

    https://msdn.microsoft.com/en-us/library/cc280386.aspx


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

    Thursday, September 17, 2015 3:43 PM
    Answerer
  • There is no way after the fact.   You would have needed auditing turned on when it happened.

    Thursday, September 17, 2015 4:59 PM
    Answerer
  • Couldn't able to login with sa account, seems someone having admin account have changed this sa password.


    Grateful to your time and support. Regards, Shiva

    Hi Shiva,

    As others have pointed out the only way is with an Audit that must be enabled before the change.

    My question is: Are all of the sql accounts unable to log in or is it just SA? It's possible that someone mistakenly changed the server to Windows Authentication only and by doing so the server won't accept mixed mode (sql logins).

    Open the Instance properties from Object Explorer and then view the Server Authentication from the security tab. If it's set to Windows Authentication mode then SA will not work.


    I hope you found this helpful! If you did, please vote it as helpful on the left. If it answered your question, please mark it as the answer below. :)


    • Edited by Daniel Janik Thursday, September 17, 2015 5:52 PM
    Thursday, September 17, 2015 5:50 PM
  • Best course of action is to put the DBA in charge of logins and passwords.  If there is no DBA then a developer has to assume this role.

    Reference:

    How to reset SQL Server sa password using Microsoft SQL Server Management Studio Express





    Kalman Toth Database & OLAP Architect Artificial Intelligence
    New Book / Kindle: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2014



    Sunday, September 20, 2015 12:02 AM
  • No , you can't trace back without enabling audit or at least enable the common compliant option

    • Proposed as answer by SQL Kitchen Sunday, September 20, 2015 12:18 PM
    • Unproposed as answer by pituachMVP Sunday, September 20, 2015 3:06 PM
    Sunday, September 20, 2015 12:18 PM
  • SQL Kitchen, Please do not propose your own answer!

    By posting an answer we already know that you think that this is THE ANSWER, else why did you post it?!? as well any other person who post an answer! he is sure that he post THE ANSWER. The Idea of proposing an answer is to let someone else say that he also think that this is THE ANSWER. If each person that think his response is the answer will propose his own answer then all responses will be marked.

    Thanks :-)


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]

    Sunday, September 20, 2015 3:20 PM