One of my clients posted a question to me about management of SQL Server service account. Since it was a nice learning for me, I am sharing my discussion via this blog post. Let us see the Best Practices About SQL Server Service Account and Password Management.
- Do I need to have a SQL Service account as a part of Local Administrators or Domain Administrator?
Answer: No, Never! As a best practices SQL Server service should be using a minimally privileged account. We should always avoid running SQL Server services under the contexts of the local system, local administrator or domain administrator accounts. On the other hand, we need to make sure that the SQL Server service account has “full control” permissions on data, log and backup directories for read and write activities.
- How should I change service account and password?
Answer: As per Microsoft documentation, we should always use SQL Server configuration manager to do any modification for SQL Service. Account change and password change, both should be done only via configuration manager.
- Do we need downtime to change service account or password?
Answer: For service account change we need to restart SQL server service. But if we are only changing the password then there is no need to restart the SQL Service.
Keep in mind a bug in SQL Server where if we change the password in clusters on the passive node, SQL services would stop.
https://support.microsoft.com/en-us/kb/972387 (SQL Server Failover Cluster Instance is taken offline when you change the password for the service account on the passive node)
- Why we should not use services.msc?
There are valid reasons for that. As per books online – 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.
I have seen DBA using PowerShell or VBScript do achieve the same remotely. As long as we are calling API provided by SQL Server, there is no difference of using the UI or script.
What method do you use to change account or password for SQL Service?
Reference: Pinal Dave (https://blog.sqlauthority.com)