TDE in SQL Server

Transparent Database Encryption (TDE) – It was developed with SQL Server 2008, and it is also available in Oracle database management systems. It is an encryption method that protects the core data in the database.

The encryption method protects the data in the database by encrypting the underlying files of the database, and not the data itself. This prevents the data from being hacked and copied to another server, in order to open the files, you have to have the original encryption certificate and a master key. The actual encryption of the database is done at the page level.

Because TDE protects/encrypts the structure of the database, it is considered an at rest encryption method.

Procedure to implement TDE (Transparent database Encryption)

STEP -1: Login to any instance and create a database.

create database TDE_Test

 

use TDE_Test

STEP -2: Create a table and insert some data,

 create table emp

(

name nvarchar(200)

)

 

insert into emp

select 'john'

 

insert into emp

select 'peter'

 

insert into emp

select 'Rose'

 

select *from emp

STEP -3: Create a Master Key, using the Master Database.

use master

create master key

encryption by password = 'LocalSecure@123'

STEP -4: Now, use the following query to create a certificate with the subject "MY DB Certificate"

   create certificate MyCert1 with Subject = 'MY DB Certificate'

STEP -5: Create a database encryption key with algorithm AES_128

 

use TDE_Test

 

create database encryption key

with algorithm = AES_128

encryption by server certificate Mycert1

STEP -6: Now Enable a Encryption Key using below query,

 

alter database TDE_Test set encryption ON

 

STEP -7: Take a full database backup using below query,

backup database TDE_Test to disk = N'C:\Admin\TDE_2_Full.bak'

 

STEP -8: Now, backup a certificate using the master database and store it in a secure location.

USE master;

 

BACKUP CERTIFICATE MyCert1

    TO FILE = 'C:\Admin\MyCert.bak'

    WITH PRIVATE KEY (

               FILE = 'C:\Admin\MyCert.pvk',

               ENCRYPTION BY PASSWORD = 'Secure@123');

--SQL1 destination server

/*

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

 

WITH

 

Move 'TDE_Test' to N'R:\Program Files\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQ\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\MicrosoftSQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

 

*/

/*

  An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

        -------------------------------------

  

·      Additional Information 

(Note- If you directly try to restore a database from a disk so you must ensure that at the time of restore, you get the below error) 

So first create a master key and then create a certificate and private key using previous certificate or 

Follow the below steps 

*/

STEP -9: Move the backup file and certificate to another location after taking a backup

from where you want to restore a database

After that open the secondary Server in SSMS.

Create a master key using master and the same encryption password.

use master

 create master key

encryption by password = 'SQL1Secure@123'

STEP -10: Create a certificate from a backup file using the appropriate private key and decryption password:

CREATE CERTIFICATE MyCert1

FROM FILE = 'C:\Admin\MyCert1.bak'   

WITH PRIVATE KEY (FILE = 'C:\Admin\MyCert1.pvk',

DECRYPTION BY PASSWORD = 'Secure@123')

STEP -11: Now Restore filelistonly for data and log file using below query,

RESTORE filelistonly From Disk = N'C:\Admin\TDE_Test_Full.bak' 

STEP -12: Now Restore a database using move command,

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

WITH

 

move 'TDE_Test' to N'R:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

STEP -12: Now Restore a database using move command,

USE [master]

 

RESTORE DATABASE [TDE_Test] FROM DISK = N'C:\Admin\TDE_Test_Full.bak'

WITH

 

move 'TDE_Test' to N'R:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Backup\TDE_Test.mdf',

 

move 'TDE_Test_log' to N'L:\Program Files\Microsoft SQLServer\MSSQL15.MSSQLSERVER\MSSQL\Data\TDE_Test_log.ldf'

,

FILE = 1,  NOUNLOAD,  STATS = 5

Ø Advantages of TDE

·      Fairly simple to implement.

·      No changes to the application tier required.

·      Is invisible to the user

·      Works with high availability features, such as mirroring, always on and Log shipping

·      Works with older versions of SQL server, back to 2008

Ø Disadvantages of TDE

·      Only encrypts data at rest, so data in motion or held within and application is not encrypted.

·      All data in the database is encrypted – not just the sensitive data.

·      Requires the more expensive Enterprise Edition (or Developer or Data center Edition) of SQL Server

·      The amount of compression achieved with compressed backups will be significantly reduced.

·      There is a small performance impact.

·      File Stream data is not encrypted

·      Some DBA tasks require extra complexity, for instance restoring a backup onto another server.

·      As TempDB is encrypted, there is potentially an impact on non-encrypted databases on the same server.

·      The master database, which contains various metadata, user data and server level information is not encrypted.

--Check which Databases are encrypted 
SELECT db_name(database_id), encryption_state,   percent_complete, key_algorithm, key_length
FROM sys.dm_database_encryption_keys
WHERE db_name(database_id) not in('tempdb');

Please follow steps below to drop Certificate and Master Key:

Step 1:  Turn off encryption on your user database

ALTER DATABASE DB_Name SET ENCRYPTION OFF

Step 2: Drop the Database Encryption Key of your user database

USE DB_Name

DROP DATABASE ENCRYPTION KEY                                           

Step 3: Drop the certificate and master key on master database

USE master

DROP CERTIFICATE TDECert_Name
DROP MASTER KEY