The SQL Server Defensive Dozen - Part 3: Authentication and Authorization in SQL Server

  • Article

Introduction

In order to secure and harden SQL Server it is important to control who and/or what can authenticate to the system and what they have access to. This will satisfy regulatory needs such as the Secure Technical Implementation Guide (STIG).

As a reminder from our Introduction to this series, hardening is the process of applying Administrative and Technical controls to a system. Every article in this series will review both Administrative Controls and Technical Controls needed to properly managed and harden a system. This article will discuss authentication configuration and documentation for hardening SQL Server.

Administrative Controls

Documentation is essential to the success of a secured SQL Server. Ideally, documentation should be created before SQL Server is even installed. This allows the DBA to build SQL Server tailored to the requirements of the project and should allow SQL Server to operate in a more secure state from start to finish. However, it is not always realistic to build the documentation prior to installing SQL Server. In such cases it is recommended to ensure the project is configured to a working state, then back down the permissions and turn off any feature which are not required. Once it is determined what the minimal level of permissions and features are required for operation, these can then be documented.

Authorizing access to principals within SQL Server is vital to the success of maintaining a secure environment. Without thorough documentation, the DBA will have the difficult (if not impossible) task of keeping SQL Server secure. Thorough documentation ensures the DBA has written guidance on how the data is to be kept secure and provides accountability to the organization to ensure a least privilege model is maintained.

