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