As a Database Administrator, security is one of the most important areas to consider when it comes to protecting the databases that you support. We use various mechanisms and technologies to secure our data and databases such as firewalls, certificates, and data encryption. Having said that although we have secured our environment, questions will always be raisesd regarding database security. Although we have protected our databases, what would happen if someone steals the mdf file or if someone steals the backup file. Although there are few ways to control this senario using third-party solutions up until SQL Server 2008 there has been no native way to handle this problem. SQL Server 2008 introduces a new feature that protects the database called Transparent Data Encryption – TDE which provides protection to the entire database. I recently tested TDE with SQL Server 2008 February CTP, and the result of the testing made me write this article. This article contains:
- What is Transparent Data Encryption?
- Implementation of TDE
- Is my database secure now, as it says?
- What needs to be considered before enabling TDE?
- What is the impact when TDE is enabled?
What is Transparent Data Encryption?
Microsoft SQL Server 2008 introduces another level of encryption, Transparent Data Encryption. TDE is full database level encryption that is not limited to columns and rows but protects both the data files and log files. The implementation of TDE on a database is comparatively very simple and transparent to the applications that connects to the selected database. It does not requires any changes to the existing applications. The protection is applied on the data files and log files as well as the backup files. Once TDE is enabled on a database, the restoring of a backup to another SQL Server instance or attaching data files to another SQL Server instance will not be permitted until the certificate that was used to secure the database encryption key (DEK) is available.
The encryption feature of TDE is applied at the page level. Once enabled, pages are encrypted before they are written to the disk and decrypted before they are read into the memory. It is important to remember that the communication channel between SQL Server and the client application will not be secured and encrypted via TDE.
The diagram below shows how SQL Server encrypts a database with TDE:
Transparent Data Encryption uses a Database Encryption Key (DEK) for encrypting the database that is stored in the database boot record. The DEK is secured by a certificate that is stored in the master database. Optionally, the DEK can be secured by an asymmetric key that resides in a Hardware Security Module (HSM) with the support of Extensible Key Management (EKM). The private key of the certificate is encrypted with the database master key that is a symmetric key, that is usually protected with a strong password. Note that although the certificate can be secured by a password, TDE requires that the certificate is secured by the database master key. The database master key is protected by the service master key that is protected by the Data Protection API.
Implementation of the TDE
As mentioned above, the implementation of TDE is fairly simple. Here is a sample script for enabling TDE on a database called TestDatabase.
— If the master key is not available, create it.
USE master;
GO
IF NOT EXISTS (SELECT * FROM sys.symmetric_keys WHERE name LIKE ‘%MS_DatabaseMasterKey%’)
BEGIN
CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘Pa$$w0rd4545’;
END
GO
— Create the certificate in the master database.
USE master;
GO
— Since ENCRYPTION BY PASSWORD is not mentioned, the private key of the certificate
— will be encrypted by database master key created above.
IF NOT EXISTS (SELECT * FROM sys.certificates WHERE name LIKE ‘%DEKCertificate%’)
BEGIN
CREATE CERTIFICATE DEKCertificate WITH SUBJECT = ‘DEK Certificate’
END
GO
— Create Database Encryption Key (DEK) in the user database
USE TestDatabase
GO
IF NOT EXISTS (SELECT * FROM sys.dm_database_encryption_keys WHERE database_id = DB_ID(‘TestDatabase’))
BEGIN
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_128
ENCRYPTION BY SERVER CERTIFICATE DEKCertificate
END
GO
— Check whether the key is created
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
— This should return one row (or more if DEKs have been generated in other databases)
— with the encryption_state of 1 (1 = unencrypted).
— Set the DEK on in the TestDatabase.
ALTER DATABASE TestDatabase
SET ENCRYPTION ON
GO
— Check whether the encryption_state is changed to 3. It should be.
SELECT DB_NAME(database_id) AS DatabaseName, * FROM sys.dm_database_encryption_keys
The first two steps show how to creat the database master key and certificate in the master database. Note that the ENCRYPTION BY PASSWORD is not specified with the CREATE CERTIFICATE, hence the private key of the self-signed certificate will be secured by the database master key. The next step shows the way of creating the DEK in the TestDatabase. Execute the code. It adds the DEK to the TestDatabase. If the certificate’s private key is secured with a password, you will get error message like below;
Msg 33101, Level 16, State 1, Line 4
Cannot use certificate ‘DEKCertificateTest’, because its private key is not present or it is not protected by the database master key. SQL Server requires the ability to automatically access the private key of the certificate used for this operation.
The sys.dm_database_encryption_keys allows you to see that the DEKs have been added to server. The column encryption_state indicates whether the DEK is in the following states unencrypted, encryption in progress, encrypted, key change in progress, and decryption in progress for the values of 1, 2, 3, 4, and 5 respectively. When you run the DMV before setting the ENCRYPTION, the state appears as 1 and, once set, state will be appeared as 3. Done. The TestDatabase is now fully secured.