HADR_ENDPOINT owner and CONNECT rights
Question
Wednesday, September 18, 2019 8:36 PM
All,
On all our SQLServers, we are changing the SQLServer service account from [ADDomain\sqlservA] to [ADDomain\sqlservB] – note that both of them have sysadmin rights on each of the SQLServers. We are changing them on Servers with AlwaysOn Availability Groups (AAG) as well and the question is pertaining to these AAG based SQLServers
The Hadr_Endpoint one some have the DBA Login [ADDomain\rgn] (because iAAG was configured by the DBA with ADDomain\rgn) by as the owner with CONNECT privilege to [ADDomain\sqlservA]. (which is the SQLServer Service Account). Later, we realized it is best to configure AAGs using SA login and one these servers the Hadr_Endpoint is owned by sa with CONNECT privilege to [ADDomain\sqlservA].
We want to make SA as the owner of Hadr_Endpoint on all our SQLServers. Would this change alone be enough? Since we will be changing the SQLServer Service account to [ADDomain\sqlservB] should we also add the CONNECT privilege. But given that [ADDomain\sqlservB] has sysadmin rights do we need to explicitly grant CONNECT privilege to the end point.
Thanks,
rgn
All replies (4)
Thursday, September 19, 2019 9:50 AM ✅Answered
Yes. We have granted sysadmin role to the SQLserver Service Accounts [ADDomain\sqlservA] and [ADDomain\sqlservB].
After replacing [ADDomain\sqlservA] with [ADDomain\sqlservB] we will decommission [ADDomain\sqlservA].
We will be using gMSA accounts for all the standalone instances and only use [ADDomain\sqlservB] for FCI and AAG based SQLServers.
Normally people do not do this and this might not be configured as good security practice you can create a service account and start SQl Server with this account using SQL Server configuration manager doing this will take care of all the rights needed for SQL Server to run. When you configure AG using SSMS wizard and you are using domain account the wizard automatically gives connect permission on endpoints to the login. If you are changing simply change from SQL Server configuration manager and make sure connect permission is their like Cathy pointed out
Cheers,
Shashank
Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it
Wednesday, September 18, 2019 9:23 PM
When you say that the service account has sysadmin rights, do you mean that you have explicitly added the account to the sysadmin role?
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Wednesday, September 18, 2019 10:12 PM
Yes. We have granted sysadmin role to the SQLserver Service Accounts [ADDomain\sqlservA] and [ADDomain\sqlservB].
After replacing [ADDomain\sqlservA] with [ADDomain\sqlservB] we will decommission [ADDomain\sqlservA].
We will be using gMSA accounts for all the standalone instances and only use [ADDomain\sqlservB] for FCI and AAG based SQLServers.
Thursday, September 19, 2019 9:42 AM
Hi grajee,
From your description, did you want to change SQL server service account from SSCM? And use below code to grant CONNECT privilege to the end point for ADDomain\sqlservB? If i misunderstood, please let me know.
GRANT CONNECT ON ENDPOINT::[Hadr_Endpoint] TO [ADDomain\sqlservB]
GO
Best regards,
Cathy
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com