I originally wrote about a version of this issue a while ago on Straight Path Solutions’ blog. That post covered a detailed breakdown of what happened, lessons learned, and other nuances and recovery touchpoints that had to be addressed. This is going to be a shorter, standalone retelling that is going to focus on just the cert side of things since that is the part worth emphasizing and repeating.

A few months after a clean migration, replication between the nodes in the global primary AG in a Distributed Availability Group (DAG) just stopped. There were no new deployments, config changes, or specific warning signs prior to this. Windows patching had occurred, the servers rebooted like they usually would, and the AG never came back together.

I know for myself, one of the most frustrating phrases that gives me the sense of impending doom is “nothing changed, why is this breaking?!”. That’s exactly what was going on here.

A Bit Of A Backstory

So, to frame this up, the client was using a DAG to migrate from an on-prem environment living in Domain A, to an Azure environment living in Domain B. Since the two environments were in different domains, there was no trust set up between them. This is important to note because it prevents you from using WINDOWS NEGOTIATE which is what your AGs natively use by default to communicate from replica to replica.

To get around this, you have to use certificate-based auth instead, which makes sense to use here as it is the only way for cross-domain AG endpoints to use to communicate without a trust set up. The migration went smoothly and the AG / new DAG up in Azure was syncing so everyone moved on.

Unintentionally Set Up To Fail

Here’s what was going to bite us eventually:

AUTHENTICATION = CERTIFICATE, NEGOTIATE <cert_name>

The Hadr_endpoint on the Global Primary in Azure was set to CERTIFICATE, NEGOTIATE. The reason for this is because they had to use CERTIFICATE to sync the data from on-prem domain to Azure domain for migration, and NEGOTIATE for Azure to Azure replica comms.

On the surface, this looks like a safety net setup. Try Windows auth and if that doesn’t work fail back to cert based auth. That’s the mentality that would eventually cause problems for us.

Months later, the cert expired. Not long after that, patching rolled out and the replicas rebooted. That is when the endpoint refused to talk anymore. The syncing stopped between the replicas in their Global Primary AG.

A Necessary But Avoidable Problem

The whole purpose of the Hadr_endpoint being set up like this in the first place was a necessary path. The certificate part of the auth was to get synchronization from on-prem AG to Azure AG from a DAG comms perspective, and the Windows Negotiate part was for local replica comms within the forwarder AG at the time living in Azure.

In a DAG, comms flows like this at a high level:

Global primary replica gets a write -> forwards that write to the local secondary replicas -> as well as forwards that write to the primary replica for the Forwarder AG. From that point it’s the Forwarder AG’s responsibility to forward that write to the secondary replicas within that Forwarder AG.

So, more simply put, they needed both to get data synchronization from on-prem to Azure AND within Azure.

After the migration was done, and the on-prem AG went away, a step was missed to take away the cert based auth portion of the primary replica’s Hadr_endpoint in Azure when it was no longer needed. When the cert expired, it bombed the whole comms ability. Think of it as an AND logic gate. If you’re having two methods of auth listed, method 1 AND method 2 have to work for comms to work. If one fails, it all fails.

TL;DR For The On-Call DBA

• If you’re migrating across domains with no trust in place, CERTIFICATE auth on the HADR endpoint is a necessary choice.
• If you’re using both CERTIFICATE, NEGOTIATE together on any of your replicas, it is not a method of fallback. BOTH need to work for comms to work.
• After a migration, if CERTIFICATE auth is no longer needed please remember to alter the Hadr_endpoint and remove that cert dependency, and make the Hadr_endpoint NEGOTIATE only or you’re going to have to maintain it and make sure your cert doesn’t expire.
• If you’re staying on cert auth long term, track the cert’s expiry date and rotate it before it expires.
• Check sys.certificates and sys.database_mirroring_endpoints (or the AG endpoint equivalent) now, before your next patch Tuesday finds it for you.

Please read the blog linked at the beginning of this post for a detailed technical explanation that has valuable queries to use which you can leverage to check and change your Hadr_endpoint communication.