Azure SQL DB Failover Group Orphaned Users

 

I have a client that resides in the Azure data platform arena, focusing mainly on Platform as a Service (PaaS) and Azure SQL DB. They have a decent landscape of Azure resources and currently utilize failover groups within SQL DB to facilitate high availability. Under the covers, failover groups are essentially Availability Groups and have similar issues that you might encounter with the on-premises version.

 

A common issue that you might encounter, which my client did recently, revolves around orphaned users. Orphaned users occur when the user object in the database has a different SID (security identifier) than what the login says it should be. It is also possible that the login may not exist at the server level. Orphaned users are also specifically related SQL Logins and not Active Directory authentication. When dealing with on-premises databases, this was commonly found when restoring a database from one server to another and you had to manually adjust the database user to fix the incorrect SID. Regarding Azure SQL DB and failover groups, orphaned users can also occur. The login is first created on the primary server and then the database user is created in the user database. The syntax would look like this, 

-- While in Master
CREATE LOGIN [User2] WITH PASSWORD = 'ThisIsNotThePassword1234'
GO
-- While in the user database
CREATE USER [User2] FROM LOGIN [USER2]
GO

As soon as the database user is created, the command is sent to the secondary replicas. However, the login is not sent, and this causes the SID of the database user to not match any corresponding logins on the secondary server. In addition, just like availability groups, databases contained in a failover group are read-only. Therefore you cannot modify the data or the database including user objects. You can modify the login in the Master database though, which allows you to resolve the issue.

 

Let’s take a look. 

 

Setup

 

I’ve got two servers in my Azure subscription, one is located in the East US region and the other in the West US region, each hosting a SQL DB named “Demo1”.

 

 

I have configured a fail-over group between these two servers such that sqldbdemo-east is the primary and sqldbdemo-west is the secondary.

 

 

Using SSMS, we can connect to both servers and see that User1 is a login for both servers. User1 is also a database user for Demo1 on both servers (it’s not shown but trust me). Since sqldbdemo-east is the primary, we can add a new user.

 

Before we get started, remember with SQL DB you can’t use a USE statement so you must be in Master to create the login.

CREATE LOGIN [User2] WITH PASSWORD = 'ThisIsNotThePassword1234';

Once the login has been created, we can now create it within the database Demo1. Make sure to change the context of the query to the database in question.

CREATE USER [User2] FROM LOGIN [USER2];

We can verify that the new user exists in Demo1 on sqldbdemo-east.

 

 

If we check the secondary database on sqldbdemo-west, we will also see that the user was created there. Remember that this user was created by the failover group automatically. You do not have to manually create this user; it will be done for you.

 

 

We can also compare the SIDs for both users to ensure that they are the same,

 

 

However, the issue comes into play because the login does not exist yet for User2 on the sqldbdemo-west.

 

 

Since there isn’t a login associated with the user, someone using the credentials for User2 will not be able to authenticate properly. Most likely you will get this type of error,

 

 

This can be fixed by adding the login User2 to sqldbdemo-west. We will use the same password that was used to create User2 on sqldbdemo-east.

 

 

Once the login is created and granted access to Master, we can then log into sqldbdemo-west as shown below.

 

 

Here, we can see that User2 has been authenticated and I can see the databases on the server. However, if I attempt to access Demo1, I get an error,

 

 

The SIDs Did It

 

This issue occurs because the SIDs for the two logins are not the same. Since they are not the same, the user object in Demo1 on sqldbdemo-west is unable to authenticate against it. Remember, that because it is a secondary replica, the database is read-only. You will not be able to do anything with the user object. Instead, you will have to drop and recreate the User2 login on secondary server with appropriate SID. You can find the correct SID to use by looking in these places on the primary server,

• Database level – Sys.database_principals
• Database level – Sys.sysusers
• Master level – Sys.sql_logins

Once you have obtained the correct SID value, while in the context of Master on the secondary server, do the following,

DROP LOGIN [User2]
GO
CREATE LOGIN [User2] WITH PASSWORD = 'ThisIsNotThePassword1234', SID = 0x01060000000000640000000000000000CA6EAC7C69DC024DBB850F80F8E595E6
GO

Now that the database user SID matches the login SID, User2 can now authenticate to the server as well as access the database.

 

 

Summary

 

The cloud offers up a lot of new things however orphaned users can happen there just like they do with on-premises instances. This will only happen with SQL logins but the resolution is straightforward once you see where the issue lies. The next time you have to create user accounts in a failover group, make sure to use the right SID from the start and you will save yourself some headache.