The SQL Server Defensive Dozen Part 2 - SQL Server Encryption, Key Management, And Data-At-Rest Encryption

  • Article

Introduction

In order to properly secure and harden SQL Server, the use of encryption provides many benefits including safeguarding data, separation of duties, and satisfying regulatory needs such as the Secure Technical Implementation Guide (STIG) or General Data Protection Regulation (GDPR). Within SQL Server there are several types of encryption: Channel encryption (encrypting login/data packets over the wire), Transparent Data Encryption (TDE) of data at rest, and Always Encrypted for data in use. SQL Server encrypts data with a hierarchical encryption and key management infrastructure. Each layer encrypts the layer below it by using a combination of certificates, asymmetric keys, and symmetric keys.

As a reminder from our Introduction to this series, hardening can be thought of as 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 encryption and key management settings for hardening SQL Server.

Administrative Controls

Many of these checks require the encryption components be documented and referenced for check compliance. For example, one must check the server documentation to see if encryption-at-rest or transmission is required for the databases. The technical controls below should match the customer's documented posture. Customer should list all encryption requirements for the SQL Instance and any encryption components configured based on the respective vulnerability. The correct controls need to be configured for each check.

Technical Controls

Service Master Key

Encrypting data requires secure encryption keys and key management.  The Service Master Key (SMK) is the root of the encryption hierarchy. It is the entry point for any kind of encryption within SQL Server. The SMK is encrypted by SQL Server Setup using both the SQL Server Service accout and also the machine key. Both encryptions use the Windows Data Protection API (DPAPI). It is critical to backup the SMK and store in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server. The following code can be used to back up the Service Master Key:
[sql]BACKUP SERVICE MASTER KEY TO FILE = 'filePath' ENCRYPTION BY PASSWORD = 'password'[/sql]

Database Master Key

The database master key is a symmetric key used to protect the private keys of certificates and asymmetric keys that are present in the database. When it is created, the master key is encrypted by using the AES_256 algorithm and a user-supplied password. In SQL Server 2008 and SQL Server 2008 R2, the Triple DES algorithm is used. To enable the automatic decryption of the master key, a copy of the key is encrypted by using the service master key and stored in both the database and in master. The copy stored in master is silently updated whenever the master key is changed. To create a Database Master Key and then the necessary backup to be stored offsite, reference the following code:
[sql]CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'
BACKUP MASTER KEY TO FILE = 'filePath' ENCRYPTION BY PASSWORD = 'password'[/sql]

Asymmetric Key Management

An asymmetric key is a securable entity at the database level. In its default form, this entity contains both a public key and a private key. When executed without the FROM clause, CREATE ASYMMETRIC KEY generates a new key pair. When executed with the FROM clause, CREATE ASYMMETRIC KEY imports a key pair from a file or imports a public key from an assembly. By default, the private key is protected by the database master key. If no database master key has been created, a password is required to protect the private key. If a database master key does exist, the password is optional. The private key can be 512, 1024, or 2048 bits long. If asymmetric keys are being used, they should be documented, secured properly (as documented), and backed-up. An asymmetric key can be used to encrypt a symmetric key for storage in a database.

Transparent Database Encryption (TDE)

Various hardening guides require SQL Server data files containing sensitive information to be encrypted. As well, system documentation must state whether the database handles classified or sensitive information. Some options for encrypting data in SQL are:

  • Transparent Database Encryption can be used to encrypt data at rest
  • Cell-level encryptioncan be used to encrypt specific columns or even cells of data with different encryption keys.
  • 3rd party server-side encryption or encryption outside of SQL Server via Encrypting File System (EFS folders), EFS is usually not recommended by Microsoft due to the way I/O’s are forced to be performed synchronously and how security works and ACL changes impacts the data availability.
  • BitLocker volumes.
  • SQL Server Always Encrypted (see Always Encrypted).

SQL Server’s Transparent Database Encryption (TDE) protects data-at-rest by encrypting all the user data in a database’s data files (.mdf and .ndf’s), in the log file (.ldf), a database snapshot (.ss), and in the database backup files (.bak, .trn). Tempdb is also encrypted when at least one database is enabled for encryption. TDE does not require applications to be changed to gain this protection. Transparent data encryption (TDE) performs real-time I/O encryption and decryption of the data and log files. It is also possible to use Extensible Key Management solutions to manage TDE keys.

