SQL Server AlwaysOn Availability Group Endpoint Encryption Algorithm Compatibility Error

By: Ahmad Yaseen | Comments | Related: > Availability Groups

Problem

SQL Server AlwaysOn Availability Groups are one of the best high availability and disaster recovery solutions which were introduced in SQL Server 2012. Configuring the AlwaysOn Availability Group can be done easily by following a few steps using the New Availability Group wizard. Last week, we faced an issue while creating an AlwaysOn Availability Group. An endpoint encryption algorithm compatibility error prevented us from completing the wizard. What is this issue and how can we overcome this issue?

Solution

Suppose we need to configure a SQL Server AlwaysOn Availability Group between two servers; a primary node and a secondary replica. We go through the steps in the New Availability Group wizard and in the Validation step we get the below error also shown in the screenshot.

The configuration of endpoint data encryption is incompatible between replicas and the endpoint connection will fail. The current configuration is listed below with following order: the name of replica, role of replica, encryption and algorithm.

We can conclude from this error, that the encryption algorithms used in the replicas endpoints are not compatible with each other. The Primary is using RC4 and the Secondary is using AES. The mirroring endpoint algorithms should be the same between the replicas in order to add these replicas to the AlwaysOn Availability Group and communicate successfully with each other.

SQL Server Encryption Algorithm Options

There are four encryption algorithm options in SQL Server that you can choose from when creating the mirroring endpoints:

AES - which is the default algorithm in SQL Server 2016.
RC4 - which is the default algorithm in SQL Server 2014.
AES RC4 - where the endpoints will negotiate for which algorithm to use, but it will give preference to the AES algorithm.
RC4 AES - where the endpoints will negotiate for which algorithm to use, but it will give preference to the RC4 algorithm.

Modifying the SQL Server Database Mirroring Endpoint

In order to overcome this issue, you need to make the mirroring endpoints algorithm the same for the two replicas. This can be achieved by changing the encryption algorithm of one of the replicas to be the same as the second one. In our case we will change the algorithm on the secondary replica to RC4.

The easiest way to write the ALTER ENDPOINT statement is to script the endpoint’s creation then modify it as required. The mirroring endpoint can be found under the Server Objects node of the Object explorer using SQL Server Management Studio as follows:

Once the creation script is generated, we need to change the CREATE statement to an ALTER statement since the endpoint already exists. Also, we will change the endpoint algorithm type to RC4 as shown below:

USE [master]
GO
ALTER ENDPOINT [Hadr_endpoint] 
 STATE=STARTED
 AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
 FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM RC4)
GO

Completing SQL Server Availability Group Setup

After applying the script, close the wizard and run it again. You will find that the validation error will not show and you can proceed with the AlwaysOn Availability Group creation process.

Next Steps

Read more SQL Server AlwaysOn Availability Tips.

About the author

Ahmad Yaseen is a SQL Server DBA with a bachelor’s degree in computer engineering as well as .NET development experience.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips