Why to Use SQL Server Configuration Manager Over Services applet (services.msc)? – Interview Question of the Week #112

Question: Why to Use SQL Server Configuration Manager Over Services applet (services.msc)?

Answer: You might have heard this advice many times but never got a complete answer to “Why”? Most of the blog would tell you “how” to change the service account in the right way.

“Why should we use the SQL Server Configuration Manager (SSCM) not services. Mass” is the question which I am trying to answer in this blog? Few interviewers also might ask this question to check candidate’s skill.

Here are the reasons which I can think of.

1. Password validation: When we change the password from services.msc it saves the password without validation. So, if we give incorrect password, service startup would fail with the standard error – Error 1069: The service did not start due to a logon failure.
2. Prorogation to permissions in the registry: When an account is changed, the permission on the registry is also modified. If it’s done from services.msc then SQL startup might fail because service account if not able to read the registry keys, which is one of the parts in SQL Service startup.
3. Group Membership Changing service account via SSCM adds the service account to the appropriate group membership which provides the necessary permissions.
4. Service Restart: When changing service account, SCCM stops and starts the service automatically. Password change doesn’t need a restart. But as mentioned earlier, its validated and error would be thrown if password is incorrect.
5. Encryption: SSCM also takes care of updating the Windows local security store which protects the service master key for the Database Engine.

Note that the configuration manager uses WMI to make changes. So above all points are also applicable when we are using SMO or WMI to change password programmatically.

Reference:Pinal Dave (http://blog.SQLAuthority.com)