locked
How to validate SQL Login Credientials - Userid and Password using tsql RRS feed

  • Question

  • How can you validate SQL Login Credientials - Userid and Password using tsql?
    Tuesday, January 17, 2017 3:44 PM

Answers

All replies

  • What do you mean "How can you validate SQL Login Credientials"?

    You cannot decode the password, the only way to validate a SQL login works is to actually login using the login and password.

    Tuesday, January 17, 2017 3:59 PM
  • How would I login using tsql? I am not trying to decode the password. I have clients that say "I think that this is the password". Would just like to verify it.
    Tuesday, January 17, 2017 4:14 PM
  • There is no way to retrieve passwords, but executing sp_helplogins retrieves all information about Logins and what database users they are mapped to.

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, January 17, 2017 4:16 PM
    Tuesday, January 17, 2017 4:16 PM
  • Obviously, if users are attempting to Login with SQL authentication credentials and it's failing, then they are not using the correct password, or else the password has expired.

    You can check whether password has expired or not, by inspecting Login Properties:

    If the 'Enforce password expiration' box is checked, then your options are to either reset the password, or else uncheck that property and have the user retry Login attempt, to see if it succeeds.

    Other than that, the only way I can think of to test validity of a password is for you to attempt a connection to SQL instance yourself, using the clients username/password, and see if it succeeds or fails:

    If the password has not expired, but the user has just forgotten the password, about the best you can do as a DBA to assist the client, is to reset the password for them.

    [Please mark as answer if this post helped you]

    Hope that helps,


    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Tuesday, January 17, 2017 5:13 PM
    • Proposed as answer by Naomi N Tuesday, January 17, 2017 6:01 PM
    • Unproposed as answer by TheBrenda Tuesday, January 17, 2017 6:49 PM
    Tuesday, January 17, 2017 4:32 PM
  • Hi, possibly looking at pwdcompare https://msdn.microsoft.com/en-us/library/dd822792.aspx may be an option.
    Tuesday, January 17, 2017 6:28 PM
  • philfactor, I need to do it with tsql. I am the developer. The client calls support and they discuss. I get an email or IM saying that the client is not sure what is the password. Yes, I know, everyone could get educated on how to connect to the DB. But I would like to just send an EXEC stored procedure statement  and Print out "Password is Correct" or "Password is not correct". 

    So my question, is there a way to verify a password is correct using tsql?



    • Edited by TheBrenda Tuesday, January 17, 2017 6:55 PM edit
    Tuesday, January 17, 2017 6:46 PM
  • KevinNicholas, PWDCOMPARE is perfect. And if the password is not correct, you can try and retry without disabling the Userid. This will be very helpful. Thanks.

    SELECT name FROM sys.sql_logins   
    WHERE PWDCOMPARE('SQLLoginPassword123!', password_hash) = 1 and name = 'SQLLoginName';  

    Tuesday, January 17, 2017 6:54 PM
  • Really? Exactly who/what will provide "password_hash"? And did you read the documentation? No - this is not your solution. Nor is there a solution at all if you cannot access the appropriate server instance - and your use of "client calls support" suggests you do not have access. Which makes complete sense from a security standpoint since developers should NEVER have access to a production machine.

    No - you have a goal that you cannot achieve. Nor should you, IMO. If you have access to a production database and also know a valid userid and password, then this system is compromised. The only way to verify a password is correct is to use it to connect to the appropriate instance. The "solution" here - if there is one - is to have the administrator of said server instance reset the password to a known value and communicate that securely to the appropriate person (and then force that person to change it immediately).

    The only thing you can do is verify that a particular login (not user) exists in the server instance (and has access to a particular database). But only if you have access to the actual server instance. It is just a bit bizarre that your support organization is involving a developer in this situation - at least from what you have mentioned so far.

    • Proposed as answer by Naomi N Tuesday, January 17, 2017 8:49 PM
    • Unproposed as answer by TheBrenda Tuesday, January 17, 2017 9:29 PM
    Tuesday, January 17, 2017 7:51 PM
  • scott_morris-ga, I understand your concerns with the procedures, which are not exactly as I stated. There is a more complicated relationship between the SQL Servers and myself that I do not care to divulge.

    But so far as the code, yes I read the doc. And provided you have the necessary security, someone could run this to find out if the password is correct for the login. 

    create login abc with  password='!_Akdosi11@#@'
    SELECT name FROM sys.sql_logins   
    WHERE PWDCOMPARE('!_Akdosi11@#@', password_hash) = 1 and name = 'abc';


    • Edited by TheBrenda Tuesday, January 17, 2017 10:07 PM edit
    Tuesday, January 17, 2017 9:28 PM

  • But so far as the code, yes I read the doc. And provided you have the necessary security, someone could run this to find out if the password is correct for the login. 

    create login abc with  password='!_Akdosi11@#@'
    SELECT name FROM sys.sql_logins   
    WHERE PWDCOMPARE('!_Akdosi11@#@', password_hash) = 1 and name = 'abc';

    If someone already has access to the server then why can't they just attempt to log in?
    Tuesday, January 17, 2017 10:34 PM
  • disssss, because everyone uses their own Windows Authentication for signing in. The account that is usually a problem is a SQL local Login that is used for a service account. So they are never use to signing in with anything but their own credentials. They seems to forget that they could just try connecting with the SQL local login. But then sometimes they try too many times and the account gets locked out. So I will just email them the tsql SELECT WHERE PWDCOMPARE and tell them to substitute in the login and password to see if that is correct.
    Wednesday, January 18, 2017 8:22 PM