Docker SQL Server Containers and Transparent Data Encryption (TDE)

It is fun to work with customers to modernize SQL Server workflows that involve TDE encryption, Extensible Key Managers (EKM), and other capabilities. This article looks at TDE support for Microsoft’s Docker SQL Server containers, as well as Windocks SQL Server containers. Windocks is an independent port of Docker’s source to Windows, and provides SQL Server database cloning that is used to support any SQL Server environment (containers or conventional instances). Full disclosure, I am a principal at Windocks, and these methods are used by a number of enterprises.

We’ll look at a 500 GB SQL Server TDE enabled database in the form of SQL Server database clones. Clones deliver full read/write operations, with databases delivered in seconds, and use less than 40 MB of storage. Clones are ideal for supporting development, QA, as well as reporting and BI, and are a great match for SQL Server containers.

TDE enabled database clones

The SQL Server cloned environments can include a single database or scores of databases, some of which may be TDE enabled and others not. The cloned environment is built with appropriate user/group permissions, and includes data masking and other preparations during the image build. A single full byte copy is built as a parent image, which is cloned and delivered in seconds. Database clones are based on Windows Virtual Hard Drives on SMB or NFS network attached file shares.

When we restore an encrypted backup, or mount a TDE enabled database to either Windocks SQL Server containers or Docker SQL Server containers, we encounter SQL Server error 15581, “create or restore the Master Encryption Key prior to performing this operation.” In order to work with a TDE enabled database image, the containers are enabled with the Master Encryption Key prior to mounting the TDE enabled database.

Windocks SQL containers and TDE

Windocks SQL Server containers are created by cloning a host installed SQL Server instance, and inherit the parent instance Master database configuration which includes the Master Encryption Certificates. The new container, however, requires the Master Encryption Certificate be refreshed, to resolve the problem cited above. We do this by applying the following SQL script.

Windocks includes control over the order of SQL Server script operations. Scripts with .sqlsys file extensions are run prior to mounting the database clone. This allows the Master Encryption Certificate to be regenerated prior to attempting work with the TDE enabled database.

The complete process is accomplished with a dockerfile that is used to create an image, and that includes support for applying the “TDE Certificate refresh” script for containers at run time. The key to the Dockerfile is an environment variable (ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1), which identifies scripts that are applied at container run time.

The image is created by building: >docker build –t (image) \path\to\dockerfile

FROM mssql-20XX
COPY tderefresh.sqlsys .
RUN tderefresh.sqlsys
SETUPCLONING FULL customers c:\windocks\samples\TestFastClonefromFullBackup
ENV USE_DOCKERFILE_TO_CREATE_CONTAINER=1
COPY tderefresh2.sqlsys .
RUN tderefresh2.sqlsys

The image is confirmed by running a >docker images command, and containers are provisioned by running >docker create , followed by >docker start , or >docker run –d , or through the Windocks web UI.

The first steps are run during the build, and everything from the ENV command onward are saved, and applied at container run time. The result is a clonable image that includes the TDE encrypted database, and each container provisioned is made ready for the encrypted database by running the scripts prior to mounting the database.

Protecting sensitive credentials

Scripts can involve use of sensitive credentials, which can be protected using the following steps:

1. Stop the Windocks service, and start the SQL Server instance used for container operations (as configured in the \windocks\config\node.conf file.
2. Add the sensitive user credentials as a SQL Server login
3. Add the new SQL Server user to the SysAdmin Group (the user needs permissions to the databases).
   
   EXEC sp_addsrvrolemember 'newuser', 'sysadmin';

1. Return the SQL Server instance to “manual, off” state
2. Using Explorer, navigate to \Windocks\bin directory, open a cmd prompt and enter “encrypt”
3. Enter the password at the prompt and return
4. The encrypted password is recorded in the encrypted.txt file in the same directory. Open the file using notepad, and copy the complete password into \windocks\config\node.conf as shown below. The syntax for the encrypted password must be exactly as shown to support container cleanup and other operations that refer to the password.
   SQLRUNAS_PASSWORD1=”paste encrypted password here”
5. Save the updated node.conf file
6. Restart the Windocks service

Conclusions and next steps:

Combining SQL Server containers with database cloning provides fast delivery of complete environments, for development, QA, reporting, and BI. A complete configured environment with a Terabyte class database can be delivered in 45 seconds, with developer self-service and a significant start toward modernizing full stack software development and delivery.

The approach outlined in this article delivers a number of benefits:

• SQL Server environments deliver up-to-date production data.
• Production issues are quickly debugged and fixed.
• Dev/QA operations are simplified with containers on a shared host, with fewer VMs used.
• Containers and images are portable and run wherever Windows Servers are supported.

In addition to delivering containers, Windocks is also a general purpose SQL Server cloning tool. Customers use clones with conventional SQL Server instances as well as containers.

To learn more about SQL Server database cloning, and Windocks database cloning for Docker SQL server containers, download the free Windocks Community Edition