SQL Server logins "un-sync" when Availability Group fails over

Question

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.

Answer 1

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.