How to use QIE with TDE(Transparent Data Encryption) on MS SQL Server

Question

I need to configure my Sql Server to use TDE for QIE.  What do I need to do?

Answer 1

All the configuration needed to use TDE is done in MS SQL Server.  QIE does not need any configuration changes.  Sweet yeah!  

Below are the steps to configure MS SQL Server to use TDE and to turn it on for the qie database.  Note:  This includes instructions to create a new QIE database and QIE user which is helpful for a new install.  These steps can be skipped to turn on TDE for an existing QIE database.

-- Step One: Create the certificates in the master database
USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'UseStrongPasswordHere123';
GO
-- Choose a name. Note: This cert is used at the server level.
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
GO

-- We can check that it was created using
SELECT * FROM sys.certificates
GO

-- Step Two: Backup the certificate to a secure location
/*
The code below shows how to back up the certificate to a file in the file system.
This file can then be backed up or copied to another system to allow the
encrypted database to be restored.
*/
USE Master
GO

BACKUP CERTIFICATE MyServerCert
TO FILE = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MySQLCert' 
WITH PRIVATE KEY (file='C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\MySQLCertKey',
ENCRYPTION BY PASSWORD='UseStrongPasswordToBackupCertHere123')
GO
/*
Code to move a backed up certificate to a New SQL Server Instance
*/
/*
USE Master
GO

-- Create a new master key.
CREATE MASTER KEY ENCRYPTION
BY PASSWORD = 'UseStrongPasswordHere123'
GO

-- Restore the certificate.
CREATE CERTIFICATE MyServerCert
FROM FILE='c:\temp\MySQLCert'
WITH PRIVATE KEY (
FILE = 'c:\temp\MySQLCertKey',
DECRYPTION BY PASSWORD='UseStrongPasswordToBackupCertHere123')
GO
*/

-- Step Three: Create a new database
CREATE DATABASE qieWithTDE
GO

/*
Note: if you need to create the qie user and map it to the qieWithTDE database
*/

/*
CREATE LOGIN qie
WITH PASSWORD='qie', DEFAULT_DATABASE=qieWithTDE, DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF;
GO
*/

-- Create a user in a database for a given login
USE qieWithTDE;
GO

CREATE USER qie FOR LOGIN qie WITH DEFAULT_SCHEMA=[dbo]
GO

-- Make the qie user a member of following groups
USE qieWithTDE
exec sp_addrolemember 'db_backupoperator', 'qie';
exec sp_addrolemember 'db_datareader', 'qie';
exec sp_addrolemember 'db_datawriter', 'qie';
exec sp_addrolemember 'db_ddladmin', 'qie';
exec sp_addrolemember 'db_owner', 'qie';
GO

-- Step Four: Create database level encryption key
USE qieWithTDE;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO

-- Step Five: Turn on encryption
ALTER DATABASE qieWithTDE
SET ENCRYPTION ON;
GO

-- To monitor encryption progress you can use this query
SELECT db_name(database_id), encryption_state, percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
GO

-- Resources:
-- http://en.wikipedia.org/wiki/Transparent_Data_Encryption
-- http://msdn.microsoft.com/en-us/library/bb934049.aspx
-- http://sqlmag.com/database-security/using-transparent-data-encryption
-- http://www.databasejournal.com/features/mssql/transparent-data-encryption-tde-in-sql-server.html

Comments

https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<UseStrongPasswordHere>';
go
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate';
go
USE AdventureWorks2012;
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyServerCert;
GO
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON;
GO