3

I will try my best to describe the situation, while we've been trying to figure this out:

We have this SQL Login [LA] which shows successful connection on principal server in the sql error logs [connection made from various apps]. This login shows successful connection on its mirror server as well.

Now, here is this another login [LB] which connects successfully on principal server but fails to connect on mirror server. [same application as above login LA connects]

Troubleshooting done:

  1. run sp_help_revlogin on both servers and matched the hashed password for login [LB]. Thus that confirms password is same for this login in terms of SQL login for both principal and mirror.

  2. SID's of login is same at both servers, so this should not be a problem as well.

  3. Application uses same creds to connect on principal which is a success but shows login failed on mirror server.

Since something is making the login unsuccessful attempts, login keeps getting locked out.

What else can i check as a DBA before passing this as an application issue to the app owners?

Please help me troubleshot to find the root cause of this issue, thank you.

Improve this question
4
  • What error message is shown in the SQL Server Error Log on the mirror?
    – Hannah Vernon ♦
    Jan 28, 2017 at 0:14
  • login failed for user [lb], reason password did not match for the login provided. [client xxxxx]
    – Newbie-DBA
    Jan 28, 2017 at 0:58
  • 1
    Newbie - since you confirmed password hash and sids BOTH match with SP_RevLogin, then check to ensure the default DB of this account is set to point to an actual valid database. You might also delete the account from the local DB security settings and then go to the global security settings and set the security back for this DB at this level. Check the default DB too on the new/mirrored server. Sometimes things get out of whack if not done in proper order fix and move on.
    – IT Thug Ninja
    Jan 28, 2017 at 1:40
  • @Walmart - the passwords don't match the login provided by the client, as noted in the comment directly above yours.
    – Hannah Vernon ♦
    Jan 28, 2017 at 1:51

1 Answer 1

Reset to default
1

If logins are getting locked, that indicates the password is incorrect. Either the server has the incorrect password recorded for the login, or the client is presenting the wrong password.

Since this is a mirroring environment, I'd bet the client is using the same password for both instances; meaning the password for the login on the mirror is not the same as the password for the login on the master.

Recreate the login on the mirror server with the known-good password, i.e. not the hashed version.

Ensure you get the sid correct (use copy-and-paste) from this query, ran against the principal server:

SELECT sp.name
    , sp.sid
FROM sys.server_principals sp
WHERE sp.name = 'login_name';

Use that sid in the CREATE LOGIN statement you run on the mirror:

CREATE LOGIN login_name
WITH PASSWORD = 'thepassword'
    , SID = 0x000000000000 --put the real sid here.
    , DEFAULT_DATABASE = tempdb;

The sid is only important because it links the login to the user inside the database.

Improve this answer
2
  • Thanks for the reply Max: As of now , we cannot recreate the login because we do not store the passwords. And again this would go to a different team in that case. Also i have checked that SID is same on both principal and mirror. Just need to know why we should re-create login since passwords works on one while it does not on other. Just trusting the protocol out here :)
    – Newbie-DBA
    Jan 28, 2017 at 1:04
  • Clearly, however you created the login on the mirror, it actually didn't create it with the correct password. Either that, or the client is using the incorrect password. You confirmed that when you added the error message from your SQL Server Error Log on the mirror machine - "login failed for user [lb], reason password did not match for the login provided. [client xxxxx]"
    – Hannah Vernon ♦
    Jan 28, 2017 at 1:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.