在 SQL Server Always On 可用性组中的可用性副本之间同步登录

介绍

SQL Server Always On 可用性组为 SQL 数据库提供高可用性和灾难恢复解决方案。如果主副本出现任何问题，它会自动对辅助副本上的 AG 数据库进行故障转移。只要我们利用侦听器，您的应用程序就不需要对连接字符串进行任何更改。但是，在 SQL Always On 中，同步可用性组的各个数据库部分。它不同步登录、代理作业、SSIS 包、链接服务器和服务器配置。在本文的这一部分中，我们将探讨主副本与辅助副本之间的登录同步。

登录和用户的快速回顾

• SQL Server 有两个登录名和用户作为安全主体
  • 服务器级主体：登录名 – master 数据库有一个登录名并包含一个 SID。您可以查询sys.server_principals来检查登录名及其 SID

    它返回 Windows、SQL 登录、服务器角色和证书。每个登录都有一个SID，如下图：

     

    1	select name, sid, type_desc from sys.server_principals

    

  • 数据库主体：用户 – 我们将这些登录映射到数据库。数据库用户使用其 SID 与Master库中的登录名进行映射

    

  您可以查询sys.database_principals以获取数据库主体列表。如下所示，我们的登录名 [SQLAg2User] 和数据库用户 [SQLAg2user] 具有类似的 SID。

  

• Windows 和 SQL 登录

我们可以在 SQL Server 中使用 Windows 或 SQL 身份验证。在 Windows 身份验证中，您从 Windows 活动目录创建用户。它使用活动目录 SID 进行身份验证。这意味着如果您在多个 SQL 实例上创建了 Windows 登录名，这些登录名将具有相同的 SID。

在 SQL 身份验证中，您创建一个带有密码的登录名。它为这些 SQL 登录生成一个 SID。如果您使用相似的名称和密码创建登录，则它具有不同的 SID。您需要在 SQL Server Always On 可用性组的不同实例之间手动同步 SID。您可以使用相同的密码哈希和 SID 在辅助副本上创建登录名。

例如，以下脚本生成一个 CREATE LOGIN 脚本，其中包含登录名 [SQLAG2Use] 的密码哈希和 SID。

 

1 2 3 4 5 6

SELECT N'CREATE LOGIN ['+sp.[name]+'] WITH PASSWORD=0x'+     CONVERT(nvarchar(max), l.password_hash, 2)+N' HASHED, '+     N'SID=0x'+CONVERT(nvarchar(max), sp.[sid], 2)+N';' FROM master.sys.server_principals AS sp INNER JOIN master.sys.sql_logins AS l ON sp.[sid]=l.[sid] WHERE sp.name='SQLAG2User'

您需要复制此脚本并在辅助副本上执行它，以创建具有与主副本类似的 SID 的登录名。它将 SID 与辅助副本数据库相匹配，并且您的应用程序不会因 AG 故障转移而面临登录问题。

因此，您需要在 SQL Server Always On 可用性组的所有副本中手动同步以下对象。

• 服务器主体（登录）
• 服务器角色、成员身份和对象权限

环境详情

在本文中，我们考虑两个节点 SQL Server Always On 可用性组，其详细信息如下：

 

• 主副本：SQLNode1\INST1
• 次要副本：SQLNode2\INST1
• 数据库：[MyNewDB]

 

在主副本上，您创建登录名并分配 [MyNewDB] 可用性组数据库的 db_datareader 权限。

 

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

USE [master] GO CREATE LOGIN [Demologin] WITH PASSWORD=N'India@123', DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO USE [MyNewDB] GO CREATE USER [Demologin] FOR LOGIN [Demologin] GO USE [MyNewDB] GO ALTER ROLE [db_datareader] ADD MEMBER [Demologin] GO

现在，连接到您的辅助副本并查看登录是否有效。它不起作用，因为辅助副本上不存在相应的登录名。

您可以使用以下方法来解决此问题。

手动在辅助副本上创建登录名

在此方法中，您可以在辅助副本上手动创建登录名。

 

1 2 3 4

USE [master] GO CREATE LOGIN [Demologin] WITH PASSWORD=N'India@123', DEFAULT_DATABASE=[MyNewDB], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF GO

如果我们查询辅助副本的 SQL 登录名和数据库用户，我们会注意到不同的 SID。这是因为数据库 SID 与主副本 SID 相似。但是，我们在生成新 SID 的辅助副本上手动创建了登录名。

由于登录名和用户SID不同，它是一个孤立用户。您可以使用 sp_change_users_login 存储过程来获取孤立用户的列表。

 

1	sp_change_users_login 'report'

您可以按照如何在 SQL Server 中发现和处理孤立数据库用户 一文来修复数据库中的孤立用户。

或者，您可以在主副本上运行脚本来生成脚本，在辅助副本上执行以避免 SID 差异。

 

