What’s the deal with logins and availability groups?
I’m sure that we all, when creating a login that accesses a database that happens to be part of an availability group, diligently go and create that login on all secondary replicas, don’t we?!
Of course, when that AG fails over to another replica, we want to make sure that all the logins that a user might need in order to access it already exists on what is now the primary node.
Now, I’m pretty sure that most of us will have been in the position where, after a fail-over we get inundated with calls, emails, Skype messages and carrier pigeon drops letting us know that so and so can no longer access the database.
When you look into it, you either find that the login never existed in the first place, so you create it or that it was there but the database user has become orphaned from it (happens when the login SID doesn’t match the SID of the database user, Adrian wrote about orphaned users in Dude where’s my access?).
You remap the orphaned user and everything is good again… that is until the next time you failover and once again you’ll be hit with the same orphaned user problem.
Why is this happening?
The problem is that, because you’ve created the logins on each server individually, they each have a different SID. That means that when you failover, the SID of the database user (which matched the SID of the login on the old primary) will be different from that of the login on the new primary.
What’s the Solution?
To prevent this problem from occurring, you need to ensure that not only does the login exist on all servers but that the SID is also identical (you’ll probably want to make sure that the passwords also match).
This was one of the problems that we encountered time and time again and is something that we developed the Undercover Catalogue to help solve.
The Undercover Catalogue, holds a fair bit of information on Logins, this includes the SID and password hash.
Let’s just take look at what the Catalogue has on the user, David.
SELECT ServerName, LoginName, SID, PasswordHash FROM Catalogue.Logins WHERE LoginName = 'David'
We can see that the login exists on all four servers that make up our AG, that’s a good start. The passwords match (as we can see from the password hash), so we know that we’re not going to get any login failures because of that but look at the SIDs? My betting is that someone’s gone and individually created those logins because the SIDs are all different. Every time we fail over, we’re going to orphan any associated database user.
Detecting These Issues
You can run the following scripts against your Catalogue to alert you if any of these issues exists.
Creating an AG Temp Table
All of the queries are going to require the following temp table, holding details of all your AGs and the nodes that make them up so you’ll need to have the following temp table created and populated appropriately.
IF OBJECT_ID('tempdb.dbo.#AGs') IS NOT NULL
DROP TABLE #AGs
CREATE TABLE #AGs
(AGName SYSName,
ServerName SYSNAME,
AGRole VARCHAR(9))
INSERT INTO #AGs
VALUES ('AG1','SQLUndercoverTest01', 'PRIMARY'),
('AG1','SQLUndercoverTest02', 'SECONDARY'),
('AG1','SQLUndercoverTest03', 'SECONDARY'),
('AG1','SQLUndercoverTest04', 'SECONDARY'),
('AG1','SQLUndercoverTest05', 'SECONDARY')
NOTE: At the time of writing, the current Undercover Catalogue v0.1 release doesn’t include any Availability Group information. This is planned for inclusion in release v0.2 and the table, Catalogue.AvailabilityGroups can act as a drop in replacement to the above temp table.
Missing Logins
To find any nodes that are missing logins
SELECT DISTINCT AGs.AGName, Logins.LoginName, AGs2.ServerName AS [Missing On Node] FROM #AGs AGs JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName JOIN #AGs AGs2 ON AGs.AGName = AGs2.AGName WHERE NOT EXISTS (SELECT 1 FROM #AGs AGs3 JOIN Catalogue.Logins Logins3 ON AGs3.ServerName = Logins3.ServerName WHERE AGs3.AGName = AGs.AGName AND AGs3.ServerName = AGs2.ServerName AND Logins3.LoginName = Logins.LoginName)
Mismatched SIDs
To find any logins where the SID is different from the primary server. The script will also create a script to drop and recreate the login with the correct SID.
SELECT DISTINCT PrimaryLogins.ServerName AS PrimaryServer, SecondaryLogins.ServerName AS SecondaryServer, SecondaryLogins.LoginName, PrimaryLogins.sid AS PrimarySID, SecondaryLogins.sid AS SecondarySID, 'DROP LOGIN ' + QUOTENAME(SecondaryLogins.LoginName) +'; CREATE LOGIN ' + QUOTENAME(SecondaryLogins.LoginName) + ' WITH PASSWORD = 0x' + CONVERT(VARCHAR(MAX), PrimaryLogins.PasswordHash, 2) + ' HASHED, SID = 0x' + CONVERT(VARCHAR(MAX), PrimaryLogins.sid, 2) + ';' AS CreateCMD FROM (SELECT AGs.AGName, AGs.ServerName, Logins.LoginName,Logins.sid, Logins.PasswordHash FROM #AGs AGs JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName WHERE AGs.AGRole = 'PRIMARY') PrimaryLogins JOIN (SELECT AGs.AGName, AGs.ServerName, Logins.LoginName,Logins.sid FROM #AGs AGs JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName WHERE AGs.AGRole = 'SECONDARY') SecondaryLogins ON PrimaryLogins.AGName = SecondaryLogins.AGName AND PrimaryLogins.LoginName = SecondaryLogins.LoginName WHERE PrimaryLogins.sid != SecondaryLogins.sid
Mismatched Passwords
Similar to the above script but working on passwords
SELECT DISTINCT PrimaryLogins.ServerName AS PrimaryServer, SecondaryLogins.ServerName AS SecondaryServer, SecondaryLogins.LoginName, PrimaryLogins.PasswordHash AS PrimaryPasswordHash, SecondaryLogins.PasswordHash AS SecondaryPasswordHash, 'DROP LOGIN ' + QUOTENAME(SecondaryLogins.LoginName) +'; CREATE LOGIN ' + QUOTENAME(SecondaryLogins.LoginName) + ' WITH PASSWORD = 0x' + CONVERT(VARCHAR(MAX), PrimaryLogins.PasswordHash, 2) + ' HASHED, SID = 0x' + CONVERT(VARCHAR(MAX), PrimaryLogins.sid, 2) + ';' AS CreateCMD FROM (SELECT AGs.AGName, AGs.ServerName, Logins.LoginName,Logins.sid, Logins.PasswordHash FROM #AGs AGs JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName WHERE AGs.AGRole = 'PRIMARY') PrimaryLogins JOIN (SELECT AGs.AGName, AGs.ServerName, Logins.LoginName,Logins.sid, Logins.PasswordHash FROM #AGs AGs JOIN Catalogue.Logins Logins ON AGs.ServerName = Logins.ServerName WHERE AGs.AGRole = 'SECONDARY') SecondaryLogins ON PrimaryLogins.AGName = SecondaryLogins.AGName AND PrimaryLogins.LoginName = SecondaryLogins.LoginName WHERE PrimaryLogins.PasswordHash != SecondaryLogins.PasswordHash
If you wanted to, you could always run these scripts as part of a scheduled job, emailing you the results (which just so happens to be exactly the setup that we use here) to constantly keep an eye on your logins and alert you if you have any issues.
There’s a good chance that these scripts will be included in some future release of the Undercover Catalogue or perhaps incorporated into the Undercover Inspector to make spotting these issues easier, so keep an eye out for that.
Thanks for reading and hopefully you’ll find these scripts useful.
As always, the scripts in this blog post can be found HERE on our GitHub repo.
