In this 40^th article on SQL Server Always On Availability Group series, we will discuss logins synchronization between replicas.

Introduction

SQL Server Always On Availability Groups provides high availability and disaster recovery solution for SQL databases. In case of any issues with the primary replica, it automatically failovers the AG databases on the secondary replica. Your application does not require any changes in the connection strings provided we leverage the listener.

However, in SQL Always On synchronizes the individual database part of the availability group. It does not synchronize logins, Agent jobs, SSIS packages, linked servers and server configurations. In this part of the article, we explore logins synchronization between primary to secondary replicas.

A quick recap on logins and users

• SQL Server has two logins and users as security principals
  • Server-level principal: Login – The master database has a login and contains a SID. You can query sys.server_principals to check login and their SID’s

    It returns the Windows, SQL logins, server roles and certificates. Each login has a SID, as shown below:

    1	select name, sid, type_desc from sys.server_principals

    

  • Database principal: User – We map these logins to the database. The database user maps with the master database logins using their SID’s

    

  You can query sys.database_principals to get a list of database principals. As shown below, we have similar SID’s for a login [SQLAg2User] and database user [SQLAg2user].

  

• Windows and SQL logins

We can use Windows or SQL authentications in SQL Server. In the Windows authentication, you create a user from the Windows active directory. It uses the active directory SID’s for authentications. It means if you have created a Windows login on multiple SQL instances, these logins have the same SID’s.

In the SQL authentication, you create a login with a password. It generates a SID for these SQL logins. If you create the login with a similar name and password, it has a different SID. You need to manually sync the SIDs between different instances of SQL Server Always On Availability Group. You can create the login on the secondary replica with the same password hash and SID’s.

For example, the following script generates a CREATE LOGIN script with password hash, SID for a login [SQLAG2Use].

You do copy this script and execute it on the secondary replica to create the login with SID similar to the primary replica. It matches the SID with the secondary replica database, and your application does not face login issues due to AG failover.

Therefore, you need to sync the following objects manually in all replicas of SQL Server Always On Availability Group.

• Server principals (logins)
• Server roles, memberships and object permissions

Environment details

For this article, we consider two nodes SQL Server Always On Availability Groups with the following details:

• Primary Replica: SQLNode1\INST1
• Secondary Replica: SQLNode2\INST1
• Database: [MyNewDB]

On the primary replica, you create a login and assign db_datareader permissions on the [MyNewDB] availability group database.

Now, connect to your secondary replica and see if the login works. It won’t work because the corresponding login does not exist on the secondary replica.

You can use the following approach for resolving this issue.

Create the login on the Secondary replica manually

In this method, you manually create the login on a secondary replica.

If we query the secondary replica for SQL login and database user, we notice different SID’s. It is because the database SID is similar to the primary replica SID. However, we created the login manually on the secondary replica that generates a new SID.

Due to the difference between the login and user SID, it is an Orphan user. You can use the sp_change_users_login stored procedure to get a list of the orphaned user.

You can follow the article, How to discover and handle orphaned database users in SQL Server, to fix the orphan user in your database.

Alternatively, you can run the script on the primary replica to generate the script, execute on the secondary replica to avoid the SID difference.

Use stored procedure sp_help_revlogin and sp_hexadecimal

Microsoft provided the stored procedure sp_help_revlogin to transfer the logins to a different instance. You create the stored procedure, execute on the primary replica, copy the script output on the secondary replica, execute it.

• Open the Microsoft docs and copy the scripts to create the stored procedure sp_help_revlogin and sp_hexadecimal on the primary replica

• Execute the stored procedure sp_help_revlogin. It returns the login script with the original SID and password

  

• Connect to the secondary replica, execute the output of sp_help_revlogin ( relevant logins). It creates the SQL login on the secondary replica instance

SSIS transfer login task

You can configure a transfer login task in the integration service (SSIS) and transfer the required logins from the primary to secondary replicas.

You can refer to the article, Transferring SQL Logins to the secondary replica of AG SSIS Transfer Login tasks for configurations.

Use DBATools to synchronize the logins between the replicas

