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:
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:
|
1 2 3 4 5 6 7 |
SELECT sp.name AS [Name of Role], member.name AS [Name of Member] FROM sys.server_role_members srm JOIN sys.server_principals AS sp ON srm.role_principal_id = sp.principal_id JOIN sys.server_principals AS member ON srm.member_principal_id = member.principal_id; |
Following is the output:
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:
|
1 2 3 4 5 |
:Connect SQLAG :Connect SQL01 ALTER AVAILABILITY GROUP [SQLAAG] FAILOVER; GO GO |
See the following image:
Once failover completes, try to login using NUpadhyay. You will see the following error:
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:
- SSIS Transfer Login task
- 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:
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:
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:
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:
New Login in SSMS:
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:
- The SQL Server certification-based logins start with ##
- The NT Authority virtual account
- 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:
- Insert the SQL Logins and groups whose name does not with ## or NT% in the temporary table named #SQLLogins tables
- 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
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
declare @LoginCount int declare @LoginName sysname declare @i int =0 create table #SQL_Logins (LoginName sysname) insert into #SQL_Login (LoginName) select name from sys.server_principals where (LEFT(name, 4) NOT IN ('NT A', 'NT S') AND TYPE IN ('U', 'G')) OR (LEFT(name, 2) <> '##' AND TYPE = 'S'); set @LoginCount = (select count(LoginName) from #SQL_Logins) While(@LoginCount>@i) begin set @LoginName = (select top 1 name from #SQL_Logins) exec sp_help_revlogin @LoginName delete from #SQL_Login where name=@LoginName set @i=@i+1 End |
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:
Once the user has been created, try to login using it:
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.
He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com
- Different ways to identify and change compatibility levels in SQL Server - July 22, 2024
- Copy SQL Databases between Windows 10 and CentOS using SQL Server data tools - October 19, 2022
- Changing the location of FILESTREAM data files in SQL Database - October 14, 2022