使用 Microsoft Entra 驗證設定複寫 - 由 Azure Arc 啟用的 SQL Server

  • 發行項

適用於：SQL Server 2022 (16.x)

本文提供針對已啟用 Azure Arc 的 SQL Server 使用驗證搭配 Microsoft Entra ID (先前稱為 Azure Active Directory) 來設定交易式和快照式複寫的步驟。

概觀

SQL Server 2022 的累積更新 6 引進了複寫的 Microsoft Entra 驗證支援，並已在累積更新 12 中正式推出。 當您使用 Microsoft Entra 驗證進行複寫時，唯一不同的步驟是第一個步驟。 具體而言，請建立 Microsoft Entra 登入，並授與系統管理員權限。

之後，請使用復寫預存程式中的 Microsoft Entra 登入，以像平常一樣設定交易式或快照式複寫。

注意

從 SQL Server 2022 CU 6 開始，使用工作階段追蹤旗標 11561 停用複寫的 Microsoft Entra 驗證。

必要條件

若要使用 Microsoft Entra 驗證設定複寫，必須符合下列必要條件：

  • 從累積更新 6 開始，由 Azure-Arc 啟用 SQL Server 2022。
  • 為複寫拓撲中的每個伺服器設定 Microsoft Entra 驗證。 檢視教學課程：為 SQL Server 設定 Microsoft Entra 驗證以了解詳情。
  • SQL Server Management Studio (SSMS) v19.1 或更高版本或 Azure Data Studio。
  • 連接到發行者和訂閱者的使用者是固定伺服器角色的 sysadmin 成員。
  • 必須使用來自受信任證書頒發機構單位 (CA) 或自我簽署憑證的憑證來加密連線。
    • 如果使用自我簽署憑證，則必須將它匯入用戶端計算機，並安裝到 [受信任的憑證] 清單中，用戶端才能信任 SQL Server。 選取 SQL Server Management Studio (SSMS) 中的 [信任伺服器憑證] 選項，無法略過這項要求，因為它不適用於複寫。

限制

使用 Microsoft Entra 驗證設定複寫目前有下列限制：

  • 目前只能使用 Transact-SQL (T-SQL) 和複寫預存程序、SSMS v19.1 或更新版本的複寫精靈，或 Azure Data Studio 來設定複寫。 目前無法使用 RMO 複寫物件或其他命令列語言來設定複寫。
  • 複寫拓撲中的每個伺服器至少都必須在 SQL Server 2022 CU 6 以上。 不支援舊版 SQL Server。

從 Microsoft Entra ID 建立 SQL 登入

建立 Microsoft Entra 登入，並為其授與 sysadmin 角色。

若要建立 Microsoft Entra 登入並將其指派為 sysadmin，請使用下列 Transact-SQL (T-SQL) 命令：

USE master
CREATE LOGIN [login_name] FROM EXTERNAL PROVIDER
EXEC sp_addsrvrolemember @loginame='login_name', @rolename='sysadmin'

例如，若要新增 newuser@tenant.com 登入名稱，請使用此命令：

USE master
CREATE LOGIN [newuser@tenant.com] FROM EXTERNAL PROVIDER
EXEC sp_addsrvrolemember @loginame='newuser@tenant.com', @rolename='sysadmin'

建立散發資料庫

使用 sp_adddistributiondb 建立散發資料庫。

以下是在經銷商建立散發資料庫的範例指令碼：

EXEC sp_adddistributiondb @database = N'distribution_db', 
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER22\MSSQL\DATA', 
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER22\MSSQL\DATA', 
@log_file_size = 2, @min_distretention = 0, @max_distretention = 72, 
@history_retention = 48, @deletebatchsize_xact = 5000, 
@deletebatchsize_cmd = 2000, @security_mode = 1

下列範例會在散發資料庫中建立資料表 UIProperties，並設定 SnapshotFolder 屬性，讓快照集代理程式知道寫入複寫快照集的位置：

