0

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?

1
3

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

2
  • Granting the machine account access to resources on the LAN could be considered a security hole since any service that runs on that server could then access those resources, not just SQL Server. If SQL Server needs to access resources on the LAN, having a discrete domain account for the SQL Server Service makes sense. I would even go so far as to say having a discrete domain account for each SQL Server instance organization-wide would provide the most security and flexibility. – Hannah Vernon Nov 23 '20 at 14:17
  • That's definitely a fine practice, and with MSA's it's no longer a password-management nightmare. But if the server is dedicated to running a single instance of SQL Server, and doesn't run other low-privilege services that access network resources, the default configuration basically gives you a discrete domain account for each SQL Server. – David Browne - Microsoft Nov 23 '20 at 15:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.