Learn more about SQL Server tools

   
   















































How to Create Secure SQL Server Service Accounts

MSSQLTips author K. Brian Kelley By:   |   Read Comments (13)   |   Related Tips: More > Security
Problem

I'm looking to install SQL Server and I know it will require a service account. In this tip we cover what should be done to configure the SQL Server service accounts securely.

Solution

Service Accounts for a Server Installation

If you're on a domain, it's generally recommended that you use a domain level account. This should be a regular domain user account and definitely not a member of the Domain Admins group. The reason for the domain user account recommendation and not a local account is that it allows Active Directory to be the single source for your security system. It makes things easier to manage and audit. If you don't have a domain, using a local account is fine, just don't make it a member of the Administrators group (if you go the domain user account route, you should also avoid this group membership).

If you're installing multiple components, the recommendation is to use separate accounts if you can. If you're just installing the database server, you have two services to be concerned with: the main SQL Server service and the SQL Server Agent service. Even in this case it's recommended to use separate accounts. While this is a best practice, it is not unusual to see a single account per server for all of the SQL Server services. However, the issue here is those additional services could potentially have permissions in other SQL Server components you might not want because they aren't needed. For instance, the SSIS service doesn't need to be able to control the database engine. However, if you use the same account, there's a potential for that to happen.

As far as assigning specific permissions, don't. Let the installer set the permissions correctly. It will attempt to set the minimum permissions necessary. If at some later point you need to change the service account, use the SQL Server Configuration Manager because it will handle the permissions as well as a few other details that are not done by changing the service account using any other interface.

One definite no-no is to re-use a service account across servers unless you're dealing with a situation like a scaled-out SQL Server Reporting Services installation. For the database engine, SQL Agent, and SSIS, you should ensure that no service account is used on multiple servers. If you have the need for a service account to access resources on a second server, you can always assign the permissions explicitly.

Considering SQL Server 2008 and Windows Server 2008 and above

If you have the combination of SQL Server 2008 or above and Windows Server 2008 or above, then SQL Server will be installed using an operating system feature called Service Isolation. In terms of user accounts, all user accounts have a unique identifier we call a SID (Security Identifier). With Windows Server 2008, services also have SIDs. This was implement for the situations were you have multiple services running under the same account (for instance, Network Service or System) but these services shouldn't have access to resources belonging to other services that happen to share the same service account. As a result, each service now has a SID.

SQL Server 2008 and above can use this to ensure that the service has permission to database resources, not the account. For instance, in the figure below, note that NT Service\MSSQL$SQL2008R2 (a SQL Server 2008R2 named instance called SQL2008R2) and its SQL Agent service, NT Service\SQLAgent$SQL2008R2, are listed as logins. This is because of service isolation. In this case, the only way you get sysadmin level access is by coming through those services. Even if the service account was to be compromised, unless it was one of those services connecting, the account would have no privileges within SQL Server.

Services Appearing in Logins

Handling Developer Workstations

When considering developer workstations, there is no change in the recommended best practices, but their probably ought to be. If your organization has a lot of developers, then keeping up with services accounts for ever developer installation can be a management nightmare. In most cases developer workstations shouldn't have processes that need to access anywhere outside of the workstation SQL Server is installed on. In this case it makes more sense to create local user accounts for the various SQL Server services. The same rules apply: separate accounts for different services and starting each user account as a user level account and allowing SQL Server to update the permissions accordingly.

One thing you don't want is for the developers to use their own accounts. This can cause a problem at password reset time. A classic scenario is the developer gets the message to change the password when he or she logs on to the domain and follows suit. However, the SQL Server service would still be configured to run with the old password. As a result, next time SQL Server is started, you'll have an audit failure. This is true for all accounts with that same user account but now the problem is every single one of those services will attempt to log on to the domain as if nothing has happened. The problem then is each of those services will generate audit failure events and could potentially cause the user account to be locked out, depending on the lockout policy. Obviously this is a situation to be avoided.

Also, if you still have developers on XP, it's time to migrate. The Service Isolation spoken of earlier is first available in Windows Vista. So a combination of Windows Vista or higher and SQL Server 2008 or higher will ensure you get Service Isolation and minimize your exposure.

In Summary

  • For servers use domain accounts
  • For developer workstations use local user accounts
  • Never configure the accounts to be anything more than regular user accounts. Let SQL Server make the permission changes.
  • Use separate accounts for each service.
  • Never re-use an account on multiple servers, with the exception of scale out scenarios like is possible with SQL Server Reporting Services.
  • Try to use Windows 2008 or higher for the operating system
Next Steps


Last Update: 9/28/2011


About the author
MSSQLTips author K. Brian Kelley
K. Brian Kelley is a SQL Server author and columnist focusing primarily on SQL Server security.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Learn more about SQL Server tools
Post a comment or let the author know this tip helped you.

       All comments are reviewed, so stay on subject or we may delete your comment.

