Sql-server – Switching SQL Server service credentials to AD account

permissionssql server

To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers. The first one I did was fine because it was a fresh install. But for existing servers, making the change, I’m not sure about the “rights” required. The how-to states:

The SQL Server setup program will grant the necessary rights on the machine to that account during installation.

But if I’m not doing a fresh install, I guess I don’t know what the necessary rights are. I know I will:

  • Apply Perform Volume Maintenance Tasks Right
  • Apply Lock pages in memory right
  • Disable interactive login for the AD service account
  • Add AD service account to local Administrators group

Is there anything more needed to be done to successfully transfer the SQL Service account from NT SERVICE\MSSQLSERVER?

Best Answer

Use SQL Server Configuration Manager to change the service account used by the SQL Server services. That will ensure any necessary changes are made to permissions required by that account. See the MIcrosoft Docs for precise details.

Regardless of what you set the service account to, any local rights should be applied to NT SERVICE\MSSQLSERVER, as this is the per-service Virtual Account, and even after you change the service account, the SQL Server will retain any rights granted to it.

To pursue best practice for SQL Service accounts, I’m working through changing the SQL service account to be AD accounts for our existing SQL servers.

This is only a best-practice if you are using a Managed Service Account or Group Managed Service account, and only if

  1. You've got a cluster and need Kerberos authentication, or

  2. You've got multiple services on the SQL Server and and need to differentiate domain permissions between them.

Otherwise leave the default Virtual Account account and grant any domain permissions for accessing external resources (like file shares) to the machine account (eg MyDomain\MyServer$).

See

Managed service accounts, group managed service accounts, and virtual accounts are designed to provide crucial applications such as SQL Server with the isolation of their own accounts, while eliminating the need for an administrator to manually administer the Service Principal Name (SPN) and credentials for these accounts. These make long term management of service account users, passwords and SPNs much easier.

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?view=sql-server-ver15#New_Accounts

Related Question