3

In What are the implications of changing the SQL Server Service Logon Account? it mentions that when replacing the SQL Server service account, the service will need to be restarted.

But there is more to it than that. According to Configure Windows Service Accounts and Permissions, the change of logon account should be done through the SQL Server Configuration Manager.

  • Always use SQL Server tools such as SQL Server Configuration Manager to change the account used by the SQL Server Database Engine or SQL Server Agent services, or to change the password for the account. In addition to changing the account name, SQL Server Configuration Manager performs additional configuration such as updating the Windows local security store which protects the service master key for the Database Engine. Other tools such as the Windows Services Control Manager can change the account name but do not change all the required settings.
    • For Analysis Services instances that you deploy in a SharePoint farm, always use SharePoint Central Administration to change the server accounts for Power Pivot service applications and the Analysis Services service. Associated settings and permissions are updated to use the new account information when you use Central Administration.
    • To change Reporting Services options, use the Reporting Services Configuration Tool.

My current environment has some 15 servers between production and development and they all use the same domain account to run the SQL Server services and a different domain account for the SQL Server Agent.

I want to replace this with 15 different pairs of active directory accounts (SQL Server service and Agent).

Questions

  1. What can possibly go wrong? Any gotchas?
  2. Can any part of this process be automated?
1

If you have Kerberos in the mix you may run into SPN issues. – Jeff A

Depending on your domain functional level (need to be 2012 I believe), you could use Group managed service accountsBob Klimes

Any manually created SPNs will need to be deleted and recreated. Any SPNs automatically created by the service on startup will be removed on shutdown, so that shouldn't be a concern. I'll second Bob Klimes' suggestion that you should go to Group Managed Service accounts, if possible. They're absolutely fantastic. Finally, you may need to configure service account permissions within each instance depending on your current configuration. Generally these accounts can be minimally provisioned, but it'll depend on your current config. – John Eisbrener

PowerShell can be used to automateBob Klimes


Community Wiki answer compiled from question comments. Community wiki posts have been donated to the community in hopes that others will edit them to keep them up to date, to add useful information, and generally improve their quality.

0

Any jobs you have like backups or snapshots which write to different locations might fail if you do not give permission to the new account on the drive folder (ie network share). Using a different account for each instance can cause some issues and may not necessarily be considered best practice. You could lump them all into a group account which share the same privileges, but like John said, you may run into SPN (Cannot Generate SSPI Context) errors where you can't connect remotely from another instance.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.