The SQL Server documentation states that it’s strongly recommended to use SQL Server Configuration Manager and not Control Panel\Administrative Tools\Services (services.msc) when changing SQL Server or SQL Server Agent service account. The question is – why?
The reason is that when SQL Server service account is configured with a Windows NT account, SQL Server sets various Windows user rights and permissions on several files, folders, and registry keys. However, when you use Services Console to set SQL Server Service account, these permissions are not set and you may run into serious issues due to lack of proper security settings.
If you’ve already made changes to ‘Log on as’ setting on MSSQLServer or SQLServerAgent services using the Services Management Console, follow the steps below to manually change the security rights:
- Give full control on the following registry keys and sub-keys to the Windows user account selected as the startup security context for SQL Services:
· HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\90
· HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<MSSQL.x>
- Give full control on the following file folder:
· \Program Files\Microsoft SQL Server\<MSSQL.1>\MSSQL
Special thanks to Saleem Hakani for this information!