SQL SERVER – FIX: Msg 35250, Level 16, State 7 – The Connection to the Primary Replica is Not Active. The Command Cannot be Processed

Along with my performance consulting, I also assist many clients in fixing some quick issue. You can refer for various consulting services which I provide. This blog is an outcome of one such engagement where the client has deployed standalone SQL Server on two machines, created windows cluster between them and wanted to then use AlwaysOn availability group feature. This blog is about Primary Replica not being active.

Here is the text of the message (for search engines)

Joining database on secondary replica resulted in an error. (Microsoft.SqlServer.Management.HadrTasks)
——————————
ADDITIONAL INFORMATION:
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)
——————————

We get the same error even if we try to run JOIN command via T-SQL.


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

We checked ERRORLOG on both the replicas and found below the 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]

SOLUTION/WORKAROUND

The error message is clear that the service account is not able to connect to SQL Server on database mirroring / AlwaysOn AG port. To fix the issue we need to run below command

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

Once above was done, the issue was resolved and we were able to join the database to AG.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

AlwaysOn, SQL Error Messages, SQL High Availability, SQL Scripts, SQL Server
Previous Post
SQL SERVER – Fix: The EXECUTE Permission was Denied on the Object ‘xp_cmdshell’, Database ‘mssqlsystemresource’,schema ‘sys’.
Next Post
SQL SERVER – Login Failed – Server is in Script Upgrade Mode – Deleting Collection Set “Utility Information”

Related Posts

5 Comments. Leave new

  • Sir,
    i have database backup from my client system. i want to know in which edition (express/enterprise/standard) of sql server they have used this database.
    cant able to contact client. Pls help me to find out.

    Reply
  • it’s says –> Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    Reply
  • parag kambli
    July 4, 2018 2:25 am

    I was facing same error on my VM instance.
    I was using domain admin account with local admin permissions.

    Got same op–Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.

    Solution–

    Create inbound rule and outbound rule for hadr port and always on listener port
    or simply disable firewall if it is test environment.

    Reply

Leave a Reply

Menu
Exit mobile version