4

I have 2 SQL Servers in an Availability Group. The logins are manually synced – same SID, same Password Hash, etc. on both replicas – and the user can log in via the listener to the primary replica. (Note: password policy & expiration policy are on.)

Later, the AG fails over to the other replica, and the user now gets a login failure. The new Primary’s sys.sql_logins table shows a different Password Hash and a different Modify_Date: the date / time of the login failure! When the original Password Hash is re-migrated to the new Primary, the user can again log in successfully.

Can anyone tell me why the login’s password hash “spontaneously” changes after failover at login time, and how to avoid this happening so the users can log in after a failover?

My hunch is that it has something to do with the password/expiration policy, but that's a requirement of our auditors so can't be turned off.

  • When the password is changed, the hash is changed as well. – Kin Shah Mar 16 '16 at 17:27
  • I haven't tried this, but you could use a DDL trigger perhaps to change the password on both servers. And also look into whatever process is syncing them, sounds like something is not right there. – Aaron Bertrand Mar 16 '16 at 17:31
  • 2
    The hash is always going to be different on different servers, even with precisely the same plain-text password (unless the login was created using the hashed version of the password). Use the PWDCOMPARE function to compare passwords. – Max Vernon Mar 16 '16 at 18:11
  • 1
    When the instance fails over, and these logins no longer work, check the LOGINPROPERTY(<loginname>, 'IsExpired') to see if the login has expired. – Max Vernon Mar 16 '16 at 18:17
  • Which version of SQL Server is this? – Max Vernon Mar 16 '16 at 18:18
0

I don't know if this is still an issue for you, or if you've found a solution, but in case you haven't, a very powerful set of tools, and one specific to your issue, can be obtained from: https://dbatools.io. Specific to your issue, use "Copy-SqlLogin" and configure it as a scheduled task on each server (primary and all secondaries) in whatever configuration that will work for your needs. I used this solution at the last place I worked and scheduled it to run every hour, and never had an issue with logins after implementation.

If, for some reason, you cannot utilize dbatools, you will need to use sp_help_revlogin, devise some method to pick out the logins you want to "replicate" (store in a table??) and then execute the IF NOT EXISTS...CREATE LOGIN... on all relevant instances, which will have to be hard-coded. So, this second method is only slightly better than manual intervention. However, assuming DBAs are the only ones who can create logins, it would be easy enough to make a procedure for all DBAs to always add a new login to this process whenever a new one is created. One thing to keep in mind, wherever the login is obtained from, the User level permissions will be carried over to all replicas, so make sure the "source" login/user is configured correctly before implementing any sync solution.

IMHO, logins and SQL Agent jobs are the only thing lacking from AlwaysOn, and perhaps in a future release, Microsoft will add some automatic functionality--especially for logins, since it really breaks the whole purpose of automatic failover if the logins aren't sync'd.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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