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.
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
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.
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.
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
Check your service accounts to see if you are following best practices
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
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.
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.
Monday, October 28, 2013 - 3:03:50 PM - K. Brian Kelley
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:
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.
Thursday, October 10, 2013 - 9:34:55 AM - K. Brian Kelley
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.
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
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.
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.