Skip to main content
Database Administrators

Return to Question

moved the main question to the top of the post for better clarity
Source Link
plumdog
plumdog
  • 253
  • 1
  • 2
  • 5

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)

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

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.

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)

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

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.

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.

added 19 characters in body; edited tags; edited title
Source Link
Paul White ♦
Paul White ♦
  • 90.3k
  • 30
  • 423
  • 663

MS SQL Server service account Windows privelegesprivileges and rights

I quite often have to setup MSMicrosoft 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 Summarizesummarize what I've seen so far;far:

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

Avoid using the SYSTEMSYSTEM 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'log on as service permission, which contradicts the part where the SQL Server configuration manager will set any required permissions. (Although imI'm not sure if a GPO may have interfered with setting this local security policy)

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

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page: http://msdn.microsoft.com/en-us/library/ms143504.aspxon 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.

Thanks.

MS SQL Server service account Windows priveleges and rights

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.

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

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

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page: http://msdn.microsoft.com/en-us/library/ms143504.aspx

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.

Thanks.

SQL Server service account Windows privileges and rights

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)

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

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.

Post Migrated Here from serverfault.com (revisions)
Source Link
plumdog
plumdog
  • 253
  • 1
  • 2
  • 5

MS SQL Server service account Windows priveleges and rights

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.

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

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

Microsoft does provide a list of the permissions that the SQL Server Setup grants on this page: http://msdn.microsoft.com/en-us/library/ms143504.aspx

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.

Thanks.