Question: Recently I got a question regarding Transparent Data Encryption (TDE) and how it works.
Answer:Microsoft has introduced a new feature TDE (Transparent Data Encryption) from SQL Server 2008. TDE provides real time encryption of data and log files. Data is encrypted before it is written to disk; data is decrypted when it is read from disk.

Main benefit of TDE is that it does not require any changes into existing application. TDE encrypt data using AES and 3DES encryption algorithms without having to change the applications that will be connecting to the database.

With TDE backup files and database files are encrypted and no one can access it without key.

Follow below 4 steps to implement the TDE on the particular database.
• Create a master key
• Create or obtain a certificate protected by the master key
• Create a database encryption key and protect it by the certificate
• Set the database to use encryption

Create a Master Key A master key is a symmetric key that is used to create certificates and asymmetric keys. You can create the Master Key using below script

--create a master key
USE master 
GO 
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd' 

Now next step is you have to create the server-based certificate which will be used to encrypt the database. You can create the certificate using below query.

--create a ceritificate
USE master;
CREATE CERTIFICATE sql_tdeDBcert 
WITH SUBJECT = 'TDE Certificate for SQL_tde database'
GO 

Here, I am creating sample database using below script, if you have already any test database you can use it.

--script to create a database
create database sql_tde

use sql_tde
go

--script to create sample table
create table fnInfo
(
	Id int primary key identity(1,1),
	CCNo varchar(16) constraint UK1 Unique,
	Name varchar(20)
)

--enter the sample data
insert into fnInfo values ('456333125','Jugal')
insert into fnInfo values ('1234568484','Sunil')
insert into fnInfo values ('1234789254','Chintan')

select * from fnInfo

Create a Database Encryption Key A database encryption key is required for TDE. Here we will set the encryption for the database by creating a database encryption key and password using the certificate we created in the above step.

use sql_tde
GO 
create database encryption key
with algorithm = AES_128
encryption by server certificate sql_tdeDBcert

Now we will check for the backup scenario, whether we are able to restore it without key or not. I have executed below backup script to take the database backup.

----backup database
backup database sql_tde to disk = 'c:\sql_tde.bak'

Now I am restoring the database backup on the different machine without key and certificate.

----backup database
restore database sql_tde from disk = 'c:\sql_tde.bak'

Restore command is failed with the below error on the differnet machine.

Starting up database 'sql_tde'.
Error: 3283, Severity: 16, State: 1.
The file "sql_tde_log" failed to initialize correctly. Examine the error logs for more details.  

To restore the encrypted database backup on different SQL Server instance we have to export the certificate created on the SQL instance on which the encrypted database backup was created.
You can export or transfer the certificate to a file. Use the below query for it.

USE master 
GO 
BACKUP CERTIFICATE sql_tdeDBcert  
TO FILE = 'C:\sql_tdeDBcert_cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\sql_tdeDBcert_key.pvk' , 
ENCRYPTION BY PASSWORD = 'P@ssw0rd' ) 
GO 

Now move the files to destination server and import the certificate using below command. Please note that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which used to export the certificate to a file.

CREATE CERTIFICATE sql_tdeDBcert 
FROM FILE = 'C:\sql_tdeDBcert_cert.cer' 
WITH PRIVATE KEY (FILE = 'C:\sql_tdeDBcert_key.pvk',  
DECRYPTION BY PASSWORD = 'P@ssw0rd'); 
GO  

You can restore the database now using below query.

----backup database
restore database sql_tde from disk = 'c:\sql_tde.bak'