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 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
Create Master Key
We must first create the master key. It must be created in the master database.
CREATE MASTER KEY ENCRYPTION
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
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.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
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.
Finally, we can enable encryption on our database by using the ALTER DATABASE command.
SET ENCRYPTION ON;
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.
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.
CREATE MASTER KEY ENCRYPTION
Once that is done, you must remember where you backed up the certificate and the encryption/decryption password.
CREATE CERTIFICATE TDECert
FROM FILE = 'C:\Temp\TDE_Cert'
WITH PRIVATE KEY (FILE = 'C:\TDECert_Key.pvk',
DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere' );
Move a TDE Protected Database to another SQL Server using detach/attach or backup/restore method.