---
title: SQL Server containers with TDE encryption
description: Instructions for creating containers and clones when the source databases utilizes TDE encryption.
---

 

 

 TECHNICAL DOCS

# 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](https://23302346.hs-sites.com/doc-tde#get-cred)

#### [Installing the credentials on the Windocks server](https://23302346.hs-sites.com/doc-tde#install-cred)

#### [Setting up the .sqlsys script](https://23302346.hs-sites.com/doc-tde#sqlsys)

#### [Using the .sqlsys script in your dockerfile](https://23302346.hs-sites.com/doc-tde#dockerfile)

#### [Building the image](https://23302346.hs-sites.com/doc-tde#image)

 

 

## **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');

 

 

[Back to Top ](https://23302346.hs-sites.com/doc-tde#top) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

 

 

## Installing the credentials on the Windocks server

1. On the server running Windocks:
2. Open services.msc, stop the Windocks service
3. Start the SQL Server instance listed in \windocks\config\node.conf
4. In Services, get the name of the account running this SQL Server instance, for example NT SERVICE\MSSQLSERVER
5. 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
6. 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');

1. Stop the SQL Server instance and change it to a disabled state
2. Restart the Windocks service.

[Back to Top ](https://23302346.hs-sites.com/doc-tde#top) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

 

 

## **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;

 

 

[Back to Top ](https://23302346.hs-sites.com/doc-tde#top) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

 

 

## **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

 

 

[Back to Top ](https://23302346.hs-sites.com/doc-tde#top) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

 

 

## 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](https://23302346.hs-sites.com/rest-api). Containers delivered from this image will support TDE encrypted databases, based on the source certificate and key.

[Back to Top ](https://23302346.hs-sites.com/doc-tde#top) [Back to Technical Docs Gallery ](https://23302346.hs-sites.com/resources)

## Extend CI/CD to your data layer

[GET A DEMO ](https://23302346.hs-sites.com/schedule-demo) [TRY FREE ](https://23302346.hs-sites.com/try-free)