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.
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.
Ahmad has a Bachelor’s Degree in Computer Engineering from the University of Jordan and five years of experience working as a SQL DBA, gaining valuable knowledge of database structures, practices, principles and theories. His experience also includes.NET development, working with database applications, scripting and creating SQL queries and views. His personal abilities include having very strong communication and interpersonal skills, the ability to prioritize and to make good sound decisions that benefit the company. He has experience in upgrading, configuring, securing, tuning and monitoring SQL Servers since SQL Server 2005. This includes SQL Server performance tuning, SQL Server resource governor management, SQL Server maintenance plans, SQL Server data collection (Reports) analyzing and SQL databases design, developing, indexing and query optimization. In addition, he is familiar with installing and configuring SSRS, SSIS and SSAS. When it comes to disaster recovery and high availability, he has a solid foundation in SQL backup and recovery scenarios, mirroring, replication, log shipping, SQL clustering and AlwaysOn technology.
- MSSQLTips Awards: Author Contender – 2016-2017 | Trendsetter (25+ tips) – 2016 | Rookie Contender – 2015
