Unable to join secondary replica to the availability group by SQL Server error 47106

Question

We were trying to create availability group with 3 synchronous replicas, where we couldn't give the names specified in /etc/hosts as node specifiers in the create availability group in the primary replica. We got the following error:

Msg 35237, Level 16, State 1, Server , Line 1

None of the specified replicas for availability group maps to the instance of SQL Server to which you are connected. Reenter the command, specifying this server instance to host one of the replicas. This replica will be the initial primary replica.

but based on the following link: What is causing this error: None of the specified replicas for availability group ag1 maps to the instance of SQL Server to which you are connected

I changed the node specifier to the hostname and that worked, but then I couldn't connect the secondary replicas to the availability group.

ALTER AVAILABILITY GROUP [ag1] JOIN WITH (CLUSTER_TYPE = EXTERNAL);

I have received the following error:

Msg 47106, Level 16, State 3, Server '', Line 1

Cannot join availability group 'ag1'. Download configuration timeout. Please check primary configuration, network connectivity and firewall setup, then retry the operation.

Msg 41158, Level 16, State 3, Server '', Line 1

Failed to join local availability replica to availability group 'ag1'. The operation encountered SQL Server error 47106 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.

Answer 1

Had the same error (47106) - the reason was that SQL Servers (SQL01 and SQL02) were running under local accounts (e.g. NT Service\MSSQLServer) and therefore granting connect permissions were required.

On SQL01:

CREATE LOGIN [DOMAIN\SQL02$] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\SQL02$];

On SQL02:

CREATE LOGIN [DOMAIN\SQL01$] FROM WINDOWS
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DOMAIN\SQL01$];

Answer 2

I solved the issue. For the error 35237, the hostname that was changed should be of max 15 characters for the node specifier while creating the availability group or else it won't recognize the remaining characters