Resolving Login SID Mismatches

Just as you can’t judge a book by its cover, you can’t judge a SQL Server login by its name.

With availability groups, you may notice a login on a primary replica and a “matching” login on the secondary replica. But just because the login is named the same on each replica doesn’t mean they’re the same to SQL Server.

Vanishing Act

Here was the issue I found myself working on. New logins were configured for servers as part of an availability group. Logins were added to both servers, and users were thought to be mapped successfully. Days went by with users accessing databases successfully. But when it came time to failover, users were no longer mapped at all. To remediate the issue, users were remapped, failover occurred to test, and once again, users were no longer mapped.

Make Me a Match

So what was the problem? The logins may have looked the same at first glance, but they did not have matching security identifiers (SIDs). A SQL Server login SID is a unique identifier that SQL Server uses to authenticate logins. Logins may have the same name but if the SID isn’t the same, SQL Server will treat them completely different.

Without the matching SIDs, SQL Server was not able to keep users mapped appropriately with the Availability Group.

To fix this type of issue, we first need to find the SID for a login in sys.server_principals. While it’s not TSQL2sday, we’ll use that login for our test:

SELECT name, sid
FROM sys.server_principals
WHERE name = 'TSQL2sday';
GO

After obtaining the SID for the first login, we’ll want to go connect to our secondary and drop the login with the mismatched SID:

DROP LOGIN TSQL2sday;

Finally, we can recreate the TSQL2sday login and specify the SID in the create statement:

CREATE LOGIN TSQL2sday WITH PASSWORD = 'password123_but_with_EXTRA_security', SID = 0x7B5121EA3A94224DA5F64F130C2CB606

Now the login names appear identical to humans, and the SIDs match for SQL Server.

Identity Crisis

Keeping login SIDs consistent across availability group replicas is an important factor in making sure your database access works as expected. Don’t forget this piece when you go to create logins, or else you might find yourself confused by the results.

Thanks for reading!