USE [distribution_db] 
IF (not exists (SELECT * FROM sysobjects WHERE NAME = 'UIProperties' and TYPE = 'U ')) 
CREATE TABLE UIProperties(id int) 
IF (exists(SELECT * FROM::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
EXEC sp_updateextendedproperty N'SnapshotFolder', N' C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER22\MSSQL\DATA', 
'user', dbo, 'table', 'UIProperties' 
ELSE 

EXEC sp_addextendedproperty N'SnapshotFolder', N' C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER22\MSSQL\DATA', 
'user', dbo, 'table', 'UIProperties'

下列指令碼會將發行者設定為使用經銷商資料庫，並定義 AD 使用者登入，以及要用於複寫的密碼：

EXEC sp_adddistpublisher @publisher = N'publisher_db', @distribution_db = N'distribution_db', 
@security_mode = 0, @login = N'newuser@tenant.com', @password = N'password', 
@working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER22\MSSQL\ReplData', 
@trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'

啟用複寫

使用 sp_replicationdboption 在發行者資料庫上啟用複寫，例如 testdb，如下列範例：

EXEC sp_replicationdboption @dbname = N'testdb', @optname = N'publish', @value = N'true'

新增發行集

使用 sp_addpublication 來新增發行集。

可以設定交易式或快照式複寫。

請遵循下列步驟來建立交易式複寫。

首先，設定記錄讀取器代理程式：

USE [AdventureWorksDB] 
EXEC [AdventureWorksDB].sys.sp_addlogreader_agent @job_login = null, @job_password = null, 
@publisher_security_mode = 2, @publisher_login = N'newuser@tenant.com', 
@publisher_password = N'<password>', @job_name = null 
GO

然後，建立交易式發行集：

use [AdventureWorksDB] 
exec sp_addpublication @publication = N'AdvWorksProducTrans', 
@description = N'Publication of database ''AdventureWorksDB'' from Publisher 'N'publisher_db''.', 
@sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', 
@allow_anonymous = N'false', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', 
@compress_snapshot = N'false', @ftp_port = 21, @allow_subscription_copy = N'false', 
@add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', 
@independent_agent = N'true', @immediate_sync = N'false', @allow_sync_tran = N'true', 
@allow_queued_tran = N'true', @allow_dts = N'false', @replicate_ddl = 1, 
@allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', 
@enabled_for_het_sub = N'false', @conflict_policy = N'pub wins'

然後，透過使用 @publisher_login 的 Microsoft Entra 登入，以及為發行者定義密碼，建立快照集代理程式並儲存發行者的快照集檔案：

use [AdventureWorksDB] 
exec sp_addpublication_snapshot @publication = N'AdvWorksProducTrans', @frequency_type = 1,
 @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, 
@frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, 
@job_login = null, @job_password = null, @publisher_security_mode = 2, 
@publisher_login = N'newuser@tenant.com', @publisher_password = N'<password>'

最後，將文章 TestPub 新增至發行集：

use [AdventureWorksDB] 
exec sp_addarticle @publication = N'AdvWorksProducTrans', @article = N'testtable', 
@source_owner = N'dbo', @source_object = N'testtable', @type = N'logbased', 
@description = null, @creation_script = null, @pre_creation_cmd = N'drop', 
@schema_option = 0x000000000803509D, @identityrangemanagementoption = N'manual', 
@destination_table = N'testtable', @destination_owner = N'dbo', @vertical_partition = N'false'

建立訂閱

使用 sp_addsubscription 來新增訂閱者，然後使用發行者上的 sp_addpushsubscription_agent 來建立發送訂閱，或在訂閱者上的 sp_addpullsubscription_agent 建立提取訂閱。 使用 @subscriber_login 的 Microsoft Entra 登入。

下列範例指令碼會新增訂用帳戶：

USE [testdb] 
EXEC sp_addsubscription @publication = N'testpub', @subscriber = N'<subscription_server>', 
@destination_db = N'testdb', @subscription_type = N'Push', @sync_type = N'automatic', 
@article = N'all', @update_mode = N'read only', @subscriber_type = 0

下列範例指令碼會在發行者端新增發送訂閱代理程式：

EXEC sp_addpushsubscription_agent @publication = N'testpub', @subscriber = N'<subscription server.', 
@subscriber_db = N'testdb', @job_login = null, @job_password = null, @subscriber_security_mode = 2, 
@subscriber_login = N'newuser@tenant.com', @subscriber_password = 'password', @frequency_type = 64, 
@frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, 
@frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, 
@active_end_time_of_day = 235959, @active_start_date = 20220406, @active_end_date = 99991231, @enabled_for_syncmgr = N'False', @dts_package_location = N'Distributor'

複寫預存程序

這些複寫預存程序中的下列參數在 CU 6 for SQL Server 2022 中修改，以支援 Microsoft Entra 進行複寫驗證：

下列值會定義這些預存程序的安全性模式：

  • 0 指定 SQL Server 驗證。
  • 1 指定 Windows 驗證。
  • 2 指定從 SQL Server 2022 CU 6 開始的 Microsoft Entra 密碼驗證。
  • 3 指定從 SQL Server 2022 CU 6 開始的 Microsoft Entra 整合式驗證。
  • 4 指定從 SQL Server 2022 CU 6 開始的 Microsoft Entra 權杖驗證。

下一步

若要深入了解，請檢閱 SQL Server 複寫和適用於 SQL Server 的 Microsoft Entra 驗證