Nisarg Upadhyay
SSIS Transfer Login task

Transferring SQL Logins to the secondary replica of AG using sp_help_revlogin and SSIS Transfer Login tasks

January 14, 2020 by

In this article, I am going to explain how we can transfer logins to the secondary replica of the Always On availability group using sp_help_revlogin and Transfer Logins Task of the SQL Server Integration Services package.

Problem statement

When we configure a new secondary replica, we must move the logins that are created on the primary replica. If we do not move the logins, after failover to the secondary replicas, users will receive the following error:

Login failed while connecting to SQLAG

Error text

TITLE: Connect to Server
——————————
Cannot connect to SQLAG.
——————————
ADDITIONAL INFORMATION:
Login failed for user ‘NUpadhyay.’ (Microsoft SQL Server, Error: 18456)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=18456&LinkId=20476

To demonstrate the scenario, I have created an Availability Group on my workstation. The following are the details:

Host name / Replica name

Role of the replica

SQL01

Primary Replica

SQL02

Secondary Replica

SQL03

Secondary Replica

On the primary replica of the Availability group, I have created a SQL Login named NUpadhyay on the primary replica. It has a bulkadmin fixed server role. Execute the following query to obtain the information:

Following is the output:

Query to get the list of SQL Logins

Now, let us do a planned manual failover. After failover, the SQL02.dc.local will become the primary replica, and SQL01.dc.local will become a secondary replica. To do that, execute the following set of commands on SQLCMD:

See the following image:

PowerShell script o perform the failover

Once failover completes, try to login using NUpadhyay. You will see the following error:

Failed to connect to SQLAG

As you can see, when we failover primary replica to the secondary replica, we are not able to connect to the database engine using SQL Login NUpadhyay. This issue can be fixed by recreating the same login on the secondary replicas. This looks easy, right? But imagine that we have a database server that has 100-200 logins. This task becomes very time-consuming. To resolve this issue, we can use any of the following approaches:

  1. SSIS Transfer Login task
  2. Create stored procedures named sp_help_revlogin and sp_hexadecimal and use it to generate a CREATE LOGIN script for all the users

SSIS Transfer Logins task

We can transfer the logins by using the Transfer Login task in the SQL Server Integration Services package. To do that, open SQL Server Data Tools 2017, create a new integration services project, and on the task flow window, drag and drop Transfer Login Task as shown below:

SSIS Transfer Login task

To configure the Transfer Login task, double-click on Transfer Logins Task, specify the source as SQL01 and destination as SQL02 and execute the package:

SSIS Transfer Login task editor

Now, choose the name of the SQL Login that you want to transfer. To do that, first, select SelectedLogins from the LoginsToTransfer drop-down box, then select the name of the login by clicking on eclipse (…) opposite to the LoginList, tick the name of SQL Login from Select Login dialog box:

Select Logins

Click OK to close the dialog box and execute the package to copy the logins from the source server to the destination server. Once the package executes successfully, you can see that the login NUpadhyay has been created.

SSIS Package execution log:

SSIS Package execution log

New Login in SSMS:

Connected to the SQL02

Create sp_help_revlogin and sp_hexadecimal SP and use them to generate the CREATE LOGIN command

We can use sp_help_revlogin and sp_hexadecimal stored procedure to transfer the logins from the primary replica to secondary replicas. Before I explain the process of copying login between replicas, let me explain how both stored procedures work.

The stored procedure sp_help_revlogin generates the CREATE Login statement for all the SQL Logins on any database instance, and the stored procedure sp_hexadecimal converts the password hash into the text file. Using these procedures, we can generate the T-SQL code to recreate the SQL Logins.

Now, let’s come back to our issue. First, create both stored procedures in the DBA utility database. You can download the scripts from the SQL Server knowledge base article 246133.

Once the procedure is created, execute it using exec <storedprocedure_name> command. Once the procedure executes successfully, it creates a following Create Login command:

  1. The SQL Server certification-based logins start with ##
  2. The NT Authority virtual account
  3. The NT Services virtual account

The above logins are not required to recreate on the secondary replica; hence to filter those logins, we will create another script which does the following tasks:

  1. Insert the SQL Logins and groups whose name does not with ## or NT% in the temporary table named #SQLLogins tables
  2. Use a while loop to iterate through the #SQL_Logins table, select the name of the user/group from the LoginName column and store it in @LoginName variable
  3. Use sp_help_revlogin to generate the Create Login script for the user/group stored in the @LoginName variable

Following is the T-SQL script:

Below is the output of the script:

— Login: sa
CREATE LOGIN [sa] WITH PASSWORD = 0x0200653E7B270B00E2B21F0ED0157B9163D536C083AFBD243C38A06587DEE4992F31193F266EB40AE37477560896F60004A267409EFDD0FB40F87F3A57A2449C8C6E9E9E74A0 HASHED, SID = 0x01, DEFAULT_DATABASE = [master], CHECK_POLICY = ON, CHECK_EXPIRATION = OFF


— Login: DCLocal\administrator
CREATE LOGIN [DCLocal\administrator] FROM WINDOWS WITH DEFAULT_DATABASE = [master]

— Login: NUpadhyay
CREATE LOGIN [NUpadhyay] WITH PASSWORD = 0x0200BC66349CABC7E2DA1DF21146287E9C48204D87B00108C6A09328B4651D531866A05846899FBBCC746A6B10C018014B679DC06ECCA384ADB469A3EBDE3EDE4FBD7FA90466 HASHED, SID = 0xDFFB516810E4234B974514193834C64D, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF


— Login: NisargUpadhyay
CREATE LOGIN [NisargUpadhyay] WITH PASSWORD = 0x02003E426F1A9106140C4B913F7CFBAB2EC577CA1F6C85521C9C6EA9AB1FE4E4AB510BA9B32F54172C513799267774B71F5C5CF90E28BEC2F80049631B219CDB5B7365D8F555 HASHED, SID = 0xB01FBD7C17393447AC33AEAD41E3182E, DEFAULT_DATABASE = [master], CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF

Now connect to SQL02 replica and execute the above query to create the user. You can connect to SQL02 using SQL Server Management Studio, or you can use PowerShell. We will use the SQL Server Management Studio:

Create Login using T-SQL Script

Once the user has been created, try to login using it:

Connect to Database engine

As you can see from below, the SQL Login NUpadhyay is now able to connect to the Availability Group:

Summary

In this article, I have explained how we can transfer SQL Logins from the primary replica to the secondary replica of the Availability Group using Transfer Logins Task of SQL Server Integration Services and the stored procedure named sp_help_revlogin and sp_hexadecimal.

Nisarg Upadhyay
947 Views