Microsoft SQL Server Encryption of Data-at-Rest

Why do we need Encryption of Data-at-Rest?

For data sets that contain sensitive information there are numerous scenarios where the physical drive on which the data is stored should be encrypted. In the event that the physical drive is stolen, a malicious party may access the original data by attaching the disk to another host and then browse the data in the event it is not encrypted. Also consider a multi-tenant virtual environment such as the public cloud, where the physical host on which your VM is running may be compromised. This document describes different mechanisms provided by Microsoft for Windows Server operating systems to encrypt data-at-rest.

We will consider two solutions:

BitLocker Drive Encryption
1. Drive encryption feature included in Windows Server.
2. Protects at volume level, so when the database server is online, the volume is unlocked, though not decrypted.
3. If a malicious user gets access to the whole drive via an out of band mechanism, they still would not be able to attach and use it.
Transparent Data Encryption(TDE)
1. TDE can be used to encrypt SQL Server data files at rest
2. TDE can encrypt sensitive data in the database and protect the keys that are used to encrypt the data with a certificate
3. TDE performs real-time I/O encryption and decryption of data and log files to protect data at rest
4. If a malicious user gets access to the data file, they cannot use it without the protection keys

It is recommended by Microsoft to have a combined defense mechanism using both Bitlocker and TDE.

With only Bitlocker enabled - if the drive gets stolen, nobody can use the data without access to the key, but if a malicious attacker gained access to the drive via a network share or other services that access data from the drive, they could copy data (for example, an SQL Server backup file or a database file) unless it is locked from doing so, by the running SQL Server process. The copy of this file on the new drive is then not encrypted anymore.

With only TDE enabled - the database files themselves are encrypted. Therefore, if either file is copied or stolen via a network, as in the example above, the attacker cannot read it. But if the attacker gains access to the whole drive, including SQL Server, he can start SQL Server and read the data using SQL, because SQL Server is performing the decryption for him.

Now if both BitLocker and TDE are used concurrently, both data theft vectors are mitigated. Both technologies complement each other, and it is recommended that you use BitLocker together with TDE for an in depth defense.

Source

Enable Bitlocker on Windows Server:

Please follow these steps to configure and enable Bitlocker on Windows Server (The attached screenshots are from Windows Server 2019).

Open Server Manager
Click Manage and select "Add Roles and Features":
Click Next:
Installation Type - select "Role Based or Feature Based installation" and click "Next":
Click on "Select a server from the server pool" and choose the server from the server pool. Click "Next":
Do not make any changes at the "Server Roles" screen, just click "Next":
In the "Select Features" page select "BitLocker Drive Encryption":
Click "Add Features" in the pop-up window and click "Next":
Enable "Restart the destination server automatically if required", click "Yes" on the pop-up screen and proceed with "Install":
The server may restart during the installation process. Wait for the installation to complete:
Once the installation is completed, open the BitLocker Management console:
Click "Turn on BitLocker" for the drive you wish to encrypt. Select "Use a password to unlock this drive" and provide a strong password. Click "Next":
Opt how you would like to back up the recovery key. For example, select "Save to a file". Provide the file name and save. Click "Next":
Choose how much of your drive to encrypt; Select encryption mode; Click "Start Encrypting":