TDE-(Transparent Data Encryption)

Creating a TDE protected database
Create a Master Key
A master key is a symmetric key that is used to create certificates and asymmetric keys.
Execute the following script to create a master key:

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO

Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location

Create a Certificate
Certificates can be used to create symmetric keys for data encryption or to encrypt the data
directly. Execute the following script to create a certificate:

USE master;
CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Certificate'
GO

Note that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a
secure location

1.Create a backup of the server certificate in the master database. If the certificate that is used
to protect the database encryption key is lost, you will not be able to access the data in a TDE
protected database. The following example stores the backup of the certificate and the private key
file, in the default data location for this instance of SQL Server (C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA).

You must retain copies of both the certificate file and the private key file in order to recover the certificate. The password for the private key, does not have to be the same as the database master key password.

Use Master
BACKUP CERTIFICATE TDECert
TO FILE = 'C:\Testing_TDECert.cer'
WITH PRIVATE KEY
(FILE = 'C:\Cer_Private_Key.pvk',
ENCRYPTION BY PASSWORD ='Pass@word11')

Create a Database Encryption Key

A database encryption key is required for TDE. Execute the following script to create a new
database and a database encryption key for it:

USE Testing

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert

Enable TDE

The final step required to implement TDE is to execute the following script:

ALTER DATABASE Testing
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO

You can query the is_encrypted column in sys.databases to determine whether TDE is
enabled for a particular database

Moving TDE Protected DB to another sql server instance

1.Create a database master key on the destination instance of SQL Server. The password does not
have to be the same as the source server.

USE master;
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = 'Pass@word123';

2.Recreate the server certificate by using the original server certificate backup file. The password must be the same as the password that was used when the backup was created.

Use Master
create CERTIFICATE TDECert111111
from FILE = 'C:\Testing_TDECert.cer'
WITH PRIVATE KEY
(FILE = 'C:\Cer_Private_Key.pvk',
DECRYPTION BY PASSWORD ='Pass@word11')

Backup the master database
•Installing the certificate and attaching the database have changed information in the master database.
You should create a new backup of the master database.

Disable TDE

USE MASTER
GO
ALTER DATABASE Testing
SET ENCRYPTION OFF
GO
USE Testing
GO
DROP DATABASE ENCRYPTION KEY
GO
DROP CERTIFICATE TDECert
DROP MASTER KEY

For more learning About TDE Click Here