AlwaysOn – Connection Handshake Failure

— by Ginger Keys

While doing some work on a test environment recently I realized the AlwaysOn Group was failing. (On a production system I would have had several Alerts set to notify me of any issues). The dashboard on my test instance showed that the secondary replica was not synchronizing and from clicking on the blue warning links it actually showed that the secondary was not connected:

Blog_20171221_1

Blog_20171221_2

Blog_20171221_3

I confirmed that both the primary and secondary instances were up and running, but the secondary Availability Replica and Databases were not connected to the AlwaysOn Group:

Blog_20171221_4

Problem

The AlwaysOn Health Events were consistent with the SQL Server Logs. I discovered that the Windows Server Failover Cluster had been down (but was back online), and that the AlwaysOn Group had failed over and back. But the culprit to my secondary replica and databases being disconnected was an endpoint issue. I found in my SQL Server logs the service account running AlwaysOn had for some reason lost its permissions to connect to the endpoint:

Blog_20171221_5

Message

Database Mirroring login attempt by user ‘Domain\user.’ failed with error: ‘Connection handshake failed. The login ‘Domain\user’ does not have CONNECT permission on the endpoint. State 84.’. [CLIENT: 10.0.0.0]

Solution

Thankfully the solution to this issue was pretty simple. Run the following script on both the primary and secondary instances to grant the service account running AlwaysOn connect permission:

GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [Domain\user]

GO

This immediately corrected the problem, and the secondary replica and databases were connected to the AlwaysOn Group without any further action.

Unfortunately the cause of issues on a SQL Server are not always apparent after the fact, so that is why it is important to be proactive and set up robust alerts and notifications. For more information on setting up alerting and monitoring for your AlwaysOn Group click here https://blog.sqlrx.com/2015/08/27/alwayson-monitoring-and-alerting/ .

For more information about blog posts, concepts and definitions, further explanations, or questions you may have…please contact us at SQLRx@sqlrx.com. We will be happy to help! Leave a comment and feel free to track back to us. Visit us at www.sqlrx.com!

 

 

 

 

 

Post a comment or leave a trackback: Trackback URL.

Trackbacks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: