15

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? Or does the SQL configuration manager actually take care of this, and I just had an unforeseen issue?

I quite often have to setup Microsoft 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.

To summarize what I've seen so far:

For simple deployments\development environments it is OK to use the virtual account defaults the installer uses: e.g. NT SERVICE\MSSQLSERVER

Avoid using the SYSTEM account, this is not secure.

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. Allegedly if you use a domain account at installation time the installer will set any required permissions for you.

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.

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 I'm not sure if a GPO may have interfered with setting this local security policy)

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page.

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.

SQL Server 2014, Domain Controller is on Windows Server 2008 R2.

Improve this question
5
  • Which version of SQL? Is this an AD environment? If so, what's the domain level?
    – Katherine Villyard
    Commented Jan 20, 2015 at 0:42
  • Thanks, I have added the versions to the question. SQL Server 2014, Domain Controller is on Windows Server 2008 R2 functional level
    – plumdog
    Commented Jan 20, 2015 at 0:48
  • 2
    This doesn't look like vague documentation to me. This looks pretty comprehensive. - msdn.microsoft.com/en-us/library/ms143504.aspx
    – joeqwerty
    Commented Jan 20, 2015 at 0:52
  • Yes I agree, overall the documentation is good but the part I am unsure about was vague is what I was meaning. You are right, by in large the documentation is very detailed.
    – plumdog
    Commented Jan 20, 2015 at 23:39
  • The URL: msdn.microsoft.com/en-us/library/ms143504.aspx is no longer accessible. The request is blocked.
    – Senior Systems Engineer
    Commented Jul 15, 2023 at 11:12

1 Answer 1

Reset to default
11

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.

Improve this answer
3
  • Thanks for your reply, I agree with your comments. I setup SQL server on a different clean VM to check this, and I was able to swap from the virtual account NT SERVICE/MSSQLSERVER to a domain user account and it worked with no issue. So I think the answer is as you have said, use SQL Server Configuration Manager to change the service user account logons and no additional permissions should be required.
    – plumdog
    Commented Jan 20, 2015 at 23:35
  • One additional comment, sorry I could have made my question clearer by putting my main point at the top of the post which I will do in future. The main question I was not sure about was; [if you create a new domain user account for each of the SQL Server processes, what permissions should be set for each account?...]. The reason I felt the documentation was vague in this part is because there is a list of permissions that are set, but it's not explicit whether SSCM will set these for you.
    – plumdog
    Commented Jan 20, 2015 at 23:36
  • @plumdog Per your second comment, most of the permissions are given by the installer. A virtual service account and sid are tied to that, which is why SSCM should always be used so that transfer in and out of permissions tied to that are done correctly, among other things such as the security tied to the service master key encryption, etc.
    – Sean Gallardy
    Commented Jan 21, 2015 at 2:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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