When you configure SQL Server AlwaysOn Availability Group from management studio, it may fail with below error while joining secondary replica to the availability group. Today, While I was preparing some demo on AlwaysOn for the community event, I encountered the same issue. So, I thought to blog the solution here so that it can reach to a broader audience.

Error Encountered:

{
TITLE: Microsoft SQL Server Management Studio
——————————
Failed to join the instance ‘SQL2K16N3’ to the availability group ‘AlwaysOn_Testing’. (Microsoft.SqlServer.Management.SDK.TaskForms)

——————————
ADDITIONAL INFORMATION:
Failed to join local availability replica to availability group ‘AlwaysOn_Testing’. The operation encountered SQL Server error 41106 and has been rolled back. Check the SQL Server error log for more details. When the cause of the error has been resolved, retry the ALTER AVAILABILITY GROUP JOIN command. (Microsoft SQL Server, Error: 41158)
}

Error found in SQL Server ErrorLog:

When I checked the SQL Server Errorlog on the primary replica, I found the Errorlog filled with following error messages

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

Resolution:

To resolve the issue, I followed the below steps;

• Ensure always on endpoint (Hadr_endpoint) are not blocked by firewall (default port 5022).

• Make sure startup account of the primary server is added to all secondary server’s and Startup accounts of all secondary servers are added to the primary servers.

• If log-on account of SQL Server is “Nt service\” or local system account

then ensure system account (Domainname\systemname$) of each replica is added to other replicas and grant connect on AlwaysOn endpoints created on each replicas for startup account of other replica servers.

To create machine account and grant connect permission, run the below script on the primary replica and also on other secondary replicas;

use [master]
GO
CREATE LOGIN [TESTDOMAIN\SQL2K16N3$] FROM WINDOWS
GO
GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [TESTDOMAIN\SQL2K16N3$]
GO

• After creating machine account and granting connection permission, the above error was resolved, and my secondary replica (SQL2k16N3) now looks clean without any errors as shown below;

 So, when we are adding a new server as a secondary replica, we have to make sure that the SQL Server is running using service account so that adding replica process can create machine account as well as it can provide grant connect permission on the Hadr_endpoint.

Thank you for reading the blog!