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)
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.
it’s says –> Cannot grant, deny, or revoke permissions to sa, dbo, entity owner, information_schema, sys, or yourself.
then your issue is something different. What is the error message?
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.