1 2 3 4

CREATE LOGIN [Demologin] WITH PASSWORD=0x0200B9AD82CA73B5065CDCA01F3C5B225CA9D0B9D5DE7394F4701575E25CC343D045 45FB8086EC8B356E57385CA9A505C45A2EF903B316544B90459FB7FA24392E5E04023226 HASHED, SID=0x04162837B648EC4AA174954E9C0960AF;

使用存储过程sp_help_revlogin和sp_hexadecimal

Microsoft 提供了存储过程 sp_help_revlogin 将登录名传输到不同的实例。您创建存储过程，在主副本上执行，在辅助副本上复制脚本输出，然后执行它。

• 打开Microsoft 文档并复制脚本以在主副本上创建存储过程 sp_help_revlogin 和 sp_hexadecimal

• 执行存储过程 sp_help_revlogin。它返回带有原始 SID 和密码的登录脚本

  

• 连接到辅助副本，执行 sp_help_revlogin 的输出（相关登录）。它在辅助副本实例上创建 SQL 登录名

SSIS传输登录任务

您可以在集成服务 (SSIS) 中配置传输登录任务，并将所需的登录名从主副本传输到辅助副本。

您可以参考文章《将 SQL 登录名传输到 AG SSIS 传输登录任务的辅助副本》进行配置。

使用DBATools同步副本之间的登录

DBATools 提供了一组有用的函数和 cmdlet，用于执行 SQL Server 数据库管理任务。您可以浏览DBATools类别来尝试这些有用的 cmdlet。

我们可以利用 DBATools 命令来自动同步登录。它需要以下命令。

 

• Get-DbaAgReplica：它返回 SQL Server Always On 可用性组副本信息。例如我们可以用它来返回主备副本实例信息
• Get-DbaLogin：我们可以使用 Get-DbaLogin 返回登录名（Windows 和 SQL）、凭据、证书对象。我们将其与Copy-DbaLogin命令结合使用，将登录从源（主）副本迁移到目标（辅助）副本

 

在下面的 PowerShell 脚本中，我们执行以下任务：

• 使用 Get-DbaAgReplica 命令收集有关 SQL Server Always On 可用性组中的主副本和辅助副本的信息
  • $primaryReplica 存储主副本信息
  • $secondaryReplicas 存储次要副本信息
• 该脚本在主副本上运行 Get-DbaLogin 命令并将其存储在 $LoginsOnPrimary 变量中
• 对于辅助副本，它执行以下任务
  • 首先，它从辅助副本生成登录名并将其存储到 $LoginsOnSecondary 中
  • 它比较 $LoginsOnPrimary 和 $LoginsOnSecondary
  • 如果发现任何差异，它将特定登录复制到辅助副本上

 

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

$AGLSN = 'AGUsingPowerShell'   $primaryReplica =    Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Primary $secondaryReplicas = Get-DbaAgReplica -SqlInstance $AGLSN | Where Role -eq Secondary      $LoginsOnPrimary = (Get-DbaLogin -SqlInstance $primaryReplica.Name)      $secondaryReplicas | ForEach-Object {              $LoginsOnSecondary = (Get-DbaLogin -SqlInstance $_.Name)          $diff = $LoginsOnPrimary | Where-Object Name -notin ($LoginsOnSecondary.Name)     if($diff) {         Copy-DbaLogin -Source $primaryReplica.Name -Destination $_.Name -Login $diff.Nane     }   }

如果主副本和辅助副本上都存在登录名，则会跳过该登录名并在“状态”列中提供信息。

对于此演示，我们创建两个登录名：

• 具有 [dbcreator] 服务器角色和 [db_datareader] 、[dba_datawriter] AG 数据库级别角色的 SQL 登录 [DBALogins1]。它使用以下脚本来创建登录名并分配权限

   

  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 用户 [aduser] 在可用性组数据库上具有服务器角色 [processadmin] 和 [db_owner] 角色

   

  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

现在，使用 DBATools 命令运行 Windows PowerShell 脚本。

如上面突出显示的，它显示最近创建的登录的成功状态，并跳过剩余的登录，因为它们已经存在于辅助副本上。您可以比较两个用户的 SID、登录名及其对主副本和辅助副本的权限。

• 注意：如果您更改主副本上的 SQL 登录密码，则还应在辅助副本上手动修改该密码。

结论

在本文中，我们学习了几种在 SQL Server Always On 可用性组中同步主副本及其相应辅助副本之间登录的方法。您必须保持登录同步，否则您可能会在 AG 故障转移后开始收到用户的投诉。您还可以使用 DBATools PowerShell 命令来自动同步丢失的登录名。

引用

https://learn.microsoft.com/en-us/sql/sql-server/failover-clusters/troubleshoot-orphaned-users-sql-server?view=sql-server-ver16