SQL SERVER – Always On Replica Disconnected After Changing SQL Server Service Account

In my lab environment, I was testing a script to change service account and it worked fine. I had Always On configured on this SQL Server and soon I realized that after changing the SQL Server service account, the secondary replica went into a disconnected state.

SQL SERVER - Always On Replica Disconnected After Changing SQL Server Service Account ao-sec-disc-01

We could see the below errors in the Primary Replica SQL Errorlog

<DateTime> Logon  Database Mirroring login attempt by user ‘MyDC\SQLAccount’ failed with error: ‘Connection handshake failed. The login ‘MyDC\SQLAccount’ does not have CONNECT permission on the endpoint. State 84.’.  [CLIENT: 192.168.x.x]

If we change the service account back to the old one, everything goes back to normal. There are no issues. So, it seemed like that there were some issues with the new account we were using and apparently AlwaysOn AG did not like that account. The error also says that the account does not have “connect” permission to the endpoints. We ran the below query to check who all have got permissions on the AlwaysOn Endpoint — Hadr_endpoint

SELECT e.name AS mirror_endpoint_name
	,s.name AS login_name
	,p.permission_name
	,p.state_desc AS permission_state
	,e.state_desc endpoint_state
FROM sys.server_permissions p
INNER JOIN sys.endpoints e ON p.major_id = e.endpoint_id
INNER JOIN sys.server_principals s ON p.grantee_principal_id = s.principal_id
WHERE p.class_desc = 'ENDPOINT'
	AND e.type_desc = 'DATABASE_MIRRORING'

The output looked like below:

SQL SERVER - Always On Replica Disconnected After Changing SQL Server Service Account ao-sec-disc-02

We had changed the SQL service account to — ‘MyDC\SQLAccount’. The old one was ‘MyDC\SQLSvcAccount’

Looks like we were missing the CONNECT GRANT on the EndPoint permission here. We performed the following steps that resolved the issue.

WORKAROUND/SOLUTION

  1. Created login of the newly added service account on both replicas.
USE [master]
GO
CREATE LOGIN [MyDC\SQLAccount] FROM WINDOWS WITH DEFAULT_DATABASE=[master]
GO 
  1. Granted connect permission the endpoints on both replicas.
GRANT CONNECT ON ENDPOINT::hadr_endpoint TO [MyDC\SQLAccount]
GO
  1. Stopped the endpoints on both the replicas.
ALTER ENDPOINT hadr_endpoint STATE=STOPPED
  1. Started endpoints on both the replicas.
ALTER ENDPOINT hadr_endpoint STATE=STARTED

After making the above changes replicas were back in the connected state. We tested the failovers and it worked great. Have you faced a similar issue with AlwaysOn AG? Please share your experience via comments.

Reference: Pinal Dave (https://blog.sqlauthority.com

, , , ,
Previous Post
SQL SERVER – Slow SQL Server 2016 Installation in Cluster: RunRemoteDiscoveryAction
Next Post
SQL SERVER – All the Features not Listed While Installing SQL Server

Related Posts

5 Comments. Leave new

  • it works after recreation of new groups then remove database old group and added in new group

    Reply
  • Oleksandr Kosariev
    March 7, 2020 2:30 am

    I owe you beer. Let me know when you in Stockholm next time.

    Reply
  • Solved my issue, Thank you very much! I did a change of the service account and did not think it needed to be in the database. Until I read your post, I noticed the old account was indeed there. As soon as I added it with the proper security, bang! Databases appeared on the secondary.

    Reply

Leave a Reply

Menu