As a SQL Server consultant, I often come across various issues that clients face while working with SQL Server. In this blog post, I would like to share a recent engagement where a client had deployed a standalone SQL Server on two machines, created a Windows cluster between them, and wanted to utilize the AlwaysOn availability group feature. The specific issue they encountered was the primary replica not being active.

The error message they received was:

Joining database on secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)

Failed to join the database ‘SQLAuthority’ to the availability group ‘ProdAG’ on the availability replica ‘SRV2’. (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)

Even when they tried to run the JOIN command via T-SQL, they encountered the same error:

Msg 35250, Level 16, State 7, Line 1

The connection to the primary replica is not active. The command cannot be processed.

Upon further investigation, they checked the ERRORLOG on both replicas and found the following messages:

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

The solution to this issue was to grant the necessary permissions to the service account. They ran the following command:

GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [DomainName\svc_sql];

After executing the above command, the issue was resolved, and they were able to successfully join the database to the availability group.

It is important to note that this error occurs when the service account is unable to connect to the SQL Server on the database mirroring or AlwaysOn AG port. Granting the necessary permissions to the service account resolves the issue.

If you encounter a similar error where the primary replica is not active, make sure to check the permissions of the service account and grant the necessary CONNECT permission on the endpoint.

I hope this blog post helps you troubleshoot and resolve any issues related to the primary replica not being active in SQL Server.