Implement Transparent Data Encryption(TDE) step by step using SQL Server

How to Encrypt SQL Server Data using TDE (Transparent Data Encryption)

TDE enables DBA to encrypt the entire database. It will help to protect the data confidentiality.

TDE can be implemented using the following steps

1)      Create a master key

2)      Create a certificate protected by the master key

3)      Create a database encryption key and protect it by the certificate (must be created in respective database)

4)      Backup the certificate using private key

5)      Final step is set database encryption on

First step to create a service master key. Master key should be created in master database.

USEMASTER;

CREATEMASTERKEYENCRYPTIONBYPASSWORD='Malls@123'

First step Once the above statement has been executed the you can verify using the following statement.

select * from sys.symmetric_keys

GO

-- if already master key is available in database you can regenarate using the password

ALTERMASTERKEY REGENERATE WITHENCRYPTIONBYPASSWORD='Malls@123'

Second step is to create the certificate in master database.

--Create a certificate to use with TDE

CREATECERTIFICATE MYTDECert

WITHSUBJECT='TDE Certificate'

Once the above statement executed you can verify the certificate using the following sql statement 

select*fromsys.certificates

GO

Once create a test database in order to test TDE.

CREATEDATABASEMallsDB

GO

useMallsDB

GO

Create database encryption key in the database TDEDB

CREATEDATABASEENCRYPTIONKEY

WITHALGORITHM=AES_256

ENCRYPTIONBYSERVERCERTIFICATE MYTDECert

--use master

-- select * from sys.symmetric_keys

GO

Once Database encryption key created .you need to take backup of certificate protected by private key.

USEmaster

BACKUPCERTIFICATE MYTDECert

TOFILE='D:\TestSQLServerCert'

WITHPRIVATEKEY

(

FILE='D:\Mallkeys',

ENCRYPTIONBYPASSWORD='Malls@123'

);

You have to use master database while executing the statement, otherwise you will encountered the following error.

--use master again

--Msg 15151, Level 16, State 1, Line 1

--Cannot find the certificate 'MYTDECert', because it does not exist or you do not have permission.

You  may get  error while taking certificate backup. You have to verify that file permissions or any file exists with the same name.

Msg 15240, Level 16, State 1, Line 2

Cannot write into file 'Mallkeys'. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

The final step is to encrypt the database using the following statement.

GO

ALTERDATABASE MAllsDB

SETENCRYPTIONON

GO

SELECT database_id,[name], is_encrypted FROMsys.databases

GO

After database has been encypted if you restore the backup of encrypted database (MallsDB) in other server

You will get following error.

In order to restore the encrypted database you need to created the service master key and certificate protected by service master key.

USEMASTER;

CREATEMASTERKEYENCRYPTIONBYPASSWORD='Malls@123'

Then create if there master key is already exist then you can drop using the following statement

DROPMASTERKEY .

It will throw errors if master key used by other databases. You can use the following command in order to regenerate the master key.

ALTERMASTERKEY REGENERATE WITHENCRYPTIONBYPASSWORD='Malls@123'

Then restore the certificate from the backup file you have created

CREATECERTIFICATE TDECERT

FROMFILE='D:\TestSQLServerCert'

WITHPRIVATEKEY (FILE='D:\Mallkeys',

/*The password used to create the private key*/

DECRYPTIONBYPASSWORD='Malls@123')

Make sure that you have to use same password as while created certificate backup

Otherwise you will encounter the following error.

Msg 15465, Level 16, State 1, Line 1

The private key password is invalid.

Ensure those file names are proper otherwise the following error will appear in message pane

Msg 15208, Level 16, State 1, Line 1

The certificate, asymmetric key, or private key file does not exist or has invalid format.

 Ensure that service account need to have permissions to access the folders.