SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Managing Logins and Users Across An Availability Group

social

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'

mismatch1

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.

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.

Comments

Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...