Risk mitigation would indicate creating a separate account for each service on each machine. The level of work required to create the accounts necessary is extremely minimal, but the unknown risks that accompany not doing so are quite high, according to Microsoft's own recommendations.
Microsoft Best Practices recommend using separate service accounts for all services.
See http://msdn.microsoft.com/en-us/library/ms144228.aspx#isolated_services for details.
The salient points being:
Isolate Services
Isolating services reduces the risk that one compromised service could be used to compromise others. To isolate services, consider the following guidelines:
Run separate SQL Server services under separate Windows accounts. Whenever possible, use separate, low-rights Windows or Local user accounts for each SQL Server service. For more information, see Configure Windows Service Accounts and Permissions.
There is also a KB talking about securing SQL Server that mentions how to configure service accounts properly:
http://support.microsoft.com/kb/2160720
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.
Technet has an article, titled Configure Windows Service Accounts and Permissions at http://technet.microsoft.com/en-us/library/ms143504.aspx that has this to say:
Security Note: Always run SQL Server services by using the lowest possible user rights. Use a MSA or virtual account when possible. When MSA and virtual accounts are not possible, use a specific low-privilege user account or domain account instead of a shared account for SQL Server services. Use separate accounts for different SQL Server services. 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.
"MSA" in the above paragraph refers to "Managed Service Accounts" which is the default for installations on Windows 7 or Windows Server 2008 R2 and above. Managed Service Accounts are defacto unique to each machine.
As an aside, one issue I think about when configuring multiple servers to run under the same service account is account lockouts. If you use a single service account for all SQL Servers, and the service account gets locked out, all your servers might be affected. If you have one account per service, at most one server can be affected by a lockout.
I quite often have to setup MS SQL Server and wondered if anyone can
provide advice on configuring the accounts the services should run as.
IMO this has been vaguely documented by Microsoft, while they point
you in the right direction I have never been able to find any concrete
examples.
It's actually documented quite thoroughly: http://msdn.microsoft.com/en-us/library/ms143504.aspx
Is there a part of that you're not sure about?
For simple deployments\development environments it is OK to use the
virtual account defaults the installer uses: e.g. NT
SERVICE\MSSQLSERVER
This is going to depend on the environment. I, personally, hate finding a server someone setup using a local account and asking to get access to network resources some time in the future, among other issues.
For production and in domain environments it's recommended to use
either a Managed Service Account, or create a domain user account(not
an admin) for each service.
Again, depends, but generally I would agree (a counter example would be availability groups where it makes sense to use a single domain account across all instances).
Allegedly if you use a domain account at installation time the
installer will set any required permissions for you.
Unless there is a failure, etc, it will do so. I'm not sure why the "Allegedly" part.
If changing the service account on an existing install from a virtual
account to a domain account the recommendation is to use the SQL
Server configuration manager to set the new service accounts.
Allegedly this will set any required permissions for you.
When changing any of the services for SQL Server, always use SSCM. Always. Period. It will set the permissions for the new account to the basics. If before the local system account was used and unrestricted permission to everything on the system was had, I would expect something to fail permissions after the change due to tighter controlled security. That's not a SQL Server SSCM fault, that's an admin fault of not granting proper EXTRA permissions (such as accessing a network share, restricted folders, items outside of the SQL Server install purview, etc.)
I just tried changing the service account in an existing install to a
domain account and it would give me a logon failure until I granted
the account 'log on as service' permission, which contradicts the part
where the SQL Server configuration manager will set any required
permissions. (Although im not sure if a GPO may have interfered with
setting this local security policy)
Sounds like a GPO is causing an issue (IMHO). Wouldn't be the first time :)
So my question is, if you create a new domain user account for each of
the SQL Server processes what permissions should be set for each
account?
I would explicitly set any permissions outside those stated in the msdn link I have above (also given by @joeqwerty and in your OP). For example, on a "backup" folder on a network share, on a new drive added to hold new databases (where setup was already run but the drive didn't exist), etc.
But it's not clear to me if that is something I should be doing
manually for the user I create to run the service as, or whether using
the SQL config manager should automatically set these permissions.
Unless something is extremely broken with the server, these shouldn't have to be manually given.
Best Answer
A separate AD service account for each service on each instance is the most secure model and if you have some good password management software it shouldn't be a problem. The work involved in creating all the service accounts and entering them into your password software will be quite time consuming depending on how big your environment is.
Some people sacrifice some security by using the same service account for all the services on a SQL instance. The problem with this is if the account gets locked out then all the services including the SQL service account will be locked out. This problem becomes even worse if you use the same AD account across SQL instances as one instance can be affected by another. Also obviously if an account is compromised, the more services it is used for then the more services that are compromised.