Problem

When setting up a SQLHA cluster and one of the nodes will not join the Availability Group and you get an error message -


Error:41158,failed to join the availability group

or from the SQL Log file -

Database Mirroring login attempt by user 'ME\SQLHA2$.' failed with error: 'Connection handshake failed. The login 'ME\SQLHA2$' does not have CONNECT permission on the endpoint. State 84.'. 


Resolution

  1. Open SQL Server Configuration Manager.
  2. Click on SQL Server Services.
  3. Change the Log On As to the service account (me\admin)


Also verify the port 5022 is open and not blocked.  SQLHA uses this port.


https://dba.stackexchange.com/questions/247441/database-mirroring-protocol-tcp-ports-used-one-default-one-dynamic#:~:text=When%20an%20application%20establishes%20a,is%20sent%20TO%20port%205022.


Note:  This is what fixed the issue for me.  If there are additional fixes, please update this KB with the fix that worked for you.

       

Cause

In certain cases permissions or blocked ports can cause issues with SQLHA