Configurer la réplication avec l’authentification Microsoft Entra – SQL Server activé par Azure Arc

  • Article

S’applique à : SQL Server 2022 (16.x)

Cet article décrit les étapes de configuration de la réplication transactionnelle et de capture instantanée à l’aide de l’authentification avec Microsoft Entra ID (anciennement Azure Active Directory) pour SQL Server activé par Azure Arc.

Vue d’ensemble

La prise en charge de l’authentification Microsoft Entra pour la réplication a été introduite dans La mise à jour cumulative 6 pour SQL Server 2022 et mise à la disposition générale dans la mise à jour cumulative 12. Lorsque vous utilisez l’authentification Microsoft Entra pour la réplication, la seule étape différente est la première. Plus précisément, créez une connexion Microsoft Entra et accordez des autorisations d’administrateur système.

Ensuite, utilisez la connexion Microsoft Entra dans les procédures stockées de réplication pour configurer la réplication transactionnelle ou de capture instantanée comme vous le feriez normalement.

Remarque

À compter de SQL Server 2022 CU 6, désactivez l’authentification Microsoft Entra pour la réplication à l’aide de l’indicateur de trace de session 11561.

Prérequis

Pour configurer la réplication avec l’authentification Microsoft Entra, vous devez respecter les conditions préalables suivantes :

  • Disposer de SQL Server 2022 activé par Azure-Arc à partir de la mise à jour cumulative 6.
  • Avoir configuré l’authentification Microsoft Entra pour chaque serveur dans la topologie de réplication. Examinez : Tutoriel : Configurer l’authentification Microsoft Entra pour SQL Server pour en savoir plus.
  • SQL Server Management Studio (SSMS) version 19.1 ou ultérieure ou Azure Data Studio
  • L’utilisateur qui se connecte au serveur de publication et à l’abonné est membre du rôle serveur fixe de l’administrateur système.
  • La connexion doit être chiffrée à l’aide d’un certificat d’une autorité de confiance approuvée ou d’un certificat auto-signé.
    • Si un certificat auto-signé est utilisé, il doit être importé sur l’ordinateur client et installé dans la liste des certificats approuvés pour que le client approuve SQL Server. Cette exigence ne peut pas être ignorée en sélectionnant l’option Faire confiance au certificat de serveur dans SQL Server Management Studio (SSMS), car elle ne fonctionne pas avec la réplication.

Limites

La configuration de votre réplication avec l’authentification Microsoft Entra présente actuellement les limites suivantes :

  • Il est actuellement possible de configurer la réplication à l’aide de Transact-SQL (T-SQL) et des procédures stockées de réplication, de l’assistant de réplication dans SSMS version 19.1 ou ultérieure, ou d’Azure Data Studio. Il n’est actuellement pas possible de configurer la réplication à l’aide d’objets de réplication RMO ou d’autres langages de ligne de commande.
  • Chaque serveur de la topologie de réplication doit être sur au moins SQL Server 2022 CU 6. Versions précédentes de SQL Server non prises en charge.

Créer une connexion SQL à partir de Microsoft Entra ID

Créez la connexion Microsoft Entra et octroyez-lui le rôle sysadmin.

Pour créer la connexion Microsoft Entra et l’affecter en tant que sysadmin, utilisez la commande Transact-SQL (T-SQL) suivante :

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

Par exemple, pour ajouter le nom de connexion pour newuser@tenant.com, utilisez cette commande :

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

Créer une base de données de distribution

Utilisez sp_adddistributiondb pour créer la base de données de distribution.

Voici un exemple de script pour créer votre base de données de distribution sur votre serveur de distribution :

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

L’exemple suivant crée la table UIProperties dans la base de données de distribution et définit la propriété SnapshotFolder afin que l’agent d’instantané sache où écrire des instantanés de réplication :

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'

Le script suivant configure le serveur de publication pour utiliser la base de données du serveur de distribution et définit la connexion utilisateur AD, ainsi qu’un mot de passe à utiliser pour la réplication :

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'

Activer la réplication

Utilisez sp_replicationdboption pour activer la réplication sur votre base de données du serveur de publication, comme testdb, comme l’exemple suivant :

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

Ajouter la publication

Utilisez sp_addpublication pour ajouter la publication.

Vous pouvez configurer la réplication transactionnelle ou de capture instantanée.

Pour créer une réplication transactionnelle, procédez comme suit :

Tout d’abord, configurez l’agent de lecture du journal :

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

Ensuite, créez la publication transactionnelle.

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'

Créez ensuite l’agent d’instantané et stockez les fichiers instantanés pour le serveur de publication à l’aide de la connexion Microsoft Entra pour le @publisher_login et définissez un mot de passe pour le serveur de publication :

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

Enfin, ajoutez l’article TestPub à la publication :

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'

Créer un abonnement

Utilisez sp_addsubscription pour ajouter votre abonné, puis utilisez sp_addpushsubscription_agent sur le serveur de publication pour créer un abonnement par émission de données ou sp_addpullsubscription_agent sur l’abonné pour créer un abonnement par extraction. Utilisez la connexion Microsoft Entra pour le @subscriber_login.

L’exemple de script suivant ajoute l’abonnement :

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

L’exemple de script suivant ajoute un agent d’abonnement par émission de données au niveau du serveur de publication :

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'

Procédures stockées de réplication

Les paramètres suivants dans ces procédures stockées de réplication ont été modifiés dans CU 6 pour SQL Server 2022 afin de prendre en charge l’authentification Microsoft Entra pour la réplication :

Les valeurs suivantes définissent les modes de sécurité pour ces procédures stockées :

  • 0 spécifie l’authentification SQL Server.
  • 1 spécifie l’authentification Windows.
  • 2 spécifie l’authentification par mot de passe Microsoft Entra à partir de SQL Server 2022 CU 6.
  • 3 spécifie l’authentification intégrée à Microsoft Entra à partir de SQL Server 2022 CU 6.
  • 4 spécifie l’authentification par jeton Microsoft Entra à partir de SQL Server 2022 CU 6.

Étapes suivantes

Pour en savoir plus, consulter Réplication SQL Server et Authentification Microsoft Entra pour SQL Server