Rajendra Gupta
SQL Server Always On Availability Group

Synchronize logins between Availability replicas in SQL Server Always On Availability Groups

January 21, 2021 by

In this 40th 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:

      Server-level principal

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

      Database principal

    • 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].

      Orphan users

  • 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].

Windows and SQL logins

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]

SQL Server Always On Availability Group

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.

Login failure messages

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.

Create the login on the Secondary replica manually

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.

Check orphan users

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.

Create Login with SID

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

    Use stored procedure sp_help_revlogin and sp_hexadecimal

  • 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.

SSIS Transfer Login task

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.

DBATools query

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

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

Now, run the Windows PowerShell script with DBATools commands.

Run WIndows PowerShell

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.

Newly created logins using SQL Server Always On Availability Group

  • 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

A comprehensive guide to SQL Server Always On Availability Groups on Windows Server 2016
Configure Domain Controller and Active Directory for SQL Server Always On Availability Groups
Configure failover clusters, storage controllers and quorum configurations for SQL Server Always On Availability Groups
Install SQL Server 2019 on Windows Server 2016 with SQL Server Always On Availability Groups
Add a new node into existing SQL Server Always On Availability Groups
Configure Managed Service Accounts for SQL Server Always On Availability Groups
Add or remove a node from SQL Server Always On Availability Groups using T-SQL scripts
Database-level health detection in SQL Server Always On Availability Groups
Automatic Page Repair in SQL Server Always On Availability Groups
Deploy a domain-independent Windows Failover Cluster for SQL Server Always On Availability Groups
Configure a SQL Server Always On Availability Group on the domain-independent Failover Cluster
An overview of distributed SQL Server Always On Availability Groups
Deploy a distributed SQL Server Always On Availability Group
Monitor and failover a Distributed SQL Server Always On Availability Group
Transparent Data Encryption for SQL Server Always On Availability Groups
Configure SQL Server replication for a database in SQL Server Always On Availability Groups
Configuring SQL Server replication for distribution databases in SQL Server Always On Availability Groups
Explore Cross-database MSDTC for distributed transactions in SQL Server Always On Availability Groups
Deploy MSDTC for distributed transactions in SQL Server Always On Availability Groups
Restore an existing availability group database participating in SQL Server Always On Availability Groups
Exploring AG dashboards for monitoring SQL Server Always On Availability Groups
Backup compression in TDE enabled databases in SQL Server Always On Availability Groups
Impact of dropping a login in the active directory tied to SQL Server Always On Availability Groups
Refresh SQL Server Always On Availability Group databases using DBATools PowerShell
Configure SQL Server Reporting Services databases in SQL Server Always On Availability Groups
Suspend and Resume Data Movement in SQL Server Always On Availability Groups
Explore failover types in SQL Server Always On Availability Groups
Explore SQL Server PolyBase External Tables in SQL Server Always On Availability Groups
SQL Server Always On Availability Groups for SQL Server Linux instances
Column-level SQL Server encryption with SQL Server Always On Availability Groups
Make the most of secondary replicas in SQL Server Always On Availability Groups
Apply SQL Server patches or cumulative updates in SQL Server Always On Availability Groups
Monitor SQL Server Always On Availability groups using extended events
The Hub and Spoke model of policy-based management for SQL Server Always On Availability Groups
Custom policies for AG dashboards of SQL Server Always On Availability Groups
Explore dynamic management views for monitoring SQL Server Always On Availability Groups
Dynamic management views for monitoring availability replicas and databases for SQL Server Always On Availability
Configure SQL Server Always On Availability Groups using Windows PowerShell scripts
Configure Integration Services Catalog Database SSISDB in SQL Server Always On Availability Groups
Synchronize logins between Availability replicas in SQL Server Always On Availability Groups
Session timeouts in SQL Server Always On Availability Groups
Lease Timeouts and Health Checks in SQL Server Always On Availability Groups
Rajendra Gupta
Latest posts by Rajendra Gupta (see all)
168 Views