Sql-server – SQL Server logins “un-sync” when Availability Group fails over

availability-groupssql server

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.

Best Answer

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.

Related Solutions

Sql-server – Linked server to Availability Group Listener – is anyone successfully using one

After I reset all the settings and re-created the environment, the problem went away. So the problem was perhaps very specific to local situation and not general to a linked server to AAG. I appreciate all those who commented.

Sql-server – SQL query to check if secondary database is removed from availability group

You could use exists with the sys.dm_hadr_database_replica_states DMV to see if the DB is in a AG.

Returns a row for each database that is participating in an Always On availability group for which the local instance of SQL Server is hosting an availability replica. This dynamic management view exposes state information on both the primary and secondary replicas. On a secondary replica, this view returns a row for every secondary database on the server instance. On the primary replica, this view returns a row for each primary database and an additional row for the corresponding secondary database

Here is the query:

if exists(
    select 
        DBName = db_name(s.database_id)
        ,s.is_local
        ,s.synchronization_state_desc
        ,s.synchronization_health_desc
    from sys.dm_hadr_database_replica_states s
    where db_name(s.database_id) = 'your_database'
    )
begin
--do something useful
end
else
begin
--exit, probably without error
end

This would need to query against the secondary if you aren't on 2014+. If you are, then you can add s.is_primary_replica = 0 to the query. Or you can use this method or a similar approach.

Best Answer

Related Solutions

Sql-server – Linked server to Availability Group Listener – is anyone successfully using one

Sql-server – SQL query to check if secondary database is removed from availability group

Related Question