SQL Server AlwaysOn Availability Group Endpoint Encryption Algorithm Compatibility Error


By:   |   Updated: 2016-05-20   |   Comments   |   Related: More > 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.

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:

Script SQL Server AlwaysOn Endpoint

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.

SQL Server AlwaysOn Validation
Next Steps


Last Updated: 2016-05-20


get scripts

next tip button



About the author
MSSQLTips author Ahmad Yaseen Ahmad Yaseen is a SQL Server DBA with a bachelorís degree in computer engineering as well as .NET development experience.

View all my tips
Related Resources




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download


get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools