SQL Server Service Accounts

Question

We have a new Windows 2016 server and I have to install SQL Server 2016 standard edition. Once I install and configure the SQL server I will move my existing production database to the new db server.

I have remote desktop access to the server to install the SQL server, but I am not sure if I need to have domain service accounts? I was of the impression that the default services SQL server assigns during the installation is enough.

Question 1:

The DB Admin team will remote desktop to that server and connect to SQL server using SSMS,not from their local SSMS. In that case do I have to have domain service accounts?

Question 2:

The application which DB support will have few hundred users connecting over network? In that case the default service account will be enough or do I have to have domain service accounts?

I am new to the database administration world, so I am still struggling with few concepts.

Answer 1

To first address your questions:

The DB Admin team will remote desktop to that server and connect to SQL server using SSMS,not from their local SSMS. In that case do I have to have domain service accounts?

While I would not advise that a production database is administrated by running SSMS directly on the production server, these two items are completely unrelated.

The application which DB support will have few hundred users connecting over network? In that case the default service account will be enough or do I have to have domain service accounts?

Again, these two items are completely unrelated.

---

You should check out Guidelines on choosing Service Accounts for SQL Server Services. The key parts of that, for you, are:

During a new installation, SQL Server setup does not default SQL Server engine Service and SQL Server Agent service to any account. The account specification is required step for these services. For details on recommended secure accounts, refer to Books Online Topic Setting Up Windows Service Accounts.

and

When choosing service accounts, consider the principle of least privilege. The service account should have exactly the privileges that it needs to do its job and no more privileges. You also need to consider account isolation; the service accounts should not only be different from one another, they should not be used by any other service on the same server. Do not grant additional permissions to the SQL Server service account or the service groups. Permissions will be granted through group membership or granted directly to a service SID, where a service SID is supported. For more details please refer to Books Online Topic Setting Up Windows Service Accounts.

So based on that, you will need to specific accounts for the engine and agent, and I would recommend having discrete logins for all services which adhere to the principle of least privilege as per the linked articles from the quotes.

Answer 2

SQL Server uses Virtual Acconts by default, which are a security best practice. You should not use a domain account unless you have a specific reason to do so.

The only two reasons I can think of are:

1) You have Failover Cluster Instance, or an Availability Group Listener that needs to support Kerberos Authentication.

or

2) SQL Server service needs to access domain resources and there are other programs running on that server under Network Service or a virtual account that need to access domain resources. This is because Virtual Accounts access network resources using the Computer Account. If you have lots of different programs installed on the server accessing network resources using the Computer Account, that can lead to an accumulation of privileges to that account.

So in the (paradigm) case of a dedicated SQL Server, not in a FCI or AG, you should not change the default service account.