SQL SERVER –Transparent Data Encryption (TDE)

Share via:
SQL SERVER –Transparent Data Encryption (TDE)

Dear Readers,

In this article, we will learn about SQL SERVER –Transparent Data Encryption (TDE)

Transparent Data Encryption (TDE) performs real-time I/O encryption and decryption of the data and log files, thereby, protecting data at rest.

When SQL Server 2008 was introduced, Microsoft implemented Transparent Data Encryption (TDE).  When TDE is enabled on a database SQL Server will encrypt the database as data is written to the disk.  Additionally, when data is read from disk it will be unencrypted.  When data is in memory is it is in an unencrypted format.

Encryption of a database file is done at the page level. The pages in an encrypted database are encrypted before they’re written to disk and are decrypted when read into memory. TDE doesn’t increase the size of the encrypted database.

One more advantage with TDE, database backups will also be encrypted. In the event that a backup of the database gets lost or stolen, the culprit will not be able to restore the database without the appropriate certificate, keys and passwords

Below are the sequence order steps to implement TDE

Steps to implement TDE for database

Here I am using DB name as Testing

Step 1: Create Database Master Key

Step 2: Create a Certificate to support TDE

Step 3: Create Database Encryption Key

Step 4: Enable TDE on Database

Now TDE implemented on Testing database and using below script we can check on which database TDE is enabled.

–Execute below query and check what databases are encrypted on the SQL Server instance

You can get the result as below

— Execute below query to find the name of the certificates

We will get below result
How to restore database backup on other instance which TDE enabled

1.Take backup TDE certificate on instance

2.Restore TDE Certificate on other instance

— create master key on other instance which we are going to restore the backup

— Restore certificate

Now you can restore the database after restoring certificates.

How to Remove TDE

1.Disable TDE on the affected databases

2.Remove the encryption key

3.Drop certificates using below query

4.Drop master key

Thank you for giving your valuable time to read the above information.
Follow us on 
Website  www.ktexperts.com
Facebook Page KTexperts
Linkedin Page : KT EXPERTS                

Share via:
Note: Please test scripts in Non Prod before trying in Production.
1 Star2 Stars3 Stars4 Stars5 Stars (No Ratings Yet)

Add Comment