SQL SERVER – Database Mirroring Login Attempt Failed With Error: ‘Connection Handshake Failed. There is No Compatible Encryption Algorithm. State 22

One of my client was trying to convert database mirroring to the AlwaysOn availability group. They had a mirroring configure where the principal server was going to become a primary replica. When they attempted to join the AG, it was failing with an error. The error messages we were getting in the wizard are not helpful. I went ahead and looked into SQL Server ERRORLOG and found below the messages

The database Mirroring login attempt failed with error: ‘Connection handshake failed. There is no compatible encryption algorithm. State 22.’. [CLIENT: ]

We scripted the endpoint from both replicas using below method.

Primary

CREATE ENDPOINT [Mirroring] 
      STATE=STARTED
      AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
      FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO 

Secondary

CREATE 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 

You can also use below to find an algorithm on both primary and secondary.

SELECT encryption_algorithm_desc FROM sys.database_mirroring_endpoints

Based on the error message, you should see different value on both ends. In my case I had one server with RC4 and one server with AES. They must match for AlwaysOn data movement to work.

SOLUTION/WORKAROUND

The solution in this situation is to make sure that we have same value passed in ALGORITHM parameter on both the sides. To fix the issue, we dropped and recreated endpoint with the same script to make sure it’s the same algorithm.

Reference: Pinal Dave (http://blog.SQLAuthority.com)