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
Getting credentials from the source server
You can get the items needed 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');
Installing the credentials on the Windocks server
- 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.
Setting up the .sqlsys script
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;
Using the .sqlsys script in your dockerfile
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
Building the image
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.