SQL Server containers with encrypted columns
The following instructions describe how to encrypt a column on the installed SQL service used by Windocks, to create the SQL script to set up the decryption, use the SQL scripts in the dockerfile, and also how to read the encrypted column from the deployed containers or instances.
Encrypting a column
Stop the Windocks Service, start the installed SQL Server service that is used by Windocks. Run the following script in the installed SQL Server service.
-- Verify that there is a service master key
USE master;
SELECT * FROM sys.symmetric_keys WHERE name = '##MS_ServiceMasterKey##';
-- Create Database level Master Key, Certificate, and symmetric key
USE YourDatabaseName;
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘YourPassword’;
CREATE CERTIFICATE YourDatabaseCertificate WITH SUBJECT = ‘Column Protect’;
CREATE SYMMETRIC KEY YourSymmetricKey WITH ALGORITHM = AES_128 ENCRYPTION BY CERTIFICATE YourCertificate;
-- Create column to hold encrypted data and encrypt the sensitive data with symmetric key
ALTER TABLE YourTable ADD SensitiveColumnEncrypted varbinary(MAX) NULL;
OPEN SYMMETRIC KEY YourSymmetricKey DECRYPTION BY CERTIFICATE YourCertificate;
UPDATE YourTable SET SensitiveColumnEncrypted = EncryptByKey(Key_GUID(‘YourSymmetricKey'),SensitiveColumn) FROM YourTable;
CLOSE SYMMETRIC KEY YourSymmetricKey;
-- Remove SensitiveColumn since data is encrypted and now in SensitiveColumnEncrypted
ALTER TABLE Customer_data DROP COLUMN SensitiveColumn
Creating the .sql script to set up decryption
Create a file named setupdecryption.sql:
setupdecryption.sql
USE YourDatabaseName;
ALTER SERVICE MASTER KEY REGENERATE;
OPEN MASTER KEY DECRYPTION BY PASSWORD = ‘YourPassword’;
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
Using the .sql script in your dockerfile
Create the dockerfile as follows:
dockerfile
FROM mssql-2014
SETUPCLONING FULL YourDatabaseName C:\windocks\dbbackups\YourBackup.bak
COPY setupdecryption.sql .
#Run the setup decryption script each time a container is created from image
ENV USE_DOCKER_FILE_TO_CREATE_CONTAINER=1
RUN setupdecryption.sql
Building the image and creating the containers
In a command line on the Windocks server:
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
Reading the encrypted column
Connect to the container with SSMS and run the script below to decrypt the column:
decryptcolumn.sql
USE YourDatabaseName;
OPEN SYMMETRIC KEY YourSymmetricKey DECRYPTION BY CERTIFICATE YourCertificate;
SELECT CONVERT(varchar, DecryptByKey(SensitiveColumnEncrypted)) FROM YourTable;
CLOSE SYMMETRIC KEY YourSymmetricKey;