4

While performing a rolling upgrade of an Availability Group from SQL Server 2016 CU1 to 2016 SP1 we noticed an issue. Immediately after we updated the secondary replica replication stopped working for that node and the SQL logs indicated there was an issue with the Database Mirroring login:

Database Mirroring login attempt by user 'DOMAIN\SERVICEACCOUNT.' failed with error: 'Connection handshake failed. The handshake verification failed. State 36.'. [CLIENT: 10.1.2.3]

What causes this issue and how can we resolve it?

Improve this question

2 Answers 2

Reset to default
4

This is an issue with the encryption method used on the database mirroring endpoint. In SQL Server 2014 the default encryption type was RC4, but in SQL Server 2016 the new default is AES and it looks like the CU2 update may have removed support for RC4.

To detect and fix this you can run the following on your nodes:

-- Looking at encryption_algorithm - RC4 is deprecated in 2016 CU2+
Select * From sys.database_mirroring_endpoints

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 AES RC4)
 GO

After which replication should start working again and you can continue with the rolling upgrade.

Improve this answer
1
  • 1
    Greg you legend! life saver! This bizarrely didn't happen when we tested using Developer edition but hit this issue in our production environment. We had this issue with basic mirroring.
    – davey
    Sep 14, 2017 at 7:31
1

In my case Netlogon service was is disabled mode on secondary server hence we received this error. Once we enabled the same database came into sync.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.