What is TDE (Transparent Data Encryption) in SQL 2008?

 What is TDE (Transparent Data Encryption) in SQL 2008?

Ref:
https://www.mssqltips.com/sqlservertip/1514/sql-server-2008-transparent-data-encryption-getting-started/
https://www.mssqltips.com/sqlservertip/1507/implementing-transparent-data-encryption-in-sql-server-2008/



TDE is a new feature in SQL Server 2008; it 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.  The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it.  There is absolutely no code that needs to be written to perform the encryption and decryption.  There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.
We've probably all heard of incidents where backup tapes containing sensitive information have been lost or stolen.  With TDE the backup files are also encrypted when using just the standard BACKUP command once encryption is turned on for the database.  The data in the backup files (or on disk) is completely useless without also having access to the key that was used to encrypt the data.
Before we dive in to the steps to implement TDE, let's take a minute to discuss encryption at a very high level.  The Wikipedia definition of encryption is "the process of transforming information (referred to as plaintext) using an algorithm (called cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key".  To encrypt some data, I choose an available algorithm, supply a key and I now have encrypted data.  To decrypt the encrypted data, I choose the same algorithm and supply the key.  The security provided by encryption is based on the strength of the algorithm and protection of the key.  There are two types of keys - symmetric and asymmetric.  With a symmetric key, the same value is used to encrypt and decrypt the data.  An asymmetric key has two components - a private key and a public key.  I use the private to encrypt data and someone else must use the public key to decrypt the data.  To recap, the symmetric key or private key of the asymmetric key pair must be stored securely in order for encryption to be effective.
Now let's walk through an example of how to implement TDE.  Books on Line lists the following four steps to implement TDE on a 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.  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.  For additional details on master keys refer to our earlier tip Managing SQL Server 2005 Master Keys for Encryption.
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.  For additional details on certificates, refer to our earlier tip SQL Server 2005 Encryption - Certificates 101.
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:

CREATE DATABASE mssqltips_tde GO USE mssqltips_tde; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE TDECert GO

In order to work with TDE the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database. 
Enable TDE
The final step required to implement TDE is to execute the following script:

ALTER DATABASE mssqltips_tde 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.

Implementation of TDE (Transparent Data Encryption) in SQL 2008:

Transparent Data Encryption is a new feature in SQL Server 2008 which implements database-level encryption complementing the row-level encryption which is currently available in SQL Server 2005. This protects database access, either directly or by restoring a database backup on another SQL Server instance.
In this tip we are going to look at how to implement transparent data encryption to protect database backups. Let us first demonstrate how prone database backups are to security threats. By default, SQL Server backups are not encrypted.  Let's start by creating a full backup of the Northwind database on my default instance.

BACKUP DATABASE Northwind  TO DISK = 'C:\Northwind_unencrypted.bak' WITH INIT, STATS = 10

Let's query the Northwind database for a specific record. We will use this record later on to view the contents of our database backup

SELECT * FROM dbo.Customers WHERE ContactName = 'Aria Cruz'

Open the database backup in Notepad and search for 'A r i a   C r u z' (note the spaces between the letters and three spaces between "Aria" and "Cruz" as this is Unicode text.) Observe that the text data is readable.

Since your native backups are readable they are prone to security risks since they can be read in clear text.  This enables someone to just look at your database backups and retrieve data from the files. What's worse is that anybody can restore your database backups on another SQL Server instance. We will demonstrate this procedure shorlty.
Let's implement transparent data encryption on our Northwind database by first setting up encryption on the server. To do that, we create the database master key on the master database

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

Then, we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE NorthwindCert WITH SUBJECT = 'My DEK Certificate for Northwind database'

We will then set the encryption for the Northwind database by creating a database encryption key and password using the certificate we just created.

USE Northwind GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE NorthwindCert GO

The encryption algorithm choice is yours, since a few different options exist.  For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article.  After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE Northwind SET ENCRYPTION ON

Let's backup the encrypted Northwind database this time but using a different filename.

BACKUP DATABASE Northwind  TO DISK = 'C:\Northwind_encrypted.bak' WITH INIT, STATS = 10

The encrypted backup would take a bit longer than the unencrypted backup. If you open the encrypted database backup and search for the string 'A r i a   C r u z' which we searched for earlier, you will no longer find it. You can scroll thru the text and notice the encrypted data which is unlike the one you'll saw in the unencrypted backup.

Restoring the encrypted database backup on another SQL Server instance
It is easy to restore the unencrypted backup on another SQL Server instance. In fact, this is what makes it prone to security risks. You can try restoring the encrypted database backup as Northwind_encrypted and you'll encounter an error. I am doing this on my TEST instance. The error message will tell you that the server certificate cannot be found. Even though the restore failed, you will still see the Northwind_encrypted database on the Object Explorer on a restoring state.

In order to restore the encrypted database backup on another SQL Server instance we need to first export the certificate we created on the instance on which the encrypted database backup was created. To export the certificate to a file, I'll connect on my default instance and run this query.

USE master GO BACKUP CERTIFICATE NorthwindCert  TO FILE = 'C:\NorthwindCert_File.cer' WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk' , ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' ) GO

