locked
Is it possible to look up a SQL Users password? RRS feed

  • Question

  •  

    Is it possible to query for a SQL Users password.  The programmers at my company have been using SQL Authentication and Hardcoding the username and password into the code.  So when mirroring was setup the users were not created on the mirror with the same SID.  Needless to say when we failed over this weekend it did not go well.  Since whoever created the users never documented it I am unable to create two of the SQL users and I cannot change the password on the principle mirror for fear of breaking some application.  I would be very grateful for some advice.

    Tuesday, February 7, 2012 9:12 PM

Answers

  • There is no way to get the users password, we do not store the password in its original form; only the hash value. You could try to brute force it but do you really need the original password value or just want to solve the current problem?

    Kevin's suggestion will solve your immediate problem and keep you going in the short term.

    After things are running smoothly, you should raise this issue to your senior management both in the admin group and with the security team following whatever protocol your company has for reporting important security risks.

    For starters, the programmers' practice of hardcoding user logins and passwords must stop immediately. Existing apps that you still have source code for should be modified to either use actual user logins or provide a proper and secure method for changing both login and password for the application. Finally, apps that you don't have source code for anymore will need some mitigation plan also. That will require a bit of thinking depending on the app, how it's used, data sensitivity, etc... If the internal team gets blocked, you might try contacting your local Microsoft rep for assistance.


    No great genius has ever existed without some touch of madness. - Aristotle

    • Marked as answer by KJian_ Tuesday, February 14, 2012 6:08 AM
    Tuesday, February 7, 2012 10:33 PM

All replies

  • Unfortunately, there is no "direct" way to retrive the password on a SQL Login.
    There might be some tool on internet to use burte-force or other logic to attack SQL User with various password combinations and get a password but I can't comment.


    Balmukund Lakhani | Please mark solved if I've answered your question, vote for it as helpful to help other user's find a solution quicker
    --------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights.
    --------------------------------------------------------------------------------
    My Blog | Team Blog | @Twitter

    Tuesday, February 7, 2012 9:17 PM
  • You can copy the password across to the new server without knowing it by creating the logins using the password_hash and SID from the sys.sql_login table on the instance with the working password e.g.

    CREATE LOGIN loginname WITH PASSWORD=hashfromcolumn HASHED, SID=sidfromcoulmn

    Tuesday, February 7, 2012 9:48 PM
  • There is no way to get the users password, we do not store the password in its original form; only the hash value. You could try to brute force it but do you really need the original password value or just want to solve the current problem?

    Kevin's suggestion will solve your immediate problem and keep you going in the short term.

    After things are running smoothly, you should raise this issue to your senior management both in the admin group and with the security team following whatever protocol your company has for reporting important security risks.

    For starters, the programmers' practice of hardcoding user logins and passwords must stop immediately. Existing apps that you still have source code for should be modified to either use actual user logins or provide a proper and secure method for changing both login and password for the application. Finally, apps that you don't have source code for anymore will need some mitigation plan also. That will require a bit of thinking depending on the app, how it's used, data sensitivity, etc... If the internal team gets blocked, you might try contacting your local Microsoft rep for assistance.


    No great genius has ever existed without some touch of madness. - Aristotle

    • Marked as answer by KJian_ Tuesday, February 14, 2012 6:08 AM
    Tuesday, February 7, 2012 10:33 PM
  • Hey, I had this very issue at a client site and the answer you need is here

    http://support.microsoft.com/kb/246133  This SP will duplicate the login, including the sid, which will save your life in the event of a failover with mirroring or logshipping as the sid remains the same.  As for the hardcoding, well if you don't have the source code then you can't change it in the appplication, so then you will never know what the password is.

    Hope that all goes well.



    Henry Rooney www.wardyit.com Please leave feedback

    Wednesday, February 8, 2012 6:46 AM
  • Thanks for all of your help  I will try coping it with the hash code to see if it works.  we won't be testing the failover again until this weekend.
    Wednesday, February 8, 2012 2:38 PM