Many hardening guides require the use of Windows Authentication (Microsoft's recommendation) coupled with password complexity requirements (typically inherited from Group Policy) for compliance. In the event that Mixed Mode Authentication is utilized, the information owners must choose whether to inherit the password complexity requirements from the Operating System (OS), or if they will implement password complexity requirements utilizing policies and procedures defined by the organization. All of these decisions must be documented.

Additionally, documenting the authorized principals and permissions within SQL Server and regularly comparing them to a properly vetted baseline is essential to ensure unauthorized access is not granted. This can be accomplished using a tool like the SQL Server Vulnerability Assessment.

Technical Controls

The technical controls discussed in this section implement and enforce the administrative controls which were defined in the Administrative Controls section. These controls are settings which can be configured within SQL Server to ensure the written policy is enforced.

Authentication Mode

Configuring the authentication mode in accordance with system documentation is vital to the integrity and trustworthiness of the system. The following sections discuss various technical procedures which should be followed when implementing the Windows or Mixed authentication modes.

All Authentication Modes

  • Rename and disable the SA login to prevent attacks on the well-known login by account name.  Automated attacks will try to brute force the SA login as soon as a SQL instance is discovered. By renaming the SA login, most automated attacks will fail to brute force the database admin password.
  • Change SA password to a strong password. This is valuable even in Windows Authentication Mode in case Mixed Authentication Mode is enabled inadvertently or intentionally. Store the password in a secure location for a "break glass in case of emergency" situation.
  • Do not delete built-in principals. This will result in the loss of functionality and potentially an inaccessible system.
  • Use logon triggers for more granular control of the login process.
  • Using either local or Active Directory policies, implement a password policy including expiration and complexity requirements at a minimum.
  • Ensure passwords are encrypted when stored in client applications.

Windows Authentication

  • Windows authentication is the preferred and recommended form of authentication.
  • Use Windows groups to authorize users rather than Windows logins where possible to reduce the administrative overhead of managing access to the instance. It is important to note that each Windows user will be individually identified in the audit log, even if they are a member of a Window group. For example, if the domain user Contoso\Dan is a member of the domain group Contoso\DBA, the user Contoso\Dan will be identified in the SQL Server audit logs.

Mixed Mode Authentication

  • Use for users from untrusted domains/workgroups, cross-platform users, non-Windows applications, or legacy applications.
  • Do not use the SA login to manage SQL Server. Rather assign a known principal to the appropriate roles (e.g. sysadmin).
  • Outfit applications with a mechanism to change SQL login passwords.
  • Set MUST_CHANGE for new logins to ensure the end user changes the password to something only they know.

Baseline Logins and Users Principals and Permissions

Principals represent users, groups, services, and other entities which can access SQL Server resources. Principals gain permissions through direct assignment or role membership.

Verifying and limiting the use of permissions and roles inside of SQL Server is a requirement of many hardening guides. Administrators are encouraged to baseline the documented role memberships and server-level permissions. Administrators are also advised to regularly review (audit) the baseline against production to verify nothing has changed. The SQL Server Vulnerability Assessment tool can be used to help with these actions.

In most cases, the hardening guides mandate the permission or role membership must follow the Principle of Least Privilege. Best practices for implementing the principle of least privilege are:

The following are queries to extract SQL Server permission and role information. These queries are useful for creating and validating baselines, creating and validating documentation, and investigating permissions granted to principals.

Login List

Obtain all logins of the following types:

  • Certificate Login
  • SQL Login
  • Windows Group
  • Windows Login

[sql]SELECT login.name,
login.type_desc,
CASE login.is_disabled
WHEN 1 THEN 'disabled'
ELSE 'enabled'
END AS 'Status'
FROM sys.server_principals login
WHERE login.type IN ('C','G','S','U')
ORDER BY login.name[/sql]

Direct Permissions Assignment to Logins

Obtain all the permissions for the logins of the following types:

  • Certificate Login
  • SQL Login
  • Windows Group
  • Windows Login

[sql]SELECT login.name,
login.type_desc,
perm.state_desc,
perm.permission_name,
perm.class_desc
FROM sys.server_principals login
JOIN sys.server_permissions perm ON login.principal_id = perm.grantee_principal_id
WHERE login.type IN ('C','G','S','U')
ORDER BY login.name[/sql]

Server Role Permissions Assignment

Obtain all the permissions for the server roles.

[sql]SELECT role.name,
CASE role.is_fixed_role
WHEN 0 THEN 'False'
ELSE 'True'
END AS 'Built In Role',
perm.state_desc,
perm.permission_name,
perm.class_desc
FROM sys.server_principals role
JOIN sys.server_permissions perm ON role.principal_id = perm.grantee_principal_id
WHERE role.type = 'R'
ORDER BY role.name[/sql]

Server Role Membership

Obtain the server principals which are members of the server roles.

[sql]SELECT role.name AS 'Role',
login.name AS 'Login'
FROM sys.server_principals role
JOIN sys.server_role_members rm ON role.principal_id = rm.role_principal_id
JOIN sys.server_principals login ON rm.member_principal_id = login.principal_id
ORDER BY role.name[/sql]

Shared Accounts

No accounts should access the system that cannot be individually identified and audited. Non-repudiation of actions taken is required to maintain system confidentiality, integrity, and availability. Non-repudiation protects against later claims by a user of not having created, modified, or deleted objects or data in the database or instance.

NT AUTHORITY\SYSTEM

Ensure the NT AUTHORITY\SYSTEM (SYSTEM) account is not privileged or otherwise documented as having elevated access. Rather than granting SYSTEM additional permissions for services running as SYSTEM, consider using a Service SID to grant permissions directly to the service itself. This is the method SQL Server uses to grant permissions to the SQL Server and SQL Agent services. This method has been adopted by Microsoft System Center Operations Manager (SCOM) administrators to grant permission to the HealthService within SQL server.

Any user with enough access to the server can execute a task that will be run as SYSTEM either using task scheduler or other tools. At this point, SYSTEM essentially becomes a shared account because the operating system and SQL Server are unable to determine who created the process. This defeats non-repudiation as the actions taken by SYSTEM show in the audit logs as having been taken by SYSTEM, and not the user who invoked the command.

Prior to SQL Server 2012, NT AUTHORITY\SYSTEM was a member of the sysadmin role by default. In more recent versions of SQL Server, NT AUTHORITY\SYSTEM is explicitly not given sysadmin access.

Database Ownership

Databases should be owned by a login which should have full control of the database. The database owner login is mapped to the dbo user in the database. The dbo user has full control in the database and cannot be limited. It is recommended to use a low-privilege login as the owner of the databases. A low-privilege login should have no administrative access to the instance. This is especially important in cases where TRUSTWORTHY is enabled on the database, because the use of TRUSTWORTHY combined with a high-privilege login can be used to escalate the privileges of a low-privilege user within the database.

Use the ALTER AUTHORIZATION statement to change the owner of the database.

Contained Databases

SQL Server 2012 introduced the concept of contained databases. A contained database is a database which includes all the settings and metadata needed for its operation without dependencies on the instance. From a security perspective, a contained database makes it easier to have user accounts that are limited to a single database. Contained databases should only be enabled if they are required. As with the instance, Windows users or groups should be used when creating users within the contained database.

Discretionary Access Control (GRANT WITH GRANT)

Discretionary access control is based on the notion that principals are "owners" of objects and therefore have discretion over who should be authorized to access the object and in which mode (e.g., read or write). Ownership is implicitly or explicitly defined during object creation. Discretionary access control allows the owner to determine who will have access to objects they control.

The follow queries can be used to determine who has been granted GRANT with GRANT:

Server Level

[sql]SELECT who.name AS [Principal Name],
who.type_desc AS [Principal Type],
who.is_disabled AS [Principal Is Disabled],
what.state_desc AS [Permission State],
what.permission_name AS [Permission Name]
FROM sys.server_permissions what
INNER JOIN sys.server_principals who ON who.principal_id = what.grantee_principal_id
WHERE what.state_desc= 'GRANT_WITH_GRANT_OPTION'[/sql]

Database Level

[sql]USE [DatabaseName]
GO

SELECT who.name AS [Principal Name],
who.type_desc AS [Principal Type],
what.state_desc AS [Permission State],
what.permission_name AS [Permission Name]
FROM sys.database_permissions what
INNER JOIN sys.database_principals who ON who.principal_id = what.grantee_principal_id
WHERE what.state_desc= 'GRANT_WITH_GRANT_OPTION'[/sql]

Proxies and External Access

Customers may have designated administration user and/or service accounts on the operating system. Typically, this is an administration account with which they automate complex maintenance routines (such as automatically adding a database to an Availability Group).

SQL Server provides highly-secure features (also capable of being permissioned) to perform tasks. Please see How to: Create a Credential (SQL Server Management Studio) and Creating SQL Server Agent Proxies for more information.

Remove any SQL Agent Proxy accounts and credentials that are not authorized.

Conclusion

This completes the facet and considerations related to securing SQL Server authentication and authorization. Please stay tuned for the next topic in the series discussing modules management and programmability for SQL Server.