As of SQL Server 2022 Cumulative Update (CU) 6, configuring replication using authentication with Microsoft Entra ID (formerly Azure Active Directory) is currently in public preview.
Previously, Windows authentication and SQL authentication were the only supported methods to validate identities when configuring replication in SQL server. The introduction of Microsoft Entra authentication as a security option for replication leverages existing Microsoft Entra integration with SQL Server powered by Azure Arc to enable cloud-based identity management for on-premises SQL Server instances. Nothing has changed with replication functionality as the configuration simply adds a new authentication method. This latest feature improvement extends the authentication modes for a user connecting to the replication Publisher and Subscriber to support the following Microsoft Entra authentication types:
- Password
- Service principal
- Integrated
The following replication types can be configured with Microsoft Entra authentication in Arc-enabled SQL Server 2022 CU 6 and higher:
- Transactional replication
- Snapshot replication
- Merge replication
Benefits of using Microsoft Entra authentication for replication
Enabling Microsoft Entra authentication for replication extends the availability of Microsoft Entra ID authentication to on-premises SQL environments that use replication. Customers can take advantage of Microsoft Entra ID’s centralized cloud-based identity management and streamline Microsoft Entra adoption across their workflows beyond the initial user login. Fully integrating with Microsoft Entra ID for all workloads improves security by allowing customers to use multi-factor authentication (MFA) for logins and get new Microsoft Entra security functionality.
Configure Microsoft Entra authentication for replication with Azure Arc-enabled SQL Server 2022 CU 6
To enable Microsoft Entra authentication for replication, you need the following:
- SQL Server 2022 CU 6 or higher onboarded to Azure Arc
- Every server in the replication topology must be CU 6 or higher
- Versions lower than SQL Server 2022 CU 6 are not supported
- SQL Server configured with Microsoft Entra authentication
- Required for every server in the replication topology
- Microsoft Entra user with the sysadmin fixed server role
- Azure Data Studio or SQL Server Management Studio (SSMS) 19.1
- Encrypted connection
- The connection must be encrypted with a certificate from a trusted Certificate Authority (CA) or with a self-signed certificate
- Microsoft Entra supported replication can be configured either through replication stored procedures using T-SQL or the Replication Wizard in SQL Server Management Studio (SSMS) and Azure Data Studio. It’s not currently possible to configure replication using RMO replication objects or other command line languages.
Although Azure Active Directory has been rebranded to Microsoft Entra ID, the UI in SSMS is taking a bit longer to catch up so, when enabling Replication using the SSMS wizard, you’ll see the option to run the process under a Windows or Azure Active Directory account when you configure agent security, such as the following screenshots from SSMS 19.1:
Next steps
Install SQL Server 2022 CU 6 or update your current SQL Server 2022 instance to CU 6, onboard your server to Azure Arc, and configure Microsoft Entra authentication. Share your experiences with us and let us know in the comments.
Related resources
Learn more:
- For the complete guide to enable replication in SQL Server 2022 CU 6 using Microsoft Entra ID follow Configure replication with Microsoft Entra authentication for Azure Arc-enabled SQL Server - SQL Ser...
- Learn more about Azure Arc for SQL Server by visiting Azure Arc-enabled SQL Server - SQL Server | Microsoft Learn
- For more information on Microsoft Entra authentication for SQL Server see Microsoft Entra authentication for SQL Server overview - SQL Server | Microsoft Learn.
- For more information on logical Replication in SQL Server see SQL Server Replication - SQL Server | Microsoft Learn.