SQL Server containers with TDE encryption

TDE encryption involves a private encryption key and certificate on the Master database. Start by restoring the encryption certificate to the SQL Server instance used by Windocks to create SQL Server containers (the instance name is listed in \windocks\config\node.conf). Items needed include the source encryption certificate, private key, and password.
DB_Cert.cer
DB_Cert.pvk
Strong_Password_For_Certificate

You can get these by running the following SQL on the source server, where the TDE database lives (typically production).

USE master; GO BACKUP CERTIFICATE TDE_CERT_For_MyData TO FILE = 'C:\temp\TDE_Cert_For_MyData.cer' WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk', ENCRYPTION BY PASSWORD='Strong_Password_For_Certificate');

On the server running Windocks,
Open services.msc, stop the Windocks service

Start the SQL Server instance listed in \windocks\config\node.conf

In Services, get the name of the account running this SQL Server instance, for example NT SERVICE\MSSQLSERVER

In File Explorer, navigate to the directory containing the private key and certificate. Provide full permissions on these files and directory to the account running the SQL Server instance

Start SQL Management Studio, connect to that instance and run the following scripts

-- Run this script on the installed SQL Server on the machine running Windocks

USE master;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Strong_Password_For_Master_Key';

-- Restore the source certificate and private key.
USE master;
CREATE CERTIFICATE TDECert FROM FILE = '\path\DB_Cert.cer’ WITH PRIVATE KEY (FILE = N'\path\DB_Cert.pvk', DECRYPTION BY PASSWORD = 'Strong_Password_For_Certificate');


Stop the SQL Server instance and change it to a disabled state

Restart the Windocks service.

Create a file named tdesetup.sqlsys. (The .sqlsys extension ensures that the script is run before any database specific attaches):
tdesetup.sqlsys

USE MASTER;
OPEN MASTER KEY DECRYPTION BY PASSWORD='Strong_Password_For_Master_Key';
ALTER SERVICE MASTER KEY FORCE REGENERATE;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
CLOSE MASTER KEY;


Create a file named dockerfile as follows:

dockerfile
FROM mssql-2017

COPY tdesetup.sqlsys .
# Run the tdesetup.sqlsys on the image so that that image can have the database(s)
RUN tdesetup.sqlsys

# This line sets up the image to deliver clones of the database to each container
SETUPCLONING FULL DbYouWantToEncrypt C:\windocks\dbbackups\pathtobackup.bak

# Commands below are run on each container create

ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1

# Run the tdesetup.sqlsys on each container created from this image so that the container database(s) are accessible
RUN tdesetup.sqlsys


In a command line on the Windocks server, build the image. You may also build with the web application or via REST API
docker build -t yourimagename path\to\directory\containing\dockerfile

After that you can create containers from the image using docker run -d yourimagename or from the web application or from the REST API. Containers delivered from this image will support TDE encrypted databases, based on the source certificate and key.