SQL Server 2008 no Compatible Encryption Algorithm

If you are getting Windows SQL Server Event ID 1474 ‘handshake failed’ errors, it may be because of a previous database Mirror or replication that has not been permanently deleted. For SQL Server 2008 R2, the Mirror or endpoint itself may be hanging around and may need to be deleted. This is true even if the Mirror was deleted off the actual Database Properties.

Event ID 1474 tells the story:

Application Error, ID = 1474, “Database mirroring connection error 5 ‘Connection Handshake failed. There is no compatible encryption algorithm. State 22.’ for ‘TCP:// -My-Mirror-DB-ServerHost:5023’.

Some stray parts of the Mirror stay in place. This can happen if there was a previous Mirror of the same database that maybe got corrupted, or maybe disk space ran out and a new Mirror is needed etc. When trying to create a replacement Mirror, some of the options may be grayed out so they cannot be edited.

Again, what is really happening is that some of the old Mirror settings are sticking around and they are causing problems. One part has encryption enabled and the other has it not enabled. They both need to be at the same setting or the ‘handshake’ fails!

Example: The Principal for the Mirror may have the encryption not selected, but the Mirror database setting has it set to encrypt [or vice versa] BUT it is grayed out and does not allow an edit. Encryption needs to be the same on each point. This was initially the cause of past ‘handshake’ failures, thus my Mirror would not start up.

It is best to start fresh, but no need to do a full backup / restore again, as that is not the issue here. If this is a Production environment, as always be careful. It is best to test this out first, but if you are certain the Mirror in question needs to be deleted, then try this [besides, it does actually delete the data, only the Mirror settings].

In SSMS go to Server Objects, Endpoints, Database Mirroring, and delete the old one. Note: This has nothing to do with System Endpoints so leave those alone. Delete the old Database Mirror for good, and start again. Again, this may need to be done even if you believe you had already deleted the DB Properties Mirror settings and thus stopped the previous Mirror. Now a fresh DB Mirror can be created or at the very least, the settings for the Mirror will not be grayed out and this can be a starting point for further troubleshooting.

 



One thought on “SQL Server 2008 no Compatible Encryption Algorithm”

Leave a Reply

Your email address will not be published. Required fields are marked *

Riguy CAPT-CHA * { Time limit is exhausted. Please reload CAPTCHA.

Not Just Any WordPress Themed I.T. Site!