Reading Time: 2 minutes

Well, we always say that is not good to use SQL Server logins and is best to enable only Windows Authentication mode on the SQL Server instance as a best practice. It seems that although it *is* a good practice if you are running a native Windows environment, it also makes your life easier if you have an Availability Group deployment in your hands, as you will see.

The problem

When you have SQL Server AlwaysOn Availability Groups the logins that are used in databases that are part of an AG need to have the same SID. Why is that? Let’s dig a little deeper…

When you create a user, a row is added to the table [syslogins] in the [master] system database.

Now, when you map a user to a database and then you start to add roles on that database for the specific user principal then [sysusers] user database table gets a row added with the relevant information.

These 2 tables ([syslogins] and [sysusers]) are joined together on the SID information! Tada!..

USE [userdatabase]
GO
SELECT su.[sid]
	,su.[name]
	,CASE WHEN sl.[name] IS NULL THEN 'Yes' ELSE 'No' END AS 'SID issue'
FROM [dbo].[sysusers] su 
	LEFT OUTER JOIN [master].[dbo].[syslogins] sl on su.[sid]=sl.[sid] 
WHERE su.[issqluser] = 1 AND su.[name] = 'user1'

“OK, and why is this a problem?”, one might ask.

You see if you use SSMS or normal T-SQL to create the login and it will just not work when the database fails over to the other node.

Why?

Let’s say your AG has 2 nodes. You create the user on the 1st node and then you create the user again on the 2nd node. Now, run the following script on each node:

USE [master]
GO
SELECT [sid], [name] FROM [syslogins] WHERE [name] = 'user1';

If you have not cheated, you should see 2 different SIDs for the same user name.

This means that when the fail over happens from the Primary to the Secondary Replica the 2 tables won’t join anymore, so the user will not be able to query the database and thus downtime.

Let’s see how we can fix that.

The Solution

Enters T-SQL: You create the login on the primary replica node with the following script or something like that, that fits your needs:

-- Connect to the PRIMARY REPLICA node of the AG
USE [master]
GO
-- Create the login
CREATE LOGIN user1 WITH PASSWORD = N'aStrongPasswordHere';

If everything runs successfully you run the next to get the SID of the user:

USE [master]
GO
-- Get the SID of the user created
SELECT [sid], [name] FROM [syslogins] WHERE [name] = 'user1';
GO

Now, that you have the SID, switch the connection the secondary replica and execute the following query:

-- Connect to the **SECONDARY** REPLICA node of the AG
USE [master]
GO
CREATE LOGIN user1 WITH PASSWORD = N'useTheSamePassword', sid=<copy-paste the SID from above>;

Now, you are ready to go back to your Primary Replica that has the databases you want to grant the rights for the user and do so freely. The next time your database fails over to the other node it will work without a problem.

Just a side note: why Windows Authentication mode does not have this SID problem? Well, if you think about it, the SID is governed by one source: the Windows Active Directory.

Cheers!