DBATools provides a collection of useful functions, cmdlets for performing database administration tasks for SQL Server. You can go through the DBATools category for experimenting with these useful cmdlets.

We can leverage DBATools commands to synchronize the logins automatically. It requires the following commands.

• Get-DbaAgReplica: It returns the information SQL Server Always On Availability Group replicas. For example, we can use it to returns the primary and secondary replica instance information
• Get-DbaLogin: We can use Get-DbaLogin to return the logins (both Windows and SQL), credentials, certificates objects. We use it in combination with the Copy-DbaLogin command that migrates the logins from the source( primary) to the destination ( secondary) replica

In the below PowerShell script, we do the following tasks:

• Gather information about the primary and secondary replicas in SQL Server Always On Availability Groups using the Get-DbaAgReplica command
  • $primaryReplica stores the primary replica information
  • $secondaryReplicas stores the secondary replicas information
• The script runs the Get-DbaLogin command on the primary replica and stores it on the $LoginsOnPrimary variable
• For the secondary replica, it does the following tasks
  • First, it generates the logins from the secondary replica and stores it into the $LoginsOnSecondary
  • It compares the $LoginsOnPrimary and $LoginsOnSecondary
  • If there is any difference found, it copies the specific login onto a secondary replica

If the logins exist on both the primary and secondary replica, it skips it and gives information in the Status column.

For this demo, let’s create two logins:

• SQL login [DBALogins1] with [dbcreator] server role and [db_datareader] ,[dba_datawriter] AG database level role. It uses the following script to create the login and assigning permissions

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

  USE [master]GO CREATE LOGIN [DBALogins1] WITH PASSWORD=N'India@123' MUST_CHANGE, DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=ON, CHECK_POLICY=ON GO ALTER SERVER ROLE [dbcreator] ADD MEMBER [DBALogins1] GO use [MyNewDB]; GO use [master]; GO USE [MyNewDB] GO CREATE USER [DBALogins1] FOR LOGIN [DBALogins1] GO USE [MyNewDB] GO ALTER ROLE [db_datareader] ADD MEMBER [DBALogins1] GO USE [MyNewDB] GO ALTER ROLE [db_datawriter] ADD MEMBER [DBALogins1] GO

• AD user [aduser] with server role [processadmin] and [db_owner] role on availability group database

  1 2 3 4 5 6 7 8 9 10 11 12 13 14

  USE [master] GO CREATE LOGIN [MYDEMOSQL\aduser] FROM WINDOWS WITH DEFAULT_DATABASE=[master] GO ALTER SERVER ROLE [processadmin] ADD MEMBER [MYDEMOSQL\aduser] GO USE [MyNewDB] GO CREATE USER [MYDEMOSQL\aduser] FOR LOGIN [MYDEMOSQL\aduser] GO USE [MyNewDB] GO ALTER ROLE [db_owner] ADD MEMBER [MYDEMOSQL\aduser] GO

Now, run the Windows PowerShell script with DBATools commands.

As highlighted above, it shows the successful status for recently created logins and skips the remaining because they already exist on a secondary replica. You can compare the SID of both users, logins and their permissions on the primary and secondary replicas.

• Note: If you change the password for a SQL login on the primary replica, you should manually modify it on the secondary replica as well.

Conclusion

In this article, we learned several methods to synchronize the logins between the primary and its corresponding secondary replica in SQL Server Always On Availability Group. You must keep the logins synchronized else you may start receiving complaints from the users after AG failover. You can use DBATools PowerShell commands as well to automatically synchronizes the missing logins.

Table of contents

• Author
• Recent Posts

Rajendra Gupta

Hi! I am Rajendra Gupta, Database Specialist and Architect, helping organizations implement Microsoft SQL Server, Azure, Couchbase, AWS solutions fast and efficiently, fix related issues, and Performance Tuning with over 14 years of experience.

I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.

I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.

Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.

Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com

View all posts by Rajendra Gupta

Latest posts by Rajendra Gupta (see all)

• Understanding PostgreSQL SUBSTRING function - September 21, 2024
• How to install PostgreSQL on Ubuntu - July 13, 2023
• How to use the CROSSTAB function in PostgreSQL - February 17, 2023