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

标签：登录名组中 Always 可用性 SID 副本 SQL GO 辅助副本

介绍

SQL Server Always On 可用性组为 SQL 数据库提供高可用性和灾难恢复解决方案。如果主副本出现任何问题，它会自动对辅助副本上的 AG 数据库进行故障转移。只要我们利用侦听器，您的应用程序就不需要对连接字符串进行任何更改。

但是，在 SQL Always On 中，同步可用性组的各个数据库部分。它不同步登录、代理作业、SSIS 包、链接服务器和服务器配置。在本文的这一部分中，我们将探讨主副本与辅助副本之间的登录同步。

登录和用户的快速回顾

SQL Server 有两个登录名和用户作为安全主体
您可以查询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]

SQL Server Always On 可用性组

在主副本上，您创建登录名并分配 [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 差异。

使用 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) 中配置传输登录任务，并将所需的登录名从主副本传输到辅助副本。

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 命令来自动同步丢失的登录名。

标签：登录名,组中,Always,可用性,SID,副本,SQL,GO,辅助副本
From： https://www.cnblogs.com/lkj371/p/17572434.html