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.
2015-04-08 15:40:46.82 spid11s Error: 17190, Severity: 16, State: 1.
2015-04-08 15:40:46.82 spid11s Initializing the FallBack certificate failed with error code: 1, state: 20, error number: 0.
2015-04-08 15:40:46.82 spid11s Unable to initialize SSL encryption because a valid certificate could not be found, and it is not possible to create a self-signed certificate.
The instance is configured to enforce SSL and SSL is not configured properly. Follow the steps at How to enable SSL encryption for an instance of SQL Server by using Microsoft Management Console. You probably want to start by removing the Force Protocol Encryption
option first (which currently exists for this instance in the registry, probably under the HKLM\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQLEXPRESS\MSSQLServer\SuperSocketNetLib
the ForceEncryption
value is 1 instead of 0), confirm the engine starts, then continue from there by either configuring it properly, or removing it if not required.
Best Answer
I would ask your vendor why they can't install SQL Server on a domain controller because the documentation seems to indicate otherwise.
See the Installing SQL Server on a Domain Controller section of Hardware and Software Requirements for Installing SQL Server 2014
So there are a few limitations, but I don't think these will apply in your situation. Using a domain account instead of a local service account shouldn't be a problem and I don't think you will use a cluster if you only have one server. Your domain controller probably isn't read only either.
The process is also documented in the Autodesk Vault knowledge base so I suppose it's supported for at least some of their software.
Microvellum's documentation seems to suggest the installer will check and you must not install SQL Server on a domain controller, but in fact that will only generate a warning but still allow you to continue, and it will work as long as you don't try to use a built-in service account.
Windows 2012 R2 essentials is also a supported operating system if you look at the Operating System Requirements on that page as long as it's 64bit.
That being said. If your vendor insists, you don't need to reinstall the operating system to remove the domain controller role from the installed operating system. The steps to demote a domain controller are documented.
Be warned though, if your workstations use a domain account to log in to the network and you remove the domain controller, your users may lose access to their PC's and the network, so you should be very careful when considering that route.
Also note one of the limitations of installing SQL Server on a domain controller is that you can not demote or promote (i.e. remove the domain controller role, or make your server a domain controller) once SQL Server is installed on the machine. In order to do so you must first uninstall SQL Server and then reinstall it afterwards.
As a last note, if you really can't install SQL Server on your machine because it's a domain controller, and you still need the domain controller role for client authentication, you could consider running a virtual machine on your server and run SQL Server inside that VM.
You should always verify licensing with your vendor, but this seems to suggest it could be a viable option without having to purchase a new server or an additional license:Understanding Licensing for Windows Server 2012 R2 Essentials and the Windows Server Essentials Experience role