Use transparent Data encryption (TDE) to protect your database

This article explains how to use transparent Data encryption (TDE) to protect a database, including the files that are backed up. To understand that this feature can be used to improve the security of your database application, please check this article.

STEP1:

Use master;

GO

IF EXISTS (SELECT

*

From

sys.databases

WHERE

name = ' Tdedemo ')

DROP DATABASE Tdedemo;

GO

CREATE DATABASE Tdedemo;

GO

Next, create the Server-level certificate which would protect the database key used to encrypt the database's files. This certificate in turn would be protected by the master key which if it does not exist would need to be created:

Create Master Key

Use master;

GO

IF not EXISTS (SELECT

*

From

Sys.symmetric_keys

WHERE

Name like '%[_]databasemasterkey% ')

BEGIN

CREATE MASTER KEY encryption by PASSWORD =

' 997jkhubhk$w4ez0876hkhjh5gh ';

END

GO

Create or obtain a certificate protected by the master key

CREATE CERTIFICATE Mytdecert

with SUBJECT = ' My TDE Certificate ';

GO

With the Server-level, the database can now is encrypted. This is do by first creating the database (symmetric) encryption key within the database and then enabling TDE:

Use Tdedemo;

GO

CREATE DATABASE encryption KEY with algorithm = aes_128 encryption by SERVER

CERTIFICATE Mytdecert;

GO

ALTER DATABASE Tdedemo

SET encryption on;

GO

Database encryption may take a and complete. While in progress, the Sys.dm_database_encryption_keys data management view would show the database in an encryption_state of 2:

SELECT

Db_name (database_id) as DB,

Encryption_state

From

Sys.dm_database_encryption_keys

WHERE

database_id = db_id ();

GO

Once TDE Encryption has been fully applied, the Encryption_state would become 3:

SELECT

Db_name (database_id) as DB,

Encryption_state

From

Sys.dm_database_encryption_keys

WHERE

database_id = db_id ();

GO

Now to demonstrate the protection of the database backup files through TDE, backup the database and its certificate.  Please note that these is being backed up locally to the same location. This isn't a secure practice but is expediant for this demo:

Use master;

GO

BACKUP CERTIFICATE Mytdecert

to FILE = ' C:\temp\MyTdeCert '

With PRIVATE KEY (

FILE = ' C:\temp\MyTdeCertPrivateKey ',

Encryption by PASSWORD = ' 997jkhubhk$w4ez0876hkhjh5gh '

) ;

GO

BACKUP DATABASE Tdedemo

to DISK = ' C:\temp\TdeDemo.bak '

With INIT;

GO

By dropping the database and the Server-level certificate, we can simulate a restore to a different server:

DROP DATABASE Tdedemo;

GO

DROP CERTIFICATE Mytdecert;

GO

With the certificate missing, the restore operation would fail:

RESTORE DATABASE Tdedemo

From DISK = ' C:\temp\TdeDemo.bak ';

GO

MSG 33111, Level A, State 3, line 1
Cannot find server certificate with thumbprint ' 0x686a8264e4a17572fbae6a1d091a47d600847fb6 '.
MSG 3013, Level A, State 1, line 1
RESTORE DATABASE is terminating abnormally.

It's not until the certificate are recovered to the server and the backup file can be restored:

CREATE CERTIFICATE Mytdecert

From FILE = ' C:\temp\MyTdeCert '

With PRIVATE KEY (

FILE = ' C:\temp\MyTdeCertPrivateKey ',

Decryption by PASSWORD = ' 997jkhubhk$w4ez0876hkhjh5gh '

) ;

GO

RESTORE DATABASE Tdedemo

From DISK = ' C:\temp\TdeDemo.bak ';

GO

Processed 168 pages for database ' Tdedemo ', file ' Tdedemo ' on file 1.
Processed 2 pages for database ' Tdedemo ', file ' Tdedemo_log ' on file 1.
RESTORE DATABASE successfully processed pages in 0.157 seconds (8.415 mb/sec).

To reset the environment:

Use master;

GO

DROP DATABASE Tdedemo;

GO

DROP CERTIFICATE Mytdecert;

GO