Summary
In my previous post, how to install and host SQL Server on Linux , I described how to install SQL Server using Docker on Linux with just a Docker Compose file. In this post, I explore things a bit further by turning on transparent data encryption (TDE) which addresses how to encrypt SQL at Rest.
A brief intro to Transparent Data Encryption (TDE)
Transparent Data Encryption (TDE) in SQL Server is a method where it is able to apply encryption at the file level. In other words, its encryption at rest. Backups, log files, all these will be encrypted. So, in the event of a server being compromised, those encrypted backups will hopefully be useless to the theives.
Aside from the obvious benefits of enabling TDE, it also addresses the GDPR requirement of having data encrypted at rest. So thats a plus.
How to enable Transparent Data Encryption
Enabling TDE on SQL Server in Docker or any SQL Server instance for that matter requires a two step process. 1) Create a master key and certifcate. 2) Turn it on. Thats it. If you’re running SQL Server on Azure however. You’ve got it easy. Super easy. It’s just a case of turning it on in the Azure portal. This transparent data encryption azure guide from Microsoft describes how.
N.B TDE is only available in Developer and Enterprise editions of SQL Server.
Create a Master key and Certificate
1. Create a master key. Connect to the SQL Docker instance that you wish to encrypt in either SQL Management Studio or Azure Data Studio and create a master key.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PoBj9MZunVVVH3V6sZdc';
GO
Be sure to use a strong password, as it enforces a strong password policy. I tend to use 1Password’s password generator or Lastpass’s password generator
Otherwise you will get this little error:
Msg 33064, Level 16, State 2, Line 3
Password validation failed. The password does not meet SQL Server password policy requirements because it is not complex enough. The password must be at least 8 characters long and contain characters from three of the following four sets: Uppercase letters, Lowercase letters, Base 10 digits, and Symbols.
2. Create a certificate
Create a certificate with an appropriate name. I’ve called mine LocalServerCertificate.
CREATE CERTIFICATE LocalServerCertificate WITH SUBJECT = 'Local Server Certificate';
GO
3. Turn on encryption
I’m enabling it for my test database local-encrypt-db, and be sure to specify the certificate LocalServerCertificate that was just created.
USE [local-encrypt-db]
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE LocalServerCertificate;
GO
ALTER DATABASE [local-encrypt-db]
SET ENCRYPTION ON;
GO
You may get the following warning such as:
Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.
This is okay, as we will be creating a backup of the certificate and key later.
4. Verify the certificate and key has been created.
SELECT [name], [principal_id], [algorithm_desc], [create_date]
from master.sys.symmetric_keys
Verify Encryption is turned on
In order to verify that the database is encrypted, we can run the following query:
SELECT *
FROM sys.dm_database_encryption_keys
WHERE encryption_state = 3;
The encryption_state=3 condition means that we are looking for encrypted databases. Sample output is below:
database_id encryption_state create_date regenerate_date modify_date set_date opened_date key_algorithm key_length encryptor_thumbprint encryptor_type percent_complete
----------- ---------------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- --------------- ----------- ------------------------------------------ ---------------- ----------------
2 3 2019-03-16 12:11:41.883 2019-03-16 12:11:41.883 2019-03-16 12:11:41.883 1900-01-01 00:00:00.000 2019-03-16 12:11:41.883 AES 256 0x ASYMMETRIC KEY 0
5 3 2019-03-16 12:11:41.750 2019-03-16 12:11:41.750 2019-03-16 12:11:41.750 2019-03-16 12:11:41.847 2019-03-16 12:11:41.750 AES 128 0x7F2020197C240B0934D37B035A01F9E9BE66A8C9 CERTIFICATE 0
It returns database id 5 and 2. To verify the actual database id, we can get the database id by running the following SQL query with the database name local-encrypt-db.
SELECT DB_ID('local-encrypt-db')
Also, another useful query below can be used as well:
select [name], is_master_key_encrypted_by_server, is_encrypted
from master.sys.databases
Backup the Master Key and Certificate
Aside from general good practice of backing up the Master key and Certificate, its required if we need to perform a backup and restore of an encrypted database on another server as it will need the original key and certificate.
1. Backup the certificate. Run this command to backup the certificate.
use master
GO
BACKUP CERTIFICATE LocalServerCertificate TO FILE = 'C:\sql\certs\local-server-certificate.cert'
WITH PRIVATE KEY ( FILE = 'C:\sql\certs\local-server-certificate.key' ,
ENCRYPTION BY PASSWORD = 'PoBj9MZunVVVH3V6sZdc' );
GO
Notice that I have specified the certificate name LocalServerCertificate and the path of where to save it, C:\sql\certs. As I’m using Windows and running Docker using Linux Containers, I just chose a local path.
If you check this local path after running the above, you will notice nothing will exist. This is because the volume is mapped between the host machine and the Docker container and its actually saved it in the Docker container itself.
2. Verify the backup certificates exist. We will use the Docker interactive command to inspect the contents of the SQL Docker container.
docker exec -it sql2017 "bash"
Then, navigate to sql\certs\ and two files should be there.
root@d3c895b431e6:/sql/certs# ls -la
drwxr-xr-x 2 root root 4096 Mar 16 12:49 .
drwxr-xr-x 3 root root 4096 Mar 16 12:49 ..
-rw-r----- 1 root root 709 Mar 16 12:49 local-server-certificate.cert
-rw-r----- 1 root root 1212 Mar 16 12:49 local-server-certificate.key
Type exit to exit Docker’s interactive mode.
3. Copy the files from the container to the host.
Now, we need to copy these files from the container to the host.
Ensure that you are no longer in Docker’s interactive mode, by typing exit if you haven’t done so already.
Then, using the cp command, we can copy the files from the Docker container to the host machine, where sql2017 is the name of the container.
docker cp sql2017:sql/certs/local-server-certificate.cert local-server-certificate.cert
docker cp sql2017:sql/certs/local-server-certificate.key local-server-certificate.key
I discovered this question on Stackoverflow - how to copy files from host to docker which was helpful in this situation.
Backup an Encrypted Database
Now that encryption is enabled, it’s a good idea to enable encryption on backups as well. In order to do this, we just need to specify the certificate that we created in the previous step when performing a backup.
1. Backup a desired database
The following script will perform a full backup and will save it in a directory called /var/opt/mssql/backup/.
DECLARE @filename NVARCHAR(256)
SET @filename = '/var/opt/mssql/backup/local-encrypt-db_' + FORMAT(GETUTCDATE(), 'yyyy-MM-dd-HHmmss.bak')
BACKUP DATABASE [local-encrypt-db]
TO DISK = @filename
WITH ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = LocalServerCertificate)
Notice that the certificate LocalServerCertificate was specified.
2. Verify the backup exists This command will verify that the backup was created.
RESTORE FILELISTONLY FROM DISK = '/var/opt/mssql/backup/local-encrypt-db_2019-03-16-133308.bak'
The following output should be shown.
LogicalName PhysicalName Type FileGroupName Size
------------------------ -------------------------------------------------- ---- ---------------- --------------------
local-encrypt-db /var/opt/mssql/data/local-encrypt-db.mdf D PRIMARY 8388608
local-encrypt-db_log /var/opt/mssql/data/local-encrypt-db_log.ldf L NULL 8388608
3. Copy the backup from the container to the host. Now the backup is created, we need to fetch it from the container.
docker cp sql2017:/var/opt/mssql/backup/local-encrypt-db_2019-03-16-133308.bak local-encrypt-db_2019-03-16-133308.bak
Restore an Encrypted Database
In order to restore an encrypted database, we need to first restore the original master key and certificates that were used to create it.
Restore the Master Key and Certificate
As we have the original password for the backed up certificates, we just need to run a few SQL commands to restore them.
1. Connect to a different SQL Server. Make sure that the version of this SQL server instance is the same or a greater version to the database backup.
2. Create the original master key Create the master key again with original same password.
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'PoBj9MZunVVVH3V6sZdc';
GO
2. Restore the certificate Use the following command to restore the original certificate.
USE master;
GO
CREATE CERTIFICATE LocalServerCertificate
FROM FILE = N'C:\sql\certs\local-server-certificate.cert'
WITH PRIVATE KEY (
FILE = N'C:\sql\certs\local-server-certificate.key',
DECRYPTION BY PASSWORD = 'PoBj9MZunVVVH3V6sZdc'
);
GO
3. Verify the certificate is installed We can again verify the certificate and master key has been created.
SELECT [name], [principal_id], [algorithm_desc], [create_date]
from master.sys.symmetric_keys
Restore the database backup
Now that we have the master key and certificate installed, we can perform a restore.
RESTORE DATABASE [local-encrypt-db-BACKUP] FROM DISK = N'/var/opt/mssql/certs/local-encrypt-db_2019-03-16-133308.bak' with REPLACE
Below is a sample output of the successful restore:
Processed 352 pages for database 'local-encrypt-db-BACKUP', file 'local-encrypt-db' on file 1.
Processed 2 pages for database 'local-encrypt-db-BACKUP', file 'local-encrypt-db_log' on file 1.
RESTORE DATABASE successfully processed 354 pages in 0.086 seconds (32.158 MB/sec).
Had the cerificate not been restored and a restore was attempted, you would have received the following error message:
Msg 33111, Level 16, State 3, Line 13
Cannot find server certificate with thumbprint '0x7F2020197C240B0934D37B035A01F9E9BE66A8C9'.
Msg 3013, Level 16, State 1, Line 13
RESTORE DATABASE is terminating abnormally.
Summary
In this post I demonstrated how its possible to enable encryption (TDE) in SQL Server which is running on a Docker container with just a few SQL commands. I also demonstated how to backup the encryption keys and also perform a back and restore of an encrypted database to ensure that its possible.
With the help of SQL Server Agents in Docker which were introduced in Feb 2018, its possible to automate the backups, which I hope to detail in a future post.