Entra Authentication for Replication in Arc-enabled SQL Server 2022 CU 12 in General Availability

Microsoft

Nov 02, 2023

As of SQL Server 2022 Cumulative Update (CU) 12, configuring replication using authentication with Microsoft Entra ID (formerly Azure Active Directory) is generally available.

Microsoft Entra authentication support for replication was introduced in Cumulative Update 6 for SQL Server 2022 and made generally available in Cumulative Update 12.

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 12:

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 12

To enable Microsoft Entra authentication for replication, you need the following:

SQL Server 2022 CU 12 onboarded to Azure Arc
- Every server in the replication topology must be using 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 12 or update your current SQL Server 2022 instance to CU 12, onboard your server to Azure Arc, and configure Microsoft Entra authentication. Share your experiences with us and let us know in the comments.