Issue:
I recently configured an SQL AG between 4 nodes and as you know whenever you configure new AG it creates new hadr_endpoints on each node to communicate with other nodes. Whoever is configuring the AG his/her account set as owner of hadr_endpoint, but as per standard practice we should configure 'SA' login as owner of endpoints so even if in future that person leaves organization and respective account get removed, our AG set up will not get affected.
So to follow above mentioned standard practice I changed owner of hadr_endpoint to 'SA' on all AG nodes using below command.
ALTER AUTHORIZATION ON ENDPOINT::hadr_endpoint TO sa;
After changing the owner AG was working fine I dint observe any issue. But on next I did failover of AG from one node to other node then I observed AG was in sync with previous primary node but other two nodes went out of sync and status in AG health dashboard for those 2 nodes was showing Not Synchronized. Of course in this case I would not be able to configure failover on these 2 nodes.
Analysis:
When I checked error logs on faulty unsynchronized nodes, I found below logs.
A connection timeout has occurred while attempting to establish a connection to availability replica 'XXXXXX' with id [XXXXXXX]. Either a networking or firewall issue exists, or the endpoint address provided for the rep
Database Mirroring login attempt by user 'XXXX\XXXXX.' failed with error: 'Connection handshake failed. The login 'xxxxx\xxxxx' does not have CONNECT permission on the endpoint. State 84.'. [SERVER: X.X.X.X]
This error occurs when SQL server service account does not have connect permission on HADR endpoint. When we configure AG this permission get granted itself but when I changed the owner of hadr_endpoint it got revoked.
Resolution:
To fix issue I executed below command on every node participated in AG.
GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO [<SQL server service account>]
Conclusion:
Whenever you change owner of HADR endpoint, connect permission of SQL service account endpoint gets revoked. So you should grant it again. So to do same I have shared below script which will take care of both. You can execute it whenever you want to change owner of hadr_endpoint to 'SA'.
|
select 'Pre-Check' as [Check] select E.*, p.name as Owner from sys.endpoints E left join sys.server_principals P on E.principal_id=p.principal_id where E.type=4 SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name FROM sys.server_permissions p INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id INNER JOIN sys.server_principals sp ON p.grantee_principal_id
= sp.principal_id WHERE class = '105' and ep.name='Hadr_endpoint' ; declare @serviceaccount nvarchar (512) declare @endpointname as sysname declare @sql as varchar(1000) select @endpointname= name from sys.endpoints where type=4 and principal_id <>1 if @endpointname is not null begin set @sql = 'ALTER AUTHORIZATION ON ENDPOINT::' + @endpointname + ' TO sa;' --select
(@sql) Exec (@sql) end select @serviceaccount = service_account from sys.dm_server_services where servicename = 'SQL Server (MSSQLSERVER)' set @sql= 'GRANT CONNECT ON ENDPOINT::Hadr_endpoint TO ['+@serviceaccount+']' --select @sql exec (@sql) select 'Post-Check' as [Check] select E.*, p.name as Owner from sys.endpoints E left join sys.server_principals P on E.principal_id=p.principal_id where E.type=4 SELECT ep.endpoint_id, p.class_desc, p.permission_name, ep.name, sp.name FROM sys.server_permissions p INNER JOIN sys.endpoints ep ON p.major_id = ep.endpoint_id INNER JOIN sys.server_principals sp ON p.grantee_principal_id
= sp.principal_id WHERE class = '105' and ep.name='Hadr_endpoint' ; |
No comments:
Post a Comment