Summary
Transparent Data Encryption (TDE) encrypts all the data that’s stored within the database’s physical files and also any backup files created
from the database. Encrypting a database with TDE is a very straightforward process, involving 3 simple steps. This article
shows how to do this.
This is the second article in a series of three around TDE.
ENCRYPTING A DATABASE WITH TDE IN 3 STEPS
STEP 1 : Set up an instance level master key and certificate
The first step is to set up a master key by running the following SQL, but make sure you change the password to something more secure. If
you already have a master key set up on the instance then you don’t need to run this (you may well already have one as it’s used by
other encryption functions).
USE Master;
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyPassword';
Then we need to create a certificate at the instance level by running the following SQL :
USE Master;
CREATE CERTIFICATE MyInstance_ServerCert
WITH SUBJECT='Certificate for MyInstance';
STEP 2 : Set up a database encryption key within the database to be encrypted
To set up a database encryption key run the following SQL in the database to be encrypted (in this example the database is called TDETest). Note that this uses the server certificate created in the previous step (I called it MyInstance_ServerCert) :
USE TDETest;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE MyInstance_ServerCert;
STEP 3 : Enable TDE for the database
To switch on encryption for a database run the following SQL :
ALTER DATABASE TDETest SET ENCRYPTION ON;
That’s it ! The database files should now be encrypted.
MORE INFORMATION
I’ve briefly described how to set up TDE, but will now discuss how to verify TDE, check on encryption progress and backup certificates
and other matters relating to TDE.
Verifying whether a Database is Encrypted
You can check whether a database is encrypted either by looking at the database properties (right click on the
database in SQL Server Management Studio, select
Properties and then click on the Options page), and looking at the “Encryption Enabled” setting as below :
Alternatively a list of all the encrypted databases on an instance can be obtained by running the following query :
SELECT name,DEK.*
FROM sys.databases D
JOIN sys.dm_database_encryption_keys DEK
ON DEK.database_id = D.database_id
ORDER BY name;
On my server I have just the one encrypted databases so get the following results from this query (note that as there is at least
one encrypted DB then tempdb is encrypted) :
Note the encryption_state column. The value of 3 indicates that the database is encrypted, a value of 2 indicates that encryption
is in progress (a newly encrypted database is encrypted in the background, which may take a while for larger databases).
Backing Up Certificates
It’s important to back up the certificate and keep this safe, as this will be needed if you want to restore the database onto
another server. To backup the certificate use the ‘Backup Certificate’ command, for instance I ran :
USE Master;
BACKUP CERTIFICATE MyInstance_ServerCert
TO FILE = 'D:\SQL2012_SQLCert.cer'
WITH PRIVATE KEY (FILE='D:\SQL2012_MasterKey.pvk',
ENCRYPTION BY PASSWORD='D8LsiBL62mLPhi0z7CoM');
Removing Encryption from a Database
If encryption is no longer required then it can be removed along with the database encryption key by running the following
commands (obviously you’d need to change the database name) :
-- Remove encryption
USE TDETest;
ALTER DATABASE TDETest SET ENCRYPTION OFF;
GO
-- Remove DEK
DROP DATABASE ENCRYPTION KEY;
You may need to leave a gap between running these statements as TDE is removed asynchronously in the background, and needs to be removed before the DEK
can be dropped. Also note that tempdb will remain encrypted
even if encryption is removed from all databases on the instance. However if the instance is
restarted then tempdb will be recreated without encryption.
Restoring Encrypted Backups to another Server
If you are restoring a database backup to the same instance (either overwriting the same database or as a new database) then this
can be done in the same way as a non-encrypted database. The restored database will also be encrypted using the same encryption key.
However if you try and restore the backup to another instance the chances are that you’ll get a ‘Cannot find server certificate with
thumbprint’ error. This is because the new server needs the certificate from the original server.
To create the certificate that we backed up in the section ‘Backing Up Certificates’ above we can just run the statement below on the new server :
CREATE CERTIFICATE MyServer_SQL2012_SQLCert
FROM FILE = 'D:\SQL2012_SQLCert.cer'
WITH PRIVATE KEY (FILE='D:\SQL2012_MasterKey.pvk',
DECRYPTION BY PASSWORD='D8LsiBL62mLPhi0z7CoM');
Note that the new server will need to have a master key set up in order to encrypt the certificate, however the password does not
need to be the same as on the original server. If you don’t have a master key setup then this can be done using the
following statement (but best to use a different password) :
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='MyPassword';
In summary if you are restoring the encrypted database to another instance you will need the certificate and its private key as
well as the backup file.
Checking What Certificates Exist
You can check which certificates exist using the following query :
SELECT * FROM sys.certificates;