MCITP

MCITP

Monday, February 12, 2018

Transparent Data Encryption (TDE)




Transparent Data Encryption (TDE) encrypts the data within the physical files of the database, the 'data at rest'. Without the original encryption certificate and master key, the data cannot be read when the drive is accessed or the physical media is stolen. The data in unencrypted data files can be read by restoring the files to another server. TDE requires planning but can be implemented without changing the database. Robert Sheldon explains how to implement TDE.

TDE Eligible SQL Server Editions


·        SQL 2016 Evaluation, Developer, Enterprise

·        SQL 2014 Evaluation, Developer, Enterprise

·        SQL Server 2012 Evaluation, Developer, Enterprise

·        SQL Server 2008 R2 Datacenter, Evaluation, Developer, Enterprise, Datacenter

·        SQL Server 2008 Evaluation, Developer, Enterprise



Implementation

Create Master Key


We must first create the master key. It must be created in the master database.

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

GO



Create Certificate protected by master key

Once the master key is created along with the strong password (that you should remember or save in a secure location), we will go ahead and create the actual certificate.

CREATE CERTIFICATE TDE_Cert

WITH SUBJECT='Database_Encryption';

GO



After we run the CREATE CERTIFICATE statement, we can verify that the certificate has been created by querying the sys.certificates catalog view:

SELECT name CertName,certificate_id CertID,  pvt_key_encryption_type_desc EncryptType,  issuer_name Issuer

FROM sys.certificates WHERE issuer_name = 'TDE certificate';

Create Database Encryption Key


Now switch to the database that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database. Then we indicate the type of encryption algorithm we are going to use. In this case it will be AES_256 encryption.

USE

GO

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;

GO



To check DEK

SELECT DB_NAME(database_id) DbName,encryption_state EncryptState,  key_algorithm KeyAlgorithm,  key_length KeyLength,  encryptor_type EncryptType FROM sys.dm_database_encryption_keys;



If any filegroups associated with the database are set as read-only, the encryption operation will fail. You’ll also run up against a number of restrictions when trying to implement TDE, such as not being able to drop a database during the initial encryption process.

 


Enable Encryption


Finally, we can enable encryption on our database by using the ALTER DATABASE command.

ALTER DATABASE

SET ENCRYPTION ON;

Go

Once the encryption is turned on, depending on the size of the database, it may take some time to complete. You can monitor the status by querying the sys.dm_database_encryption_keys DMV.

When you implement TDE on any user table, SQL Server also encrypts the tempdb database as all database stores its data in tempdb as well.

Disable TDE on the user database

At some point, you might decide that you want to disable encryption on a user database. The process is as simple as enabling it. You again run an ALTER DATABASE statement, only this time turning off the encryption,

ALTER DATABASE EmpData2

SET ENCRYPTION OFF;



Notice that the tempdb database is still encrypted. As it turns out, the database will stay encrypted until it is re-created, which occurs whenever the SQL Server service restarts.

 


Backup Certificate


It’s important to backup the certificate you created and store it in a secure location. If the server ever goes down and you need to restore it elsewhere, you will have to import the certificate to the server. In certain environments, the DR servers are already stood up and on warm/hot standby, so it’s a good idea to just preemptively import the saved certificate to these servers.

BACKUP CERTIFICATE TDE_Cert

TO FILE = 'C:\temp\TDE_Cert'

WITH PRIVATE KEY (file='C:\temp\TDE_CertKey.pvk',

ENCRYPTION BY PASSWORD='InsertStrongPasswordHere')



Remember to store the certificate in a safe and available locations .

Restoring a Certificate


In order to restore the certificate, you will once again have to create a service master key on the secondary server.

USE Master;

GO

CREATE MASTER KEY ENCRYPTION

BY PASSWORD='InsertStrongPasswordHere';

GO



Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.

USE MASTER

GO

CREATE CERTIFICATE TDECert

FROM FILE = 'C:\Temp\TDE_Cert'

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

DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );
Once the certificate is restored to the secondary server you may restore a copy of the encrypted database.

Some things to note before applying TDE. There are some drawbacks. Remember that TDE encrypts the underlying database files including the backups. You can’t just take the files and dump them onto another SQL Server without the appropriate encryption keys and certificates. It does NOT allow for granular user level encryption. If that is the type of encryption you are looking for, you should investigate column level encryption.


Move a TDE Protected Database to another SQL Server using detach/attach or backup/restore method.