ssis sql_使用sp_help_revlogin和SSIS传输登录任务将SQL登录名传输到AG的辅助副本

ssis sql

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.

在本文中，我将解释如何使用SQL Server Integration Services程序包的sp_help_revlogin和“ 传输登录名”任务将登录名传输到Always On可用性组的辅助副本。

问题陈述 (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

标题：连接到服务器
——————————
无法连接到SQLAG。
——————————
附加信息：
用户“ NUpadhyay”的登录失败。 （Microsoft SQL Server，错误：18456）
要获取帮助，请单击：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

主机名/副本名	副本的作用
SQL01	主副本
SQL02	二级副本
SQL03	二级副本

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:

在“可用性”组的主副本上，我在主副本上创建了一个名为NUpadhyaySQL登录名。 它具有bulkadmin固定服务器角色。 执行以下查询以获取信息：

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:

现在，让我们执行计划的手动故障转移。 故障转移后， SQL02.dc.local将成为主副本，而SQL01.dc.local将成为辅助副本。 为此，请在SQLCMD上执行以下命令集：

: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:

故障转移完成后，尝试使用NUpadhyay登录。 您将看到以下错误：

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:

如您所见，当我们将主副本故障转移到辅助副本时，我们将无法使用SQL Login NUpadhyay连接到数据库引擎。 可以通过在辅助副本上重新创建相同的登录名来解决此问题。 这看起来很容易，对吧？ 但是，假设我们有一台具有100-200个登录名的数据库服务器。 该任务非常耗时。 要解决此问题，我们可以使用以下任何一种方法：

1. SSIS Transfer Login task
   SSIS转移登录任务
2. sp_help_revlogin and sp_help_revlogin和sp_hexadecimal and use it to generate a sp_hexadecimal的存储过程，并使用它为所有用户生成CREATE LOGIN script for all the users CREATE LOGIN脚本

SSIS传输登录任务 (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:

我们可以使用SQL Server Integration Services程序包中的“传输登录名”任务来传输登录名。 为此，请打开SQL Server Data Tools 2017，创建一个新的Integration Services项目，然后在任务流窗口上，拖放“ 传输登录任务” ，如下所示：

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:

要配置传输登录任务的传输登录任务，双击，指定源作为SQL01和目的地SQL02和执行包：

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:

现在，选择要传输SQL登录名。 为此，首先，从LoginsToTransfer下拉框中选择SelectedLogins ，然后通过单击Eclipse选择登录名。 （…）与LoginList相对，在“ 选择登录”对话框中勾选SQL登录名：

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.

单击“ 确定”关闭对话框并执行程序包，以将登录名从源服务器复制到目标服务器。 包成功执行后，您可以看到已创建登录NUpadhyay 。

SSIS Package execution log:

SSIS包执行日志：

New Login in SSMS:

SSMS中的新登录名：

创建sp_help_revlogin和sp_hexadecimal SP，并使用它们生成CREATE LOGIN命令 (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.

我们可以使用sp_help_revlogin和sp_hexadecimal存储过程将登录名从主副本转移到辅助副本。 在解释复制副本之间的登录过程之前，让我解释两个存储过程如何工作。

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.

存储过程sp_help_revlogin为任何数据库实例上的所有SQL登录名生成CREATE Login语句，存储过程sp_hexadecimal将密码哈希转换为文本文件。 使用这些过程，我们可以生成T-SQL代码以重新创建SQL登录名。

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.

现在，让我们回到我们的问题。 首先，在DBA实用程序数据库中创建两个存储过程。 您可以从SQL Server知识库文章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:

创建过程后，请使用exec <storedprocedure_name>命令执行该过程。 该过程成功执行后，它将创建以下“ 创建登录”命令：

1. The SQL Server certification-based logins start with ##
   基于SQL Server认证的登录名以##开头
2. The NT Authority virtual account
   NT Authority虚拟帐户
3. The NT Services virtual account
   NT Services虚拟帐户

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. ## or #SQLLogins的临时表中插入名称不是NT% in the temporary table named##或 #SQLLogins tables NT％SQL登录名和组。
2. #SQL_Logins table, select the name of the user/group from the #SQL_Logins表，从LoginName column and store it in LoginName列中选择用户/组的名称，并将其存储在@LoginName variable @LoginName变量中
3. sp_help_revlogin to generate the Create Login script for the user/group stored in the sp_help_revlogin为存储在@LoginName variable @LoginName变量中的用户/组生成“创建登录”脚本。

Following is the T-SQL script:

以下是T-SQL脚本：

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

—登录：sa
使用密码= 0x0200653E7B270B00E2B21F0ED0157B9163D536C083AFBD243C38A06587DEE4992F31193F266EB40AE37477560896F60004A267409EFDD0FB40F87F3A57A2449C8C6E9E9E9E74E


—登录：DCLocal \ administrator
使用DEFAULT_DATABASE = [master]从WINDOWS创建登录[DCLocal \ administrator]

—登录：NUpadhyay
CREATE LOGIN [NUpadhyay] WITH PASSWORD = 0x0200BC66349CABC7E2DA1DF21146287E9C48204D87B00108C6A09328B4651D531866A05846899FBBCC746A6B10C018014B679DC06ECCA384ADB469A3EBDE3EDE4FBD7FA90466 HASHED，SID = 0xDFFB516810E4234B974514193834C64D，DEFAULT_DATABASE = [主]，CHECK_POLICY = OFF，CHECK_EXPIRATION = OFF


—登录：NisargUpadhyay
CREATE LOGIN [NisargUpadhyay] WITH PASSWORD = 0x02003E426F1A9106140C4B913F7CFBAB2EC577CA1F6C85521C9C6EA9AB1FE4E4AB510BA9B32F54172C513799267774B71F5C5CF90E28BEC2F80049631B219CDB5B7365D8F555 HASHED，SID = 0xB01FBD7C17393447AC33AEAD41E3182E，DEFAULT_DATABASE = [主]，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:

现在连接到SQL02副本并执行上面的查询以创建用户。 您可以使用SQL Server Management Studio连接到SQL02，也可以使用PowerShell。 我们将使用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:

从下面可以看到，SQL登录NUpadhyay现在可以连接到可用性组：

摘要 (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.

在本文中，我已经说明了如何使用SQL Server Integration Services的“传输登录任务”以及名为sp_help_revlogin和sp_hexadecimal的存储过程将SQL登录名从可用性组的主副本传输到辅助副本。

翻译自: https://www.sqlshack.com/transferring-sql-logins-to-the-secondary-replica-of-ag-using-sp_help_revlogin-and-ssis-transfer-login-tasks/

ssis sql