In SQL Server Always On Availability Group environment endpoint connection issue is a common problem and it needs to be resolved by giving endpoint permissions.
Are you getting the following messages in the SQL Server error logs?
2022-01-05 09:41:29.360 Logon Database Mirroring login attempt by user '<Account name>.' failed with error: 'Connection handshake failed. The login '<Account name>' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: <IP address>]
2022-01-05 09:41:29.520 Logon Database Mirroring login attempt by user '<Account name>.' failed with error: 'Connection handshake failed. The login '<Account name>' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: <IP address>]
2022-01-05 09:41:34.520 Logon Database Mirroring login attempt by user '<Account name>.' failed with error: 'Connection handshake failed. The login '<Account name>' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: <IP address>]
2022-01-05 09:42:09.380 Logon Database Mirroring login attempt by user '<Account name>.' failed with error: 'Connection handshake failed. The login '<Account name>' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: <IP address>]
If yes, your secondary databases will not be in a healthy synchronization state in SQL Server Always On Availability Group.
To resolve this error, Check Hadr_endpoint available in sys.endponts. It should show status as STARTED in both primary and secondary replicas.
Use master
go
select * from sys.endpoints
where name='Hadr_endpoint'
If the endpoints are not started, you can provide CONNECT permission to Hadr_endpoint in the secondary node of Always ON using the below script.
GRANT CONNECT on ENDPOINT::Hadr_Endpoint TO [<Account Name>];Note: Replace the <Account name> with the SQL Server service account.
After the Always-On nodes are in sync and able to connect, databases are in a healthy state.