SQL Server Accounts
Sometimes I run into established DBA's who have a little confusion regarding the different types of accounts used with SQL Server. I suspect that kind of confusion may come from a history of installing/experimenting with SQL Server on a workstation or laptop where they do everything under a single account. The biggest problem with a single account for everything is that it's a major violation of the security principle of least-privileges. Instead, DBA's should think in terms of four different types of accounts, so I hope this list will clear up this issue:
Installation Account: This is the Windows account the person is logged in as when they are installing SQL Server, and it needs maximum privileges (permissions) for the installation process to be able to accomplish everything it needs to do. Because of these maximum privileges, this installation account should not be used for normal DBA work, nor for service accounts, nor for user or application accounts. In high-security environments, you may need to double check that your installation account has all the privileges needed before you start the install (https://tinyurl.com/7f8czcs).
Service Accounts (aka 'startup' account): A service account is a Windows account that stores and controls the privileges for a service-oriented application, such as the SQL Server database engine, SQL Server Agent, Reporting Services, Analysis Services, Integration Services, or the Full-text Service. When a service starts, it essentially logs into Windows using the credentials of its service account. These service accounts must exist before you install SQL Server services, but you do NOT need to assign all the privileges they'll need because SQL Server's install process will do that, assuming your installation account has all the privileges it needs. Each instance of each service on each server should have its own unique service account, so if you have the SQL Server database engine, SQL Server Agent, and the Full-text service all installed on 50 servers, you need 150 service accounts (see https://msdn.microsoft.com/en-us/library/cc281953.aspx or the DoD Database STIG). DBA's who are not held to best practices often compromise on this issue, and here are the two most common compromises I've seen: #1, Using one service account per server (e.g. the database engine, Agent, SSRS, etc. all use the same service account, but only on one server); #2, Use one service account per type of service (e.g. every instance of the database engine uses the same service account, every instance of Reporting Services uses the same service account which is different from the engine's service account) . These compromises may be common, but they reduce security and are NOT best practices.
Administrator Accounts: The principle of role-based security requires a Windows domain group for DBA's, where each DBA should have their normal Windows account as a member of that group, and the SQL Server Sysadmin server role should only be assigned to that group. No individual DBA accounts should have SQL Server system administrator rights; such rights should only come through membership in the DBA group. SQL Server system administrative privileges should be restricted to only those DBAs authorized by the system's Information Assurance Officer. I'll also mention here that high-security best practices require that the built-in "sa" account be both renamed and disabled, and on version 2005 and earlier, the "Builtin\Administrators" group should be removed to ensure that operating system administrators do not get SQL Server admin privileges.
User/Application Accounts: Users and applications use Windows accounts to connect to SQL Server, and to comply with the least-privileges principle, each user and application must have its own account, which should normally not have sufficient privileges to function as an installer account, a service account, or an admin account. Furthermore, permissions should not be assigned directly to user/application accounts, but to Windows groups that the user/application accounts are members of. Stating this another way, each unique set of privileges (permissions) should be assigned to a Windows group only, and each user or application account should be a member of the group that carries the privileges that account needs. For example, if the Contoso\Joe and Contoso\Jane user accounts need the db_owner role for the Sales database, create a Windows group such as Contoso_Sales_Data, assign the db_owner role to the Contoso_Sales_Data group, and make the Contoso\Joe and Contoso\Jane accounts members of that group. This role-based security method should be used even if some groups end up with only one member. It may seem like role-based access controls are a lot of extra trouble, but the benefits it provides are worth it for enterprise-class organizations.
Here are the most relevant sections of the DoD Database STIG (V8R1):
- 2.3 Database Authorizations
- 3.3.11 Least Privilege
- List of requirements in 3.3.11.1 and 3.3.11.2: DG0005, DG0008, DG0040, DG0041, DG0063, DG0077, DG0080, DG0085, DG0086, DG0116, DG0119, DG0120, DG0121, DG0124
For any SQL Server security newbies, I'll also point out here that a best practice is to only use Windows authentication (mixed mode authentication), as opposed to SQL Server logins. I think the main reason for this is that SQL Server logins have password hashes stored in SQL Server, and obviously, Windows password hashes are stored elsewhere. The only time a SQL Server login should be used is when you have a poorly written 3rd-party application that isn't capable of connecting with a Windows account.
Comments
- Anonymous
September 29, 2014
Quote "For any SQL Server security newbies, I'll also point out here that a best practice is to only use Windows authentication (mixed mode authentication), as opposed to SQL Server logins."
I think you should edit this sentence and remove the "(mixed mode authentication)" bit as it's ambiguous/misleading. - Anonymous
September 29, 2014
Sorry forgot to mention this is a very useful post. However, I'd like to see it extended (or a new one created) that discusses the correct configuration of SQL Service accounts. For example, SQL DB Engine should be Domain User accounts, not a member of the Domain Admins grp, not a member of Local Admins grp, not a member of the SQL SysAdmins role, should have 'Log On Locally' rights (etc), should have the ability to create an SPN if you want Kerebos authentication (the best authentication protocol, then comes NTLM etc).