Interview question: Why should you change a SQL Server service account with SQL Server Configuration Manager rather than the Windows Services applet (services.msc)?
Answer: SQL Server Configuration Manager changes more than the Windows service logon field. It coordinates SQL Server-specific permissions and protection for the Database Engine service master key. Microsoft advises using it for account and password changes.
Interviewers ask this because both tools appear to offer the same account box. If all you remember is the click path, the question seems unfair. The useful answer is what SQL Server needs around that box.
- Permissions: Configuration Manager updates the settings and Windows permissions that the SQL Server service needs, including registry access. A generic service-account edit does not perform all of that SQL Server configuration.
- Protected keys: It updates the Windows local security store used to protect the Database Engine’s service master key when the account changes.
- Password changes: A password changed through Configuration Manager takes effect immediately without restarting the SQL Server service. A change through
services.mscrequires a service restart. - Account changes: Plan for a service restart and verify startup with the new account. The tool can prompt for that restart; changing the account is different from changing only its password.
My earlier answer also described adding the new account to a local SQL Server group. That was too broad as a modern rule. For many current components, permissions are assigned to a per-service SID, which does not change when the logon account changes. Some components and clustered setups still have their own group or permission requirements. Check the component and installation instead of assuming one group-membership step fits every server.
This is why I use Configuration Manager for SQL Server and SQL Server Agent account maintenance, then confirm the service state and review the error log after the change.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
Discover more from SQL Authority with Pinal Dave
Subscribe to get the latest posts sent to your email.
5 Comments. Leave new
If you’re using a cluster, I think SSCM will also make the update on all cluster nodes, whereas services.msc will only make the update locally.
You are right Elijah. Thanks for adding that.
Getting below error
—————————
SQL Server Configuration Manager
—————————
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
—————————
OK
What should I do?
Thanks for your time peter, I would write a blog on the steps we followed to fix your issue.
Is there a PowerShell equivalent of using Configuration Manger (yet)? We have an instance (I inherited this config) that cycles the services weekly. I would like to implement a better hands-free solution than the current “NET START – – NET STOP” solution.