Protecting Patients through Background Checks
Database Encryption
Background Check System
IT Deployment Guide
Version 01
2/19/2015
Table of Contents
1Overview
2Technical Overview
3Key Terms and Definitions
4Steps to Encrypt Data
4.1Create Database Master Key (DMK)
4.2Create Certificate for DEK
4.3Create Backup of Certificate
4.4Create Database Encryption Key (DEK)
4.5Enable TDE
4.6Enable Column Level Encryption
5Grant Rights to Encryption Information
6Steps to Move Your Encrypted Database
1Overview
This document provides an overview of database encryption utilizing Microsoft’s Transparent Data Encryption (TDE), as well as a step-by-step process to add TDE encryption to your Background Check System (BCS) database.
2Technical Overview
TDE performs real-time input/output encryption and decryption of the data and log files. The encryption uses a Database Encryption Key (DEK), which is stored in the database boot record for availability during recovery. The DEK is a symmetric key secured by using a certificate stored in the master database of the server or an asymmetric key protected by an Extensible Key Management (EKM) module. TDE protects data "at rest,"meaning the data and log files. It provides the ability to comply with many laws, regulations, and guidelines established by various industries. This enables software developers to encrypt data by using Advanced Encryption Standard (AES) and Triple Data Encryption Standard (3DES) encryption algorithms without changing existing applications.
3Key Terms and Definitions
BCS Encryption Term / DescriptionTransparent Data Encryption (TDE) / TDE provides full database level encryption, performing real-time input/output encryption and decryption of the data and log files.
Encrypting File System / Encrypting file systems provide operating system level encryption.
Database Master Key (DMK) / DMK is an encryption key stored at the user database level which protects certificates and asymmetric keys.
Master Key vs. Certificate / The Master Key is a symmetric key that is used to protect all encryption keys and certificates in the database. The certificate is the means by which the database is encrypted.
VeriSign/Symantec™
SSL Certificates vs. MicrosoftSQL Server Certificates / VeriSign/Symantec are companies that sell SSL Certificates. These certificates are insured by the companies and guarantee that your website cannot be hacked. The MicrosoftSQL Server Certificate is a certificate generated by SQL Server and is not guaranteed by Microsoft. It is a “use at your own risk” certificate.
4Steps to Encrypt Data
4.1Create Database Master Key (DMK)
Create a DMK for the master database. Ensure that the DMK is encrypted by the Service Master Key (SMK) and that your password meets Microsoft(MS) Windows policy requirements for complexity.
use master
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourpassword';
4.2Create Certificate for DEK
If using a MS SQL Server Certificate, create a new certificate for use as the DEK protector. (Note: If purchasing a certificate guaranteed by a vendor such as VeriSign, follow the vendor’s steps for certificate implementation rather than the code below.)
use master
CREATE CERTIFICATECnaIaBcsCertificateWITH SUBJECT = 'TDECertificate'
“CnaIaBcsCertificate” represents the certificate name. The certificate is stored under this name in the key store. Additionally, the certificate name is generally used by system processes to identify the certificate when necessary. Give your certificate a name of your choice.
“TDE Certificate” represents the certificate subject. The subject is additional information about the certificate. For a website, the subject is usually the domain name. In the case of the BCS certificate, give your certificate a subject of your choice.
4.3Create Backup of Certificate
Use your private key to create a backup of your certificate. The private key is an asymmetric key used to decrypt data. It is created by the operating system and is used to protect the symmetric key, which is used by SQL Server to encrypt sensitive data that is stored in SQL Server. To ensure against data loss, save both your certificate backup and the file that contains your private key in secure locations.
BACKUP CERTIFICATECnaIaBcsCertificate TO FILE = 'C:\Projects\TDEKeys\StoredCert\storedcert'
WITH PRIVATE KEY
(
FILE = 'C:\Projects\TDEKeys\StoredKey\storekey',
ENCRYPTION BY PASSWORD = 'yourpassword'
);
“CnaIaBcsCertificate” represents the certificate name. Use the name that you gave your certificate when you created it in section 4.2 above.
“C:\Projects\TDEKeys\StoredCert\storedcert” represents the directory path to and the file name of the backup certificate. Ensure that you provide a valid directory path, which can be a local path or a universal naming convention (UNC) path to a network location.
“C:\Projects\TDEKeys\StoredKey\storekey” represents the directory path to and the file name of the private key. Ensure that you provide a valid directory path, which can be a local path or a UNC path to a network location.
4.4Create Database Encryption Key (DEK)
Create the DEK and encrypt it with the certificate created in section 4.2 above.
Use [NameofBCSDatabase]
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM= AES_256
ENCRYPTION BY SERVER CERTIFICATECnaIaBcsCertificate
“[NameofBCSDatabase]” represents a placeholder for your BCS Database Name.
“CnaIaBcsCertificate” represents the certificate name. Use the name that you gave your certificate when you created it in section 4.2 above. The certificate is referenced as a server certificate to distinguish it from other certificates that may be stored in the user database.
4.5Enable TDE
Use the command below to start a background thread (referred to as the encryption scan), which will run asynchronously and encrypt the entire database file.
ALTER DATABASE [NameofBCSDatabase] SET ENCRYPTION ON
“[NameofBCSDatabase]” represents a placeholder for your BCS Database Name.
4.6Enable Column Level Encryption
Use the commands below to setup column level encryption on specific tables in the database.
Use[BCS Table Name]
CREATE MASTER KEY ENCRYPTION BY PASSWORD='ABCMS123!';
use[BCS Table Name]
CREATE CERTIFICATE CnaIaBcsCertificate WITH SUBJECT='TDE Certificate'
CREATE SYMMETRIC KEY CnaIaBcsSymmetricKey
WITH ALGORITHM= AES_256
ENCRYPTION BY CERTIFICATE CnaIaBcsCertificate;
“[BCS Table Name]” represents a placeholder for the name of the table that you want to encrypt at the column level.
5Grant Rights to Encryption Information
Use the commands below to grant access to certain encryption information to specific users of the system.
GRANT VIEW DEFINITION ON CERTIFICATE::CnaIaBcsCertificateTOYourBCSUser;
GO
GRANT VIEW DEFINITION ON SYMMETRIC KEY::CnaIaBcsSymmetricKeyTOYourBCSUser;
GO
GRANT CONTROL ON CERTIFICATE::CnaIaBcsCertificateTOYourBCSUser;
GO
6Steps to Move Your Encrypted Database
In the event that you need to move your encrypted BCS database due to installing new servers or re-doing the setup of your network, follow the steps provided below:
- Detach the BCS (encrypted) database.
- Copy the BCS (encrypted) database from the source server to the same location on the destination server.
- Copy the backup of the server certificate and the private key file from the source server to the same location on the destination server.
- Create a database master key on the destination instance of the SQL Server. It does not have to be the same password as the original server:
usemaster
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'yourpassword';
- Recreate the server certificate by using the original server certificate backup file:
CREATECERTIFICATECnaIaBcsCertificate
FROMFILE='C:\Projects\TDEKeys\StoredCert\storedcert'
WITH PRIVATE KEY
(
FILE='C:\Projects\TDEKeys\StoredKey\storekey',
DECRYPTION BY PASSWORD='yourpassword’
);
GO - Attach the BCS (encrypted) database. (Note: Unless you also bring over the transaction log file, you will need to create a new log by leaving the path to it blank.)
Database EncryptionPage 1 of 9
Version 01 | 2/19/2015 | IT Deployment Guide | Audience: State IT Staff