Syncing Logins/Users Across SQL Server Instances (For Availability Groups and Mirroring Failovers)

Hey Everyone! Guess who's still around!

Welcome back to SQL Something (with a post dedicated to my friend Greg who guilt-ed me into finally starting back posts). In all seriousness, it's really been far too long.

Today's post briefly touches on SQL Server Always On Availability Groups in SQL Server 2016. You can find a pretty good tutorial on how to set up Always On Availability Groups in an Azure VM via the links here and here. They are pretty thorough (great job team Microsoft!), but they do have a couple very, very small errors/typos that I might point out in a subsequent post.

Today, however, we are merely focusing on a simple login/user issue in order to help streamline failing over to your secondary. The fix is about syncing logins between availability groups. This fix can also be applied to Mirroring (a deprecated feature after 2016)

Definitions

So before we proceed, a couple quick definitions:

• Login - Grants a Principal (an application/user/etc.) access to a Server. One Login can be mapped to multiple Users. See also.
• User - Grants a Login access to a Database. See also.

The Issue

When you create a Login, a unique identifier is assigned to it. So, creating a login on Server1 and then creating a login with the same properties on Server2 will still result in two logins with differing unique identifiers or SIDs (as seen in Fig. 1).

Fig. 1: Primary and Seconday servers with Logins with different SIDs.

So, if you were to failover to Server2, the login is actually viewed as a separate and unique login and as such would not associated with the user on the database.

Failing over and then attempting to log onto the database on the new primary with your existing creds will produce either a "Login failed" error or you won't automatically have access to the database via the user associated with the database.

Solution

To fix this, we need to sync the Logins across the two servers. We need to make sure that the logins have the same unique identifier, so that they sync with the users assigned to the databases.

In order to get the info for the SID, we can query sys.server_principals. You can also get the hashed password by querying sys.sql_logins (which would then have to be converted to nvarchar for use).

You can then put all this info together into one very solid query provided by Daniel Hutchmacher's post on SQLSunday. An excellent post all around.

I will provide the query snippet below (slightly modified to include an '=' that was left out.):

SELECT  N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD = 0x' +

       CONVERT(nvarchar(max), l.password_hash, 2) + 

       N' HASHED, CHECK_POLICY=OFF, ' +

       N'SID=0x' + CONVERT(nvarchar(max), sp.[sid], 2) + N';'

FROM   master.sys.server_principals AS sp

       INNER JOIN

        master.sys.sql_logins AS l ON sp.[sid] = l.[sid]

WHERE  sp.[type] = 'S' AND sp.is_disabled = 0;

For each SQL Server Login created, this query produces a CREATE LOGIN command that is to be run on your secondary server. These statements recreates the logins on the secondary with the same SID and passwords that are found on the primary, making failover much smoother. :-)

Fig. 2: Primary and Secondary servers with Logins with the same SIDs.

DISCLAIMER: As stated, I’m not an expert so please, PLEASE feel free to politely correct or comment as you see fit. Your feedback is always welcomed. :-)

Sources:
https://sqlsunday.com/2016/10/11/how-to-sync-logins-between-availability-group-replicas/
https://www.brentozar.com/archive/2015/06/how-to-set-up-standard-edition-alwayson-availability-groups-in-sql-server-2016/#comment-2398138