High Availability Error 35250 and “An error occurred while receiving data: '10054(An existing connection was forcibly closed by the remote host.)

Question

I am facing problem in configuring High Availability, this is my 30-40 time configuration of Always On Availability Groups with central subscriber replication topology and I have never been stuck like this with SQL Server error 35250. I searched a lot but I was unsuccessful in fixing it.

I got this error:

An error occurred while receiving data:
'10054 (An existing connection was forcibly closed by the remote host.)'.

I am using Windows Server 2012 R2 with two node server on SQL Server 2014 (64 bit).

Windows login account and SQL Server service account are both running under a domain account (abc\Administrator).

hadr_endpoint (port-5022) are started with window login permission.

My Always On endpoint ([Hadr_endpoint]) are not blocked by firewall because all firewalls are stopped.

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

Note I am not installed any hotfixes.

I create a domain admin account (on DC) like domainname.in\Administrator and give the all permission to this user (under Member Of tab) and login with domainname\Administrator to the both server. Login in SQL Server with same domainname\Administrator, same service account domainname\Administrator. Note - I created user domainname.in\Administrator but login with domainname\Administrator without '.in'. Is it the issue?

Endpoint URL (please check with ".in" and service account without ".in" (is this the issue?)

My network team not using any kind of network capture and we stopped firewall. We are not using any security software. We are using cyberoam and we also pin all the three server into same switch.

CREATE ENDPOINT [Hadr_endpoint] 
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)

Service Broker script:

CREATE ENDPOINT [BrokerEndpoint] 
    STATE=STARTED
    AS TCP (LISTENER_PORT = 4022, LISTENER_IP = ALL)
    FOR SERVICE_BROKER (MESSAGE_FORWARDING = ENABLED
, MESSAGE_FORWARD_SIZE = 10
, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

I am not using RC4 algorithm for endpoint or service broker. I also increased session timeout time with 60 seconds for secondary replica. I also see the .dag file and it show "failure_condition_level 3". I know probably this is network issue but my network person unable to solve it.

Is there any user mapping issue in SQL Server? Any database is compulsory add to my SQL Server login account?

Answer 1

After too many tries and reading lots of blogs about SQL Server error 35250, I can't solve this error.

Finally, I tried IP address in Endpoint URL and I successfully configured Always On availability group. Problem in FQDN name.

To use an IP address in Endpoint URL: First check below services are started. If not, then start on all replicas and DC:

1. DNS Client 
2. Function Discovery Resource Publication
3. SSDP Discover
4. UPnP Device Host

Above services help to discover all nodes in the network. Then check into network for your all node are shown over there. If not then follow these steps: Go to Network and sharing center; click on change advanced sharing settings; and turn on network discovery.

TCP://10.10.10.530:5022

Answer 2

I was getting the same error:

An error occurred while receiving data:

'10054 (An existing connection was forcibly closed by the remote host.)'.

In my case, there were two things that had to be taken care of:

1. the shared folder you create for full backup have the login account permissions.

2. go to sql server configuration manager and make sure you are not use the default user do it in both servers.

   

then go to the 2nd node SQL server Management studio => open availability group database =>right click on database => join to availability group.

It should run successfully.