TDE encryption

Transparent Data Encryption

It needs to generate a few certificates protected by a password, and then you can turn encryption on for a database.

When encryption is on, the data file itself is encrypted at page level, so even you lost the data file, others wont be able to decrypt it.

use master

--create master key

IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name = '##MS_DatabaseMasterKey##')

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

--Backup master key

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'password'

BACKUP MASTER KEY TO FILE = 'C:\Temp\your_database_master_key'

ENCRYPTION BY PASSWORD = 'password'

CLOSE MASTER KEY

--There is no TDE certificate defined

IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name = 'your_database_TDE')

CREATE CERTIFICATE your_database_TDE WITH SUBJECT = 'your_database_TDE'

--Now let's backup the TDE cert.

BACKUP CERTIFICATE your_database_TDE to file = 'C:\Temp\your_database_TDE'

WITH PRIVATE KEY

(

FILE = 'C:\Temp\your_database_TDE.prvk'

, ENCRYPTION BY PASSWORD = 'password'

);

USE [ONE_DATABASE]

--Check if the target database is encrypted or not

SELECT

name

, db.database_id

, ek.create_date

FROM sys.databases db

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

WHERE 

is_encrypted = 0

AND db.name = 'ONE_DATABASE'

--if not, create encryption key 

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_128

ENCRYPTION BY SERVER CERTIFICATE your_database_TDE;

--turn encryption on

ALTER DATABASE [ONE_DATABASE]

SET ENCRYPTION ON;

if restoring database onto an different server, it needs to

move the certificates to the server first

---------------Restore Server Certificate----------------

USE master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'

CREATE CERTIFICATE your_database_TDE

FROM FILE = 'C:\Temp\your_database_TDE'

WITH PRIVATE KEY

(

FILE = 'C:\Temp\your_database_TDE.prvk'

, DECRYPTION BY PASSWORD = 'password'

)

And then you run restore database / appply encryption using the existing certificates