Transparent Data Encryption (TDE)

• From SQL 2019 TDE is available in Standard Edition and Enterprise Edition. For earlier versions this was an Enterprise Edition only feature.

• The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE.

Check

Check existing certificates and encrypted databases

use [master]

go

SELECT * 

  FROM sys.symmetric_keys

SELECT db.name,

       db.is_encrypted,

       dm.encryption_state,

       dm.percent_complete,

       dm.key_algorithm,

       dm.key_length

  FROM sys.databases db

  LEFT OUTER JOIN sys.dm_database_encryption_keys dm ON db.database_id = dm.database_id

 ORDER BY name

/* Certificates */

SELECT * 

  FROM sys.certificates

SELECT DB_NAME(database_id) AS dbName, * 

  FROM sys.dm_database_encryption_keys 

 WHERE encryption_state = 3 

 ORDER BY 1

SELECT DB_NAME(dek.database_id) as dbName,

       c.name AS CertName,

       dek.set_date,

       c.expiry_date,

       DATEADD(year, 2, c.start_date) AS [Two Years After Create]

  FROM sys.dm_database_encryption_keys dek

  LEFT JOIN sys.certificates c ON dek.encryptor_thumbprint = c.thumbprint

ORDER BY 1

Encryption State

SELECT DB_NAME(database_id) AS dbName, encryption_state

  FROM sys.dm_database_encryption_keys 

 ORDER BY 1

• 0 = No encryption key present

• 1 = Unencrypted

• 2 = Encryption in progress

• 3 = Encrypted

• 4 = Key Change in Progress

• 5 = Decryption in Progress

• 6 = The certificate or asymmetric key that is encrypting the database encryption key is being changed

Rotate Encryption Keys

Create New Certificate

USE master

go

CREATE CERTIFICATE newTDECert WITH SUBJECT = 'NEW TDE Certificate', expiry_date = '2020-06-19' /* 2 years from today */

go

BACKUP CERTIFICATE newTDECert 

TO FILE = 'G:\SQLBackup\newTDECert.cer.bak'

WITH PRIVATE KEY

(FILE = 'G:\SQLBackup\newTDECert_Key.pvk.bak',

ENCRYPTION BY PASSWORD = 'Use_Strong_Password_Neccessary_To_Restore_Later')

Rotate TDE Encryption Key

/* work in context of encrypted database */

USE [WorkspaceDB]

go

ALTER DATABASE ENCRYPTION KEY

    ENCRYPTION BY SERVER CERTIFICATE newTDECert

Re run Check scripts above to confirm rotation has been successful

Encryption Key Backups

• Store backup of encrypted keys on target server (e.g. local backup folder, but only IF it is considered secure. If you run backups to tape this has the added side-effect of storing the encryption key with the backup.. great for restoring, not so great if the tape is stolen). Alternatively keep it in a secure central certificate store.

• Delete backups of old certificate but always keep at least one backup copy of every certificate you use until the last database backup that may possibly use them has been purged. If you ever need to restore a database that used encryption, you’ll need the certificate that was in effect at the time the backup was created.

Drop Old Certificate

USE master

go

DROP CERTIFICATE ExpiredTDECert

Encrypt Database

Create master key and certificate (if does not exist)

USE [MASTER]

go

--Service Master Key is backed up with master database but we can also make separate backup

BACKUP SERVICE MASTER KEY

TO FILE = 'F:\SQLBackup\service_master_key.bak'

ENCRYPTION BY PASSWORD = 'Use_Strong_Password12#34_a'

go

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Use_Strong_Password12#34_a'

GO

--Database Master Key is backed up with database (master if using TDE) but we can also make its backup

BACKUP MASTER KEY

TO FILE = 'F:\SQLBackup\master_key.bak'

ENCRYPTION BY PASSWORD = 'Use_Strong_Password12#34_a'

go

CREATE CERTIFICATE TDECert1 WITH SUBJECT = 'TDE Certificate', expiry_date = '2020-06-19' /* 2 years from today */

Create key in your database and make backup of certificate and key

use [WorkspaceDB]

go

CREATE DATABASE ENCRYPTION KEY WITH 

ALGORITHM = AES_256 ENCRYPTION BY 

SERVER CERTIFICATE TDECert1

/*

You will get warning: 

The certificate used for encrypting the database encryption key has not been backed up. 

You should immediately back up the certificate and the private key associated with the certificate. 

If the certificate ever becomes unavailable or if you must restore or attach the database on another server, 

you must have backups of both the certificate and the private key or you will not be able to open the database.

*/

go

USE [master]

GO 

BACKUP CERTIFICATE TDECert1 

TO FILE = 'F:\SQLBackup\certificate_TDECert1.cer.bak'

WITH PRIVATE KEY

(FILE = 'F:\SQLBackup\certificate_TDECert1_Key.pvk.bak',

ENCRYPTION BY PASSWORD = 'Use_Strong_Password_Neccessary_To_Restore_Later')

Encrypt your database

USE WorkspaceDB

go

/* that will start encryption in the background */

ALTER DATABASE  WorkspaceDB SET ENCRYPTION ON 

go

/* 

Database become encrypted when is on the list returned by following command, database can be used form DML during that process 

Encryption_state = 3 is encrypted

*/

SELECT DB_NAME(database_id) as dbName, * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3 

Encryption Key Backups

• store backup of encrypted keys on target server (e.g. local backup folder, but only IF it is considered secure) or in a secure central certificate store.

• Delete backups of old certificate but always keep at least one backup copy of every certificate you use until the last database backup that may possibly use them has been purged. If you ever need to restore a database that used encryption, you’ll need the certificate that was in effect at the time the backup was created.

Decrypt Database

Following steps have been successfully performed on MS SQL 2008R2 SP3

• Check existing certificates and encrypted databases:

use master

go

select * from sys.symmetric_keys

select * from sys.databases where is_encrypted=1

SELECT * FROM sys.certificates

/* encryption_state = 3 is encrypted */

SELECT DB_NAME(database_id) as dbName, * FROM sys.dm_database_encryption_keys WHERE encryption_state = 3

• Decrypt database and drop key

--work in context of database you are going to decrypt

USE WorkspaceDB

go

ALTER DATABASE WorkspaceDB SET ENCRYPTION OFF

go

/* 

Database become decrypted when is on the list returned by following command, database can be used form DML during that process 

Encryption_state = 1 means not encrypted

*/

SELECT DB_NAME(database_id) as dbName, * FROM sys.dm_database_encryption_keys WHERE encryption_state = 1

go

--work in context of database you are going to decrypt

USE WorkspaceDB  

go

/* execute only if above command returns encryption_state = 1 - non encrypted  */

DROP DATABASE ENCRYPTION KEY

• Perform full backup and TEST restore on DIFFERENT server

• If there is no encrypted databases on server then you can drop certificate.

USE master

go

DROP CERTIFICATE TDECert1

go

DROP MASTER KEY

• Delete backups of old certificate but always keep at least one backup copy of every certificate you use until the last database backup that may possibly use them has been purged. If you ever need to restore a database that used encryption, you’ll need the certificate that was in effect at the time the backup was created.

• Delete TDE passwords from your secure password store (if you have one).

Backup/Restore

• Use standard restore procedure if encrypted database with TDE needs to be restored with different name to original server

• Use encrypted database restore procedure if database encrypted with TDE needs to be copied to different server 

Asymmetric Keys

If asymmetric keys are in use they should use at least a 2048-bit encryption algorithm.

use [<database_name>]

GO

SELECT name AS Key_Name, key_length

FROM sys.asymmetric_keys;

GO

Extensible Key Management (EKM)

Check

TODO

Enable

sp_configure 'show advanced', 1  

GO  

RECONFIGURE  

GO  

sp_configure 'EKM provider enabled', 1  

GO  

RECONFIGURE  

GO  

Usage

Bibliography