Then, we'll need to copy the certificate and the private key file to the other SQL Server instance. Since I am running my default and my TEST instances on the same server, I'll just refer to those files when I do the import process. Now, on the TEST instance, import the certificate by first creating a master key.

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd' GO

The password provided here is different from the one you used in the default instance as you are creating a new master key for this instance. After a master key has been created, you can create a certificate by importing the certificate we created earlier.

CREATE CERTIFICATE NorthwindCert FROM FILE = 'C:\NorthwindCert_File.cer'  WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk',  DECRYPTION BY PASSWORD = 'mY_P@$$w0rd'); GO

Notice that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which we used to export the certificate to a file. This is because we will use the same certificate on this new instance to access the encrypted database. After the certificate has been created, we can now restore the encrypted database backup on this instance. You might want to drop the initial encrypted database which you attempted to restore before proceeding. You will now be able to restore the encrypted database backup successfully.

Transparent Data Encryption is a new feature in SQL Server 2008 which implements database-level encryption complementing the row-level encryption which is currently available in SQL Server 2005. This protects database access, either directly or by restoring a database backup on another SQL Server instance.
In this tip we are going to look at how to implement transparent data encryption to protect database backups. Let us first demonstrate how prone database backups are to security threats. By default, SQL Server backups are not encrypted.  Let's start by creating a full backup of the Northwind database on my default instance.

BACKUP DATABASE Northwind  TO DISK = 'C:\Northwind_unencrypted.bak' WITH INIT, STATS = 10

Let's query the Northwind database for a specific record. We will use this record later on to view the contents of our database backup

SELECT * FROM dbo.Customers WHERE ContactName = 'Aria Cruz'

Open the database backup in Notepad and search for 'A r i a   C r u z' (note the spaces between the letters and three spaces between "Aria" and "Cruz" as this is Unicode text.) Observe that the text data is readable.

Since your native backups are readable they are prone to security risks since they can be read in clear text.  This enables someone to just look at your database backups and retrieve data from the files. What's worse is that anybody can restore your database backups on another SQL Server instance. We will demonstrate this procedure shorlty.
Let's implement transparent data encryption on our Northwind database by first setting up encryption on the server. To do that, we create the database master key on the master database

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd'

Then, we create the server-based certificate which will be used to encrypt the database

CREATE CERTIFICATE NorthwindCert WITH SUBJECT = 'My DEK Certificate for Northwind database'

We will then set the encryption for the Northwind database by creating a database encryption key and password using the certificate we just created.

USE Northwind GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_128 ENCRYPTION BY SERVER CERTIFICATE NorthwindCert GO

The encryption algorithm choice is yours, since a few different options exist.  For guidance on selecting an encryption algorithm on SQL Server, check out this Microsoft TechNet article.  After creating the database encryption key, we'll enable encryption on the database level.

ALTER DATABASE Northwind SET ENCRYPTION ON

Let's backup the encrypted Northwind database this time but using a different filename.

BACKUP DATABASE Northwind  TO DISK = 'C:\Northwind_encrypted.bak' WITH INIT, STATS = 10

The encrypted backup would take a bit longer than the unencrypted backup. If you open the encrypted database backup and search for the string 'A r i a   C r u z' which we searched for earlier, you will no longer find it. You can scroll thru the text and notice the encrypted data which is unlike the one you'll saw in the unencrypted backup.

Restoring the encrypted database backup on another SQL Server instance
It is easy to restore the unencrypted backup on another SQL Server instance. In fact, this is what makes it prone to security risks. You can try restoring the encrypted database backup as Northwind_encrypted and you'll encounter an error. I am doing this on my TEST instance. The error message will tell you that the server certificate cannot be found. Even though the restore failed, you will still see the Northwind_encrypted database on the Object Explorer on a restoring state.

In order to restore the encrypted database backup on another SQL Server instance we need to first export the certificate we created on the instance on which the encrypted database backup was created. To export the certificate to a file, I'll connect on my default instance and run this query.

USE master GO BACKUP CERTIFICATE NorthwindCert  TO FILE = 'C:\NorthwindCert_File.cer' WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk' , ENCRYPTION BY PASSWORD = 'mY_P@$$w0rd' ) GO

Then, we'll need to copy the certificate and the private key file to the other SQL Server instance. Since I am running my default and my TEST instances on the same server, I'll just refer to those files when I do the import process. Now, on the TEST instance, import the certificate by first creating a master key.

USE master GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'EnKrYpt3d_P@$$w0rd' GO

The password provided here is different from the one you used in the default instance as you are creating a new master key for this instance. After a master key has been created, you can create a certificate by importing the certificate we created earlier.

CREATE CERTIFICATE NorthwindCert FROM FILE = 'C:\NorthwindCert_File.cer'  WITH PRIVATE KEY (FILE = 'C:\NorthwindCert_Key.pvk',  DECRYPTION BY PASSWORD = 'mY_P@$$w0rd'); GO

Notice that the password specified here in the DECRYPTION BY PASSWORD parameter is the same as that which we used to export the certificate to a file. This is because we will use the same certificate on this new instance to access the encrypted database. After the certificate has been created, we can now restore the encrypted database backup on this instance. You might want to drop the initial encrypted database which you attempted to restore before proceeding. You will now be able to restore the encrypted database backup successfully.