Partilhar via

Configurar a replicação com autenticação da Microsoft Entra - SQL Server ativado pelo Azure Arc

Aplica-se a: SQL Server 2022 (16.x)

Este artigo fornece etapas para configurar a replicação Transacional e de Instantâneo usando a autenticação com o Microsoft Entra ID (anteriormente Azure Active Directory) para SQL Server habilitado para Azure Arc.

Visão geral

O suporte à autenticação Microsoft Entra para replicação foi introduzido na Atualização Cumulativa 6 para SQL Server 2022 e disponibilizado em geral na Atualização Cumulativa 12. Quando você usa a autenticação do Microsoft Entra para replicação, a única etapa diferente é a primeira etapa. Especificamente, crie um login do Microsoft Entra e conceda permissões sysadmin.

Depois disso, utilize o login do Microsoft Entra nos procedimentos armazenados de replicação para configurar a replicação Transacional ou de Instantâneo como faria normalmente.

Observação

A partir do SQL Server 2022 CU 6, desative a autenticação Microsoft Entra para replicação utilizando o sinalizador de rastreio de sessão 11561.

Pré-requisitos

Para configurar a replicação com a autenticação do Microsoft Entra, você deve atender aos seguintes pré-requisitos:

  • Ter o SQL Server 2022 habilitado pelo Azure-Arc a partir da Atualização Cumulativa 6.
  • Configurada a autenticação do Microsoft Entra para cada servidor na topologia de replicação. Consulte o Tutorial: Configurar a autenticação do Microsoft Entra para o SQL Server para saber mais.
  • Uma versão suportada do SQL Server Management Studio (SSMS).
  • O usuário que se conecta ao editor e ao assinante é um membro da função de servidor fixa sysadmin .
  • A conexão deve ser criptografada usando um certificado de uma Autoridade de Certificação (CA) confiável ou um certificado autoassinado.
    • Se um certificado autoassinado for usado, ele deverá ser importado para a máquina cliente e instalado na lista Certificados Confiáveis para que o cliente confie no SQL Server. Esse requisito não pode ser ignorado selecionando a opção Certificado de servidor confiável no SQL Server Management Studio (SSMS), pois ele não funciona com replicação.

Limitações

A configuração da replicação com a autenticação do Microsoft Entra atualmente tem as seguintes limitações:

  • Atualmente, só é possível configurar a replicação usando o Transact-SQL (T-SQL) e os procedimentos armazenados de replicação, o Assistente de Replicação no SSMS v19.1 ou superior ou o Azure Data Studio. Atualmente, não é possível configurar a replicação usando objetos de replicação RMO ou outras linguagens de linha de comando.
  • Cada servidor na topologia de replicação deve ter pelo menos o SQL Server 2022 CU 6. Não há suporte para versões anteriores do SQL Server.

Criar login SQL a partir do Microsoft Entra ID

Crie o login do Microsoft Entra e conceda-lhe a sysadmin função.

Para criar o login do Microsoft Entra e atribuí-lo como um sysadmin, use o seguinte comando Transact-SQL (T-SQL):

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

Por exemplo, para adicionar o nome de login do newuser@tenant.com, use este comando:

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

Criar banco de dados de distribuição

Use sp_adddistributiondb para criar o banco de dados de distribuição.

Segue-se um exemplo de script para criar a sua base de dados de distribuição no seu Distribuidor:

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

O exemplo a seguir cria a tabela UIProperties na base de dados Distribution e define a propriedade SnapshotFolder de modo que o agente de snapshot saiba onde gravar snapshots de replicação.

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'

O script a seguir configura o Publicador para usar o banco de dados do Distribuidor e define o logon do usuário do AD, juntamente com uma senha a ser usada para replicação:

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'

Habilitar replicação

Use sp_replicationdboption para habilitar a replicação no banco de dados do Publisher, como testdb, como no exemplo a seguir:

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

Adicionar a publicação

Use sp_addpublication para adicionar a publicação.

Você pode configurar a replicação transacional ou por instantâneo.

Siga estas etapas para criar uma replicação transacional.

Primeiro, configure o agente do leitor de log:

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

Em seguida, crie a publicação transacional:

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'

Em seguida, crie o Snapshot Agent e armazene os arquivos de snapshot para o Publisher usando o login do Microsoft Entra para o @publisher_login e definindo uma senha para o Publisher:

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

Por fim, adicione o artigo TestPub à publicação:

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'

Criar Subscrição

Utilize sp_addsubscription para adicionar o seu Subscritor e, em seguida, utilize sp_addpushsubscription_agent no Publicador para criar uma subscrição push ou sp_addpullsubscription_agent no Subscritor para criar uma subscrição pull. Use o login do Microsoft Entra para o @subscriber_login.

O script de exemplo a seguir adiciona a assinatura:

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

O script de exemplo a seguir adiciona um agente de assinatura por push no Publisher:

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'

Procedimentos armazenados de replicação

Os seguintes parâmetros nesses procedimentos armazenados de replicação foram modificados no CU 6 para o SQL Server 2022 para oferecer suporte à autenticação Microsoft Entra para replicação.

Os valores a seguir definem os modos de segurança para esses procedimentos armazenados:

  • 0 especifica a Autenticação do SQL Server.
  • 1 especifica a Autenticação do Windows.
  • 2 especifica a autenticação por senha do Microsoft Entra a partir de SQL Server 2022 CU 6.
  • 3 especifica a autenticação integrada do Microsoft Entra começando a partir do SQL Server 2022 CU 6.
  • 4 especifica a autenticação de token do Microsoft Entra a começar no SQL Server 2022 CU 6.

Próximos passos

Para saber mais, consulte Replicação no SQL Server e autenticação do SQL Server com Microsoft Entra

  • Last updated on