Condividi tramite

Configurare la replica con l'autenticazione Microsoft Entra - SQL Server abilitato da Azure Arc

  • Articolo

Si applica a: SQL Server 2022 (16.x)

Questo articolo illustra i passaggi per configurare la replica transazionale e snapshot usando l'autenticazione con l'ID Microsoft Entra (in precedenza Azure Active Directory) per SQL Server abilitato per Azure-Arc.

Panoramica

Il supporto dell'autenticazione di Microsoft Entra per la replica è stato introdotto nell'aggiornamento cumulativo 6 per SQL Server 2022 e reso disponibile a livello generale nell'aggiornamento cumulativo 12. Quando utilizzi l'autenticazione Microsoft Entra per la replica, l'unico passaggio diverso è il primo passaggio. In particolare, creare un account di accesso di Microsoft Entra e concedere autorizzazioni sysadmin.

After that, use the Microsoft Entra login in the replication stored procedures to configure Transactional or Snapshot replication as you normally would.

Nota

A partire da SQL Server 2022 CU 6, disabilitare l'autenticazione di Microsoft Entra per la replica usando il flag di traccia della sessione 11561.

Prerequisiti

Per configurare la replica con l'autenticazione di Microsoft Entra, è necessario soddisfare i prerequisiti seguenti:

Limiti

La configurazione della replica con l'autenticazione di Microsoft Entra presenta attualmente le limitazioni seguenti:

  • Attualmente è possibile configurare la replica solo usando Transact-SQL (T-SQL) e le stored procedure di replica, la Replica guidata in SSMS v19.1 o versione successiva o Azure Data Studio. Non è attualmente possibile configurare la replica usando oggetti di replica RMO o altri linguaggi della riga di comando.
  • Ogni server nella topologia di replica deve trovarsi almeno in SQL Server 2022 CU 6. Le versioni precedenti di SQL Server non sono supportate.

Creare l'account di accesso SQL da Microsoft Entra ID

Creare l'account di accesso di Microsoft Entra e concedergli il ruolo sysadmin.

Per creare l'account di accesso di Microsoft Entra e assegnarlo come sysadmin, usare il comando Transact-SQL (T-SQL) seguente:

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

Ad esempio, per aggiungere il nome di accesso per newuser@tenant.com, usare questo comando:

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

Creare il database di distribuzione

Usare sp_adddistributiondb per creare il database di distribuzione.

Di seguito è riportato uno script di esempio per creare il database di distribuzione nel server di distribuzione:

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

Nell'esempio seguente viene creata la tabella UIProperties nel database di distribuzione e viene impostata la proprietà SnapshotFolder in modo che l'agente di snapshot sappia dove scrivere gli snapshot di replica:

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'

Il seguente script configura il server di pubblicazione per l'uso del database di distribuzione e definisce l'account di accesso utente di Active Directory, insieme a una password da usare per la replica:

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'

Abilitare la replica

Usare sp_replicationdboption per abilitare la replica nel database del server di pubblicazione, ad esempio testdb, come nell'esempio seguente:

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

Aggiungere la pubblicazione

Usare sp_addpublication per aggiungere la pubblicazione.

È possibile configurare la replica transazionale o snapshot.

Per creare una replica transazionale, attenersi ai passaggi seguenti.

Configurare prima di tutto l'agente di lettura 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

Quindi, creare la pubblicazione transazionale:

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'

Successivamente, creare l’agente di snapshot e archiviare i file di snapshot per il server di pubblicazione usando l'account di accesso Microsoft Entra per @publisher_login e definendo una password per il server di pubblicazione:

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

Infine, aggiungere l'articolo TestPub alla pubblicazione:

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'

Creare la sottoscrizione

Usare sp_addsubscription per aggiungere l’abbonato e usare quindi sp_addpushsubscription_agent nel server di pubblicazione per creare una sottoscrizione push o sp_addpullsubscription_agent nell’abbonato per creare una sottoscrizione pull. Usare l'account di accesso di Microsoft Entra per @subscriber_login.

Il seguente script di esempio aggiunge la sottoscrizione:

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

Il seguente script di esempio aggiunge un agente di sottoscrizione push nel server di pubblicazione:

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'

Stored procedure per la replica

I seguenti parametri in queste stored procedure di replica sono stati modificati in CU 6 per SQL Server 2022 per supportare l'autenticazione di Microsoft Entra per la replica:

I valori seguenti definiscono le modalità protette per queste stored procedure:

  • 0 specifica l'autenticazione di SQL Server.
  • 1 specifica l'autenticazione Windows.
  • 2 specifica l'autenticazione della password di Microsoft Entra a partire da SQL Server 2022 CU 6.
  • 3 specifica l'autenticazione integrata di Microsoft Entra a partire da SQL Server 2022 CU 6.
  • 4 specifica l'autenticazione del token Microsoft Entra a partire da SQL Server 2022 CU 6.

Passaggi successivi

Per altre informazioni, vedere autenticazione di replica di SQL Server e Microsoft Entra per SQL Server