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.