SQL SERVER – Always On Availability Group Error 35250: The Connection to the Primary Replica is Not Active

Along with my performance consulting, I have also assisted many clients in fixing some quick issues. You can refer for various consulting services which I provide. During my consulting with various clients about Always On deployment, one of the commonly reported errors as below.  (I have changed the AG Name, Node names to be more generic). Below came when we used the wizard to create an availability group. Let us learn how to fix error related to primary replica.

Joining database on secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
——————————
ADDITIONAL INFORMATION:
Failed to join the database ‘DB’ to the availability group ‘AG1’ on the availability replica ‘NODE2’. (Microsoft.SqlServer.Smo)
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)

This error also comes when we try to join the replica to an availability group using below T-SQL.

ALTER DATABASE DB SET HADR AVAILABILITY GROUP = AG1

And the error is below:

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed.

MY CHECKLIST

Here is the little checklist which I have made after referring various sites on the internet.

1. Ensure that AlwaysOn endpoint, generally named as Hadr_endpoint, is not blocked by firewalls (Default port 5022). To confirm this, we should do a Telnet test from each node to another node. Sometimes you need to install “Telnet Client” on the server using “Server Manager” > Add features. If you are not able to reach to port than make sure.
   1. Endpoints are already created and listening. Check ERRORLOG for this.
   2. A firewall is allowing the port. Check Firewall for this.
2. Make sure we can resolve FDQN with IP and vice versa. Generally, a ping test can be done to achieve this.
3. Check the hosts file in “C:\Windows\System32\drivers\etc” to make sure there are no incorrect entries.
4. Make sure that the startup account of the primary server is added to all secondary server’s and Startup accounts of all secondary servers are added to primary servers. In short, startup account of each replica to be added to other replicas. I have generally seen a client using one service account across all servers *cough* *cough*
5. If the service account of SQL Server is “NT Service\” or LocalSystem account, then ensure system account (Domain\ReplicaName$) of each replica is added to other replicas.  Generally, people add it as SysAdmin *cough* *cough* but it is better to give lesser permission like below

GRANT CONNECT ON ENDPOINT::[Hadr_endpoint] TO [Domain\node1$]

In my client’s case we were getting below error in ERROLROG

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

This is covered in # 5 in above checklist. So, we created a login for other replicas in each replica and granted connect permission to fix the error. Microsoft documentation also states the same: “If any server instances that are hosting the availability replicas for an availability group run as different accounts, the login each account must be created by a master on the other server instance. Then, that login must be granted CONNECT permissions to connect to the database mirroring endpoint of that server instance.”

I hope that my checklist would help you in narrowing down the cause of the error and you should be able to fix it.

Reference: Pinal Dave (https://blog.sqlauthority.com)