*Name   *Email Notify for updates



       Note: your email address is not published. Required fields are marked with an asterisk (*)


Get free SQL tips:

*Enter Code refresh code     



Tuesday, January 28, 2014 - 1:13:02 PM - Chris Read The Tip

I like the way you spell out the service accounts and the REASONS why you use one over the other. There's so much misinformation on this and Microsoft provides limited information (they never explain "why" you do something). This is how it should be written.


Wednesday, November 06, 2013 - 8:38:49 AM - Prashant Thakwani Read The Tip

Sorry Brian, Please ignore my last comment. I just got to know from the DBA that they added it later after the installation.

 


Tuesday, November 05, 2013 - 3:00:25 PM - Prashant Thakwani Read The Tip

Thanks Brian.

Yes, I am pretty sure. One strage thing is where the service account in the form of DOMAIN_NAME\ACCT_NAME are there, into those instances, I dont see accounts like NT Service\*.

 


Tuesday, November 05, 2013 - 10:50:28 AM - K. Brian Kelley Read The Tip

Are you sure it wasn't explicitly added because of a 3rd party product, such as a backup tool?

 


Tuesday, November 05, 2013 - 10:30:07 AM - Prashant Thakwani Read The Tip

Similar to the accounts NT Service\*, in few of the SQL Server installations with Windows 2008R2, SQL Server 2008 and SID, I have seen the actual domain service account for each SQL Server and SQL Server agent account into the SQL Server with sysadmin into SQL Server, instead of NT SERVICE\*. For example DOMAIN_NAME\ACCT_NAME

I am trying to understand the difference between these two installations.


Monday, October 28, 2013 - 4:14:47 PM - Luke L Read The Tip

Thanks for the quick reply.  I understand the cluster service SID isolation bit, but I found it odd that the NT Authority\System login was what was actually performing the Select @@Servername for the isAlive check.  I would have expected that to come from the the ClusSvc Sid. 

I suppose the ClusSvc SID is the group that authorizes the connection to SQL, however NT Authority\System is the context the connection is actually made. Much in the same way a user that is part of a group (which has a lgoin to SQL) which authorizes them to access SQL, but the context is the user account for auditing etc.

I have some testing to try and see if I can eliminate the NT Authority login.

Thanks again,

-Luke.

 


Monday, October 28, 2013 - 3:03:50 PM - K. Brian Kelley Read The Tip

The cluster service logon is using the SID for the Cluster Service, not the account itself. This is actually more secure. The feature that supports this is called Service Isolation. You can read about it here:

 

http://windowsitpro.com/security/q-how-can-security-windows-service-benefit-service-isolation-feature-how-can-i-set-service-

 

In other words, you don't want to change this.

With respect to NT Authority\System, that's probably tied to VSS. That can't be changed.

 


Monday, October 28, 2013 - 12:25:28 PM - Luke L Read The Tip

I am using SQL2008 R2 and Windows 2008 R2 in a cluster.  Per best practices, all of my SQL Services are running under domain accounts.  When I review the isntance Logins I see NT Authority\System and NT Service\ClusSvc.

I understand this is the cluster service coming in to perform IsAlive, LooksAlive checks, and they've been restricted to the Public role to run Select @@servername. Is it possible to change the ClusterService Account Logon from Local System to a domain account so that these general logins can be removed?  If so, can you pelase point me in the direction of some documentation. 

Thanks,

-Luke.


Thursday, October 10, 2013 - 9:34:55 AM - K. Brian Kelley Read The Tip

If the SQL Server Configuration Manager wasn't used to set the account in the first place, it might not have appropriate rights. The Administrators group does have all the rights, but it has too many rights for most installations. 

 


Wednesday, October 09, 2013 - 5:31:05 PM - Cesar Read The Tip

I checked the SQL Server Configuration Manager and everything looked good there; the appropriate accounts assigned to the right services and the password verified. My work around was to add the domain accounts to the local server administrators group. At that point, the services started...odd.


Wednesday, October 09, 2013 - 8:54:54 AM - K. Brian Kelley Read The Tip

No. They shouldn't need any special access. On the local server, if you use SQL Server Configuration Manager, it will assign the right permissions, provided you have the permission to add those rights. If you just changed the account through the Services manager or something other than SQL Server Configuration Manager, please make the changes using that tool.

 

 


Wednesday, October 09, 2013 - 1:26:06 AM - Cesar Read The Tip

 

Do the domain accounts that are created for each sql service need to be added to any specific local server groups in order to start their respective sql service? for some reason, the services won't start due to logon error. I've supplied the proper account and password for each but still doesn't start. The domain accounts have no memberships other than the regular' domain users'. Thank you.


Tuesday, May 07, 2013 - 3:14:03 PM - Gustav Read The Tip

Thank you - you explain clearly what to do

But the steps concerning the title of your article "How to ..."  are missing so I don't know what to do

Youa are explaining what to do but not how :)




 
Sponsor Information