Problem
We are looking to install SQL Server and we 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.
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
