January 29, 2018 at 1:35 pm
Hi All,
I am planing to change the sql server service account in two node always-on setup. Need to know the steps that needs to be taken which is a recommended way. Also, what are the minimum permission required to give this new service account on the domain. I know we need to give certain permissions or manually register the SPN in the domain in the standalone SQL Server.
Do we need to do any thing different in always on setup.
Many Thanks,
B
January 30, 2018 at 7:46 am
Well since no one answered yet, let me explain my plan so far.
I have two nodes always on AG
node1
node2
change the owner of the Always on endpoint owner is its the service account to sa
1 - make node1 fail over manual and Async ( data loss is OK while doing this)
2 - make note2 fail over manual and Async
3 - suspend the data movement on node1.
4 - suspend the data move on node2.
go to configuration manager to node2 ( secondary) and change the service account
start data move
fail over the Ag to secondary , node2 is the primary now
suspend the data move again
change the service account in the configurations manager in node1 ( secondary)
start the data move again
---------------------
I have tested these in my environment and the setup works. My issue is what permission if any I need to give to the new service account. my old service account is the domain admin ( I know bad practice).
when I change the service account. trying to connect from client tool is giving me SSP handshake error. when I change the service account to a new service account that has domain admin permission than it works so there must be some permission issue some where.
Can any one help?
.
February 1, 2018 at 4:19 am
qur7 - Monday, January 29, 2018 1:35 PMI am planing to change the sql server service account in two node always-on setup. Need to know the steps that needs to be taken which is a recommended way.
Have you employed a listener for your Availability Group?
qur7 - Monday, January 29, 2018 1:35 PM
Also, what are the minimum permission required to give this new service account on the domain. I know we need to give certain permissions or manually register the SPN in the domain in the standalone SQL Server.Do we need to do any thing different in always on setup.
Many Thanks,
B
The account should be granted AD permissions "Read ServicePrincipalName" and "Write ServicePrincipalName", this will ensure that the SPNs can be automatically registered\deregistered.
Who owns the database mirroring endpoints?
qur7 - Tuesday, January 30, 2018 7:46 AMWell since no one answered yet, let me explain my plan so far.
Patience is a virtue, advice here is free and we have day jobs
qur7 - Tuesday, January 30, 2018 7:46 AM]
I have two nodes always on AGnode1
node2
change the owner of the Always on endpoint owner is its the service account to sa
Back to my question above, who owns the endpoint originally?
qur7 - Tuesday, January 30, 2018 7:46 AM]
1 - make node1 fail over manual and Async ( data loss is OK while doing this)
2 - make note2 fail over manual and Async
3 - suspend the data movement on node1.
4 - suspend the data move on node2.
go to configuration manager to node2 ( secondary) and change the service account
start data move
fail over the Ag to secondary , node2 is the primary now
suspend the data move again
change the service account in the configurations manager in node1 ( secondary)
start the data move again
---------------------
Set the replicas to synch during any maintenance or improvements, not asynch?
When the group is synchronised, On all secondary replicas change the account via SQL Server Config Manager. You'll need to restart the services for those replicas.
When synchronised failover the group to a secondary, then repeat for the remaining replica.
qur7 - Tuesday, January 30, 2018 7:46 AM]
My issue is what permission if any I need to give to the new service account. my old service account is the domain admin ( I know bad practice).
Revoke this immediately.
Do not apply any permissions to the account other than what has been mentioned above, all other required permissions are granted by the SQL Server Config Manager during the account change.
qur7 - Tuesday, January 30, 2018 7:46 AM]
when I change the service account. trying to connect from client tool is giving me SSP handshake error. when I change the service account to a new service account that has domain admin permission than it works so there must be some permission issue some where.
Can any one help?
Do you have multiple versions of sql deployed on this server?
.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 1, 2018 at 6:33 am
Hi Perry,
Thanks for the response, no way I was implying that I need answer right away. Was just adding comments to keep the thread going.
To answer the questions.
- we are using listener.
- the new account doesn't have the permissions mentioned above. I will work on it now.
- end points were originally owned by the first service account. I have changed it to SA now.
- Will change from Async to Sync and change the service account.
- No , only one SQL server is installed on the nodes.
Many Thanks for the help.
February 1, 2018 at 8:16 am
qur7 - Thursday, February 1, 2018 6:33 AMHi Perry,Thanks for the response, no way I was implying that I need answer right away. Was just adding comments to keep the thread going.
To answer the questions.- we are using listener.
- the new account doesn't have the permissions mentioned above. I will work on it now.
- end points were originally owned by the first service account. I have changed it to SA now.
- Will change from Async to Sync and change the service account.
- No , only one SQL server is installed on the nodes.Many Thanks for the help.
When using a listener all replicas must run as the same user account for the sql server service otherwise SPN failures will occur.
Have you tried running repair on the sql server client tools?
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
February 2, 2018 at 6:48 am
I was finally able to resolve the hand-shake error. The issue was related to register the SPN for the new service account manually fore both server on the always on SQL setup. Also before doing the register need to remove the old SPN entry with the old service account from the domain. I used Adsiedit.msc utility to do that on the DC. Need domain admin permission to do that. Either have a account to do that or ask the domain/system administrator to do this for you.
Thanks for all the help.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply