Configuration of SQL2012 AlwaysOn High Availability Group fails with error 35250

I just started testing the “AlwaysOn High Availability Groups” function of Microsoft SQL2012. While I tried to configure a group the configuration wizard always finished with the following error:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Joining database on secondary replica resulted in an error.
(Microsoft.SqlServer.Management.HadrTasks)
------------------------------
ADDITIONAL INFORMATION:
Failed to join the database 'XenApp' to the availability group
'sql2012ha01' on the availability replica 'SQL02'.
(Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.
SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText
&LinkId=20476
------------------------------
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
The connection to the primary replica is not active.  The command cannot
be processed. (Microsoft SQL Server, Error: 35250)

If you then check the created availability group the primary node is online and the secondary node is offline.

When I now tried to “Join” the Secondary Server into the availability group the following message was displayed:

TITLE: Microsoft SQL Server Management Studio
------------------------------
Failed to join the instance 'SQL02' to the availability group
'sql2012ha01'. (Microsoft.SqlServer.Management.SDK.TaskForms)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+
Server&ProdVer=11.0.2100.60+((SQL11_RTM).120210-1917+)&EvtSrc=Microsoft.
SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText
&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)
------------------------------
Failed to join local availability replica to availability group
'sql2012ha01'. 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)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%
20Server&ProdVer=11.00.2100&EvtSrc=MSSQLServer&EvtID=41158&LinkId=20476

In the Microsoft Technet I found the following solutions:

http://technet.microsoft.com/en-us/library/ff878308.aspx#JoinDbFails

But that didn’t help me to solve my problem.

After looking a little bit around I found a hint that a special Port in the Windows Firewall has to be opened for the Availability Group Replication. After creating the Group you can easily check which port is used (default: 5022). You just need to open the properties of the working node in the Availability Group. The replication port is displayed at the end of the “Endpoint URL”.

Interestingly I still got the same error after opening the Port on all Nodes in the Windows Firewall. But this time the following error was shown in the Application Event Log of the Primary Server.

Database Mirroring login attempt by user 'DOMAIN\SQL02$.' failed
with error: 'Connection handshake failed. The login 'DOMAIN\SQL02$'
does not have CONNECT permission on the endpoint. State 84.'.
[CLIENT: 192.168.XX.XX]

The reason for this is quite simple: For the replication the SQL-Server connects to the Replication Servers. These connection is made with the account under which the SQL-Service is running. If this isn’t changed on all Servers to the same Active-Directory User (during the Installation) normally an account like “NT Service\MSSQLSERVER” is used. If this account is configured for the Service the connection is made with the “Computer Account” of each Node.

To solve this problem you need to add the Computer Account of each other node to the SQL Security Settings. The accounts need the right “Connect on Endpoint”. In my Lab I configured the other Computer-Accounts on every SQL Server and gave them Sysadmin rights – in a productive environment I suggest to give the accounts only the necessary rights (Connect on Endpoint). To add the other Computer Accounts just go to the Security Settings and add a Login using Windows Authentication. The account is named “DOMAIN\COMPUTERNAME$”.

To give the user only the required rights (Connect on Endpoint) you need the following script:

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

You have to modify the script with the Computer names of every other node.

After the accounts are added – just create your Availability Group again – this time everything should work without any problems