To simplify the use and management of TDE it is possible to use or share the same key (or TDE “thumbprint”) across multiple servers, especially for High Availability and Disaster Recovery. The TDE certificate must be installed on all servers:

  • in which the database is a member of a database mirroring session
  • or which are replicas of an Always On Availability Group (AG)

Note, the key can be altered at any time with SQL Server Management Studio (SSMS) or T-SQL.

There are four steps to enable TDE on a user database:

  1. Create the DMK. If it does not already exist, create the Database Master Key (DMK) in the master database.

[sql]IF NOT EXISTS (SELECT 1 FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Password';[/sql]

  1. Create the TDE certificate in the master database. The name of the TDE certificate should be a descriptive name. The “WITH SUBJECT” parameter is optional.

[sql]CREATE CERTIFICATE DatabaseTDECertificate WITH SUBJECT = 'TDE Certificate for the Database';[/sql]

  1. Create the DEK in the user database. It’s possible to encrypt the DEK with an asymmetric key instead of your TDE certificate, but doing so will make it difficult or impossible to restore the database backup to another instance of SQL Server. There are also other encryption algorithms that can be used, but AES_256 is the strongest option. While weaker algorithms may provide faster performance, using TDE sacrifices performance for maximum security, so the strongest possible DEK might as well be used.

[sql]CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE DatabaseTDECertificate[/sql]

  1. Set encryption on. Just creating all the keys and the certificate does not enable TDE; The ALTER DATABASE command must be used to complete the process.

[sql]ALTER DATABASE [DatabaseName] SET ENCRYPTION ON[/sql]

Backup the TDE Certificate

TDE uses certificate (thumbprints) and a database encryption key (DEK) to encrypt the database. It is important to back up the customer's unique TDE configuration, certificates, and passwords. The .cer, .pvk, and passwords should be backed up immediately after implementation of TDE (and at regular intervals for safety).   Since the certificate may be critical for a recovery of the database, these backups should be stored offline and off-site.  The following example backups up the certificate and private key
[sql]BACKUP CERTIFICATE DatabaseTDECertificate
TO FILE = 'C:\Folder\DatabaseTDECertificate.cer'
WITH PRIVATE KEY ( FILE = 'C:\Folder\DatabaseTDECertificate.pvk', ENCRYPTION BY PASSWORD = 'Password');[/sql]
When switching certificates for a database, it is important to retain older keys/certificates for some time as portions of the log may still be encrypted with the older certificate. Use the following to switch a certificate:
[sql]USE [DatabaseName];
ALTER DATABASE ENCRYPTION KEY ENCRYPTION BY SERVER CERTIFICATE [NewCertificateName][/sql]

Always Encrypted

Always Encrypted (new in SQL Server 2016) is a feature designed to protect sensitive data, such as credit card numbers or national identification numbers (e.g. U.S. social security numbers), stored in Azure SQL Database or SQL Server databases. Always Encrypted allows clients to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine (either Cloud-based SQL Database or SQL Server on-premises). Additionally, Always Encrypted provides a separation between those who own the data (and can view it) and those who manage the data (but should not have access). It ensures that sensitive data is never exposed in plain-text inside the database, protecting it from DBAs and admins of the machine hosting the system. This allows organizations to encrypt data at rest and in use for storage in Azure, to enable delegation of on-premises database administration to third parties, or to reduce security clearance requirements for their own DBA staff. There are some nuances with Always Encrypted which need to be considered including certain column types (e.g. text, image, and ntext) and transactional replication are not supported.

Always Encrypted makes encryption transparent to applications. An Always Encrypted-enabled driver installed on the client computer achieves this by automatically encrypting and decrypting sensitive data in the client application. The driver encrypts the data in sensitive columns before passing the data to the Database Engine, and automatically rewrites queries so that the semantics to the application are preserved. Similarly, the driver transparently decrypts data, stored in encrypted database columns, contained in query results. The customer can use Always Encrypted feature of SQL 2016 to meet the encryption at-rest and in-transit requirements of the SQL STIG.

The process for using Always Encrypted is as follows:

1.  Create a column master key for client applications that use the MSSQL_CERTIFICATE_STORE provider
[sql]CREATE COLUMN MASTER KEY ColMasterKey
WITH (
KEY_STORE_PROVIDER_NAME = 'MSSQL_CERTIFICATE_STORE',
KEY_PATH = 'Current User/Personal/f2260f28d909d21c642a3d8e0b45a830e79a1420'
);[/sql]
2. Create a column encryption key
[sql]CREATE COLUMN ENCRYPTION KEY ColEncryptionKey
WITH VALUES (
COLUMN_MASTER_KEY = ColMasterKey,
ALGORITHM = 'RSA_OAEP',
ENCRYPTED_VALUE = [The encrypted CEK value BLOB]
);[/sql]
3. Create/modify table to reference column encryption key for any fields to be encrypted
[sql]CREATE TABLE Payroll (
EmployeeName nvarchar(60)
COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ColEncryptionKey,
ENCRYPTION_TYPE = RANDOMIZED,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
SSN varchar(11)
COLLATE Latin1_General_BIN2 ENCRYPTED WITH (COLUMN_ENCRYPTION_KEY = ColEncryptionKey,
ENCRYPTION_TYPE = DETERMINISTIC ,
ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'),
Department int NULL
);[/sql]

Channel Encryption

In order to ensure data-in-transit (login/data packets) is encrypted, SQL Server supports TLS (Transport Layer Security) and SSL (Secure Sockets Layer).  When a SQL Server is configured with TLS or SSL and a client requests an encrypted connection, an initial handshake takes place to negotiate the cipher suite for future communications.  Once agreed, SQL sends its TLS/SSL certificate to the client, which the client must validate and then trust against its copy of the Certificate Authority certificate.  A secure connection is established if the certificate is trusted and meets all requirements.

The latest SQL STIG for SQL 2016 requires using TLS as Microsoft has changed the security guidance to move to TLS 1.2 for secure communications.The STIG requires a DOD Certificate to enhance the encryption. Use the following to setup channel encryption:

  1. Install a certificate in the Windows certificate store of the server computer.
  2. Click Start, in the Microsoft SQL Server program group, point to Configuration Tools, and then click SQL Server Configuration Manager.
  3. Expand SQL Server Network Configuration, right-click the protocols for the server you want, and then click Properties.
  4. On the Certificate tab, configure the Database Engine to use the certificate. If enabling for a Failover Cluster Instance (FCI), the Certificate's thumbprint must be added to the registry for each node in the FCI.
  5. On the Flags tab, view or specify the protocol encryption option. The login packet will always be encrypted. When the ForceEncryption option for the Database Engine is set to Yes, all client/server communication is encrypted and clients that cannot support encryption are denied access. When the ForceEncryption option for the Database Engine is set to No, encryption can be requested by the client application but is not required. SQL Server must be restarted after you change the ForceEncryption setting.
  6. To test if encryption is working you should get TRUE, when running the following script.

[sql]SELECT encrypt_option FROM sys.dm_exec_connections WHERE sessionId = @@SPID[/sql]

Encryption Best Practices

Best practices for data encryption:

  • Encrypt high-value and sensitive data.
  • Use symmetric keys to encrypt data, and asymmetric keys or certificates to protect the symmetric keys.
  • Password-protect keys and remove master key encryption for the most secure configuration.
  • Do not delete pre-provisioned system certificates in the master database
  • Always back up the service master key, database master keys, and certificates by using the key-specific DDL statements.
  • Always back up your database to back up your symmetric and asymmetric keys.
  • TDE is recommended for encrypting existing applications or for performance sensitive applications.
  • Cell-level encryption can be used for defense in depth both for a database encrypted by TDE and for limited access control using passwords.
  • Use Always Encrypted to encrypt sensitive data inside client applications and never reveal the encryption keys to the Database Engine or Database Administrators
  • Retain older keys for some time. Portion of log may still be encrypted with the older certificate.
  • Dynamic data masking limits (DDM) sensitive data exposure by masking it to non-privileged users. It can be used to greatly simplify the design and coding of security in your application.

Conclusion

This completes the facet and considerations related to securing SQL Server Encryption, Key Management, And Data-At-Rest Encryption. Please stay tuned for the next topic in the series discussing authentication and authorization for SQL Server.