SQL Server Database Mirroring Login Attempt Failed with Error ‘Connection Handshake Failed’

Updated: Jan 7

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'

SQL Server Always On Availability Group endpoints

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.


GRANT CONNECT on ENDPOINT Permission

After the Always-On nodes are in sync and able to connect, databases are in a healthy state.

54 views0 comments

Recent Posts

See All