Wondering how to resolve SQL Server Error 35250? We can help you.

At Bobcares, we offer solutions for every query, big and small, as a part of our Microsoft SQL Server Support Services.

Let’s take a look at how our Support Team is ready to help customers with SQL Server Error 35250.

How to resolve SQL Server Error 35250?

Usually, this error occurs whenever there is a communication issue between endpoints of both replicas or any replica unable to connect to the endpoints.

The error message is given below:

Failed to Join the database ‘DBName’ to the availability group ‘AOAG-Name’ on the availability replica ‘Availability Replica Name’

If we use T-SQL, the error message shown as given below:

Msg 35250, Level 16, State 7, Line 1
The connection to the primary replica is not active. The command cannot be processed. (microsoft sql server, error: 35250)

Today, let us see the steps followed by our Support Techs to resolve it.

By default, AlwaysOn configures the database mirroring endpoints to use port 5022.

However, we can use any other configure port for the database mirroring endpoints in your environment.

Query sys.tcp_endpoints on each replica to confirm which port is being use:

select name, type, port from sys.tcp_endpoints where type_desc=’database_mirroring’
go

By default inbound traffic is block in Windows firewall.

Port 5022 is use by the primary and secondary replicas for synchronization and communication purposes.

Traffic must allow inbound on this port.

• Endpoint is not created or started

Firstly, ensure the mirroring endpoints are create and start on the primary and the secondary replicas.

To detect if the endpoints are create, query for database_mirroring endpoints at the primary and the secondary.

:Connect SQLNODE1
select name, state_desc, port from sys.tcp_endpoints where type_desc=’DATABASE_MIRRORING’
go
:Connect SQLNODE2
select name, state_desc, port from sys.tcp_endpoints where type_desc=’DATABASE_MIRRORING’
go

If we find an endpoint does not exist, create it:

:Connect SQLNODE1
create endpoint [Hadr_endpoint]
state=started
as tcp (listener_port = 5022, listener_ip = all)
for database_mirroring (role = all, authentication = windows negotiate, encryption = required algorithm aes)
go

Then, to detect if the endpoints are start, query them at the primary and the secondary.

:Connect SQLNODE1
select name, state_desc, port FROM sys.tcp_endpoints where name=’hadr_endpoint’
go
:Connect SQLNODE2
select name, state_desc, port from sys.tcp_endpoints where name=’hadr_endpoint’
go

If we find an endpoint is not running, start it:

:Connect SQLNODE1
alter endpoint [Hadr_endpoint] state = started

• Endpoint permissions

If database mirroring endpoints are configure to use Windows authentication, ensure that the SQL Server instances hosting our availability replicas run with a SQL Server startup account are domain accounts.

The New Availability Group wizard catches this misconfiguration, and reports it.

Then if we proceed by clicking Yes and complete the wizard, it will fail during the ‘Joining to availability group at .’

If we are attempting to create an availability group using transact-sql, this configuration problem will not detect and command ALTER DATABASE…SET HADR AVAILABILITY GROUP… will fail with 35250.

• SQL Server is not listening on port 5022

If SQL Server is unable to listen on port 5022, the New Availability Group wizard and the transact-sql command ALTER DATABASE…SET HADR AVAILABILITY GROUP… will fail with 35250.

To determine if SQL Server is listening on port 5022, review the SQL Server error log. We should find the following message(s) in the SQL Server error log:

2022-03-03 08:52:25.47 spid23s Server is listening on [ ‘any’ 5022].
2022-03-03 08:52:25.47 spid23s Server is listening on [ ‘any’ 5022].

SQL Server may not able to listen on port 5022 if another application is already listening on the port.

If we find that SQL Server is not listening on port 5022 because it is already use, run netstat -a to determine what application is using the port.

• SQL Server service accounts not added to SQL Server Instances

There should Grant CONNECT permission on Always On endpoint.

We can obtain the endpoints list using the command:

select * from sys.endpoints;

We can grant connect permission on endpoint using the command given below:

GRANT CONNECT on ENDPOINT::Endpoint_Name TO [login_name];

where Endpoint_Name is the name of the endpoint and the login_name is the service account that is used to run SQL Server services.

As the account does not exist on secondary replica, it will result in the error:

Msg 15151, Level 16, State 1, line 1
Cannot find the login ‘login_name’, because it does not exist or you do not have permission.

We have to create this service account on secondary replica as well or where it was missing on any of the replica.

After that, re-add the database to the AOAG configuration.

[Looking for a solution to another query? We are just a click away.]

Conclusion

To sum up, our skilled Support Engineers at Bobcares demonstrate how to resolve SQL Server Error 35250.