Skip to content

Configuration of SQL2012 AlwaysOn High Availability Group fails with error 35250

8. May 2012

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:
sql01

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.

sql02

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

sql03
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”.

sql04

sql05

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.

sql06
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$”.

sql07

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 Smile

From → SQL

18 Comments
  1. Chad permalink

    Brilliant, just what I needed. Thanks

  2. cameron permalink

    Had same problem, your fix worked, thanks.

  3. Anthony permalink

    Have the same problem, but am using the same domain service account to run SQL Server service rather than computer accounts. I have added inbound rules for 5022 in Windows Firewall, to no avail. 😦

  4. i.am.technophile permalink

    This fixed my issue as well. THANK YOU!!!

  5. Thanks for the tip. So why does it need this if all services, connections, et. al. are under a domain admin? (For lab setup only, I understand that this isn’t protocol).

  6. Thank you very very very very much !!! Worked perfect !

  7. Hey there, You have done an excellent job. I will certainly digg it and personally suggest to my
    friends. I am confident they will be benefited from this site.

  8. Navish permalink

    How can I get to SQL Alwayson Properties window?

    • makarovm permalink

      This window is adding a new rule to the firewall. By SQL Alwayson is irrelevant. Rule name is misleading.

      • Could you please explain more detailed what you mean? I don’t get it

  9. Tosin permalink

    For me,
    I forgot to add the domain user I was using on the DAG to the Secondary node.
    I added it and everything went on smoothly

  10. Congratulations for the Post, solve my problem… 🙂

  11. Hany Helmy permalink

    Thanx a lot.

  12. ash permalink

    Hello Jan,
    I am glad this article showed on my Google search. It is clearly helped me understand the problem when I got the error while trying to setup Availability Group.
    thank you very much!
    Ash

Trackbacks & Pingbacks

  1. Link Resource # 54 : May 11 – May 17 « Dactylonomy of Web Resource

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: