Knowledge Base

When to use TDE

In a scenario where the physical media (such as drives or backup tapes) is at risk of being stolen by a malicious third party who can then restore or attach the database and browse the data.

Note: If you want to limit user access to your Database, use SQL Server rights, not TDE.

SQL Server versions and editions that support TDE

SQL Server 2014 – Enterprise, Developer, and Evaluation editions
SQL Server 2012 – Enterprise, Developer, and Evaluation editions
SQL Server 2008 R2 – Datacenter, Enterprise, Developer, and Evaluation editions
SQL Server 2008 – Enterprise, Developer, and Evaluation editions

Script to enable TDE

— /////////////////////////////////////////// —
— Using Transparent Database Encryption (TDE) —
— /////////////////////////////////////////// —

— Create Master Key

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘Pa$$w0rd’
GO

— Create server certificate

CREATE CERTIFICATE SV32_ServerCertificate
WITH SUBJECT=‘Server Level Certificate’
GO

— Backup the certificate with the private key

BACKUP CERTIFICATE SV32_ServerCertificate
TO FILE=‘C:\DATABASES\SQLSRV2014_DEV\Backup\SV32_ServerCertificate.cer’
WITH PRIVATE KEY (FILE=‘C:\DATABASES\SQLSRV2014_DEV\Backup\TDE_DATABASE1.pvk’,ENCRYPTION BY PASSWORD=‘Pa$$w0rd’)

— Create Database Encryption Key (DEK)

USE TDE_DATABASE1
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM=AES_128

ENCRYPTION BY SERVER CERTIFICATE SV32_ServerCertificate
GO

— Encrypt database

ALTER DATABASE TDE_DATABASE1
SET ENCRYPTION ON
GO

How to check that TDE is enabled (1)

Try to restore a backup of your database on another SQL Server instance.

— Backup the encrypted database

BACKUP DATABASE TDE_DATABASE1
TO DISK=‘C:\DATABASES\SQLSRV2014_DEV\Backup\TDE_DATABASE1_encrypt.bak’

— Attempt to restore the encrypted backup on another SQL Server instance

USE master
GO

RESTORE DATABASE TDE_DATABASE1
FROM DISK=‘C:\DATABASES\SQLSRV2014_DEV\Backup\TDE_DATABASE1_encrypt.bak’
WITH
MOVE ‘TDE_DATABASE1’ TO ‘C:\DATABASES\SQLSRV2014_DEV_1\TDE_DATABASE1_encrypt.mdf’,
MOVE ‘TDE_DATABASE1_log’ TO ‘C:\DATABASES\SQLSRV2014_DEV_1\TDE_DATABASE1_encrypt.ldf’

This should produce an error like this:

Msg 33111, Level 16, State 3, Line 2
Cannot find server certificate with thumbprint ‘0xF31411FB327DADD88C5E7E7E737CF4F48E8BDEE6’.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.

To restore the database you have to restore the certificate with the private key (or create it)

— Create Master Key

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD=‘Pa$$w0rd’
GO

— Restore the certificate with the private key

BACKUP CERTIFICATE SV32_ServerCertificate
FROM FILE=‘C:\DATABASES\SQLSRV2014_DEV\Backup\SV32_ServerCertificate.cer’
WITH PRIVATE KEY (FILE=‘C:\DATABASES\SQLSRV2014_DEV\Backup\TDE_DATABASE1.pvk’,DECRYPTION BY PASSWORD=‘Pa$$w0rd’)
GO

How to check that TDE is enabled (2)

Check that the Encryption Enable property is set to True in the Database Properties -> Options -> Other options.

Script to disable TDE, remove DEK, certificate BKP, certificate and master key

— //////////////////////////////////////////// —
— Remove Transparent Database Encryption (TDE) —
— //////////////////////////////////////////// —

— Decrypt database

USE TDE_DATABASE1
GO
ALTER DATABASE TDE_DATABASE1
SET ENCRYPTION OFF
GO

— Delete Database Encryption Key (DEK)

DROP DATABASE ENCRYPTION KEY
GO

— Delete backup of certificate and the private key

EXEC sp_configure‘show advanced options’, 1
GO
EXEC sp_configure‘xp_cmdshell’, 1
GO
RECONFIGURE
GO
EXECUTE master.dbo.xp_cmdshell‘del C:\DATABASES\SQLSRV2014_DEV\Backup\SV32_ServerCertificate.cer’
EXECUTE master.dbo.xp_cmdshell‘del C:\DATABASES\SQLSRV2014_DEV\Backup\TDE_DATABASE1.pvk’
EXEC sp_configure‘show advanced options’, 0
GO
EXEC sp_configure‘xp_cmdshell’, 0
GO
RECONFIGURE
GO

— Delete server certificate

USE master
GO
DROP CERTIFICATE SV32_ServerCertificate
GO

— Delete master key

DROP MASTER KEY
GO