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.

Solarwinds

SQL SERVER - Database Mirroring Login Attempt Failed With Error: 'Connection Handshake Failed. There is No Compatible Encryption Algorithm. State 22 AO-Mirror-01-800x453

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)

Solarwinds
, , , ,
Previous Post
SQL SERVER – Ghost Cleanup Getting Hung and Blocking Checkpoint Process. What’s Wrong?
Next Post
SQL Azure Database – Msg 40197, Level 20 – The Service has Encountered an Error Processing Your Request. Please Try Again. Error Code 40549

Related Posts

Leave a Reply

Menu