Tapas Pal shows you how to encrypt your SQL Server database for a pre-existing, business critical web application using Transparent Data Encryption (TDE), a new full database encryption technique introduced in SQL Server 2008.
As part of the SOX (Sarbanes-Oxley Act) regulatory compliance implementation, one of my banking customers asked me to encrypt their database for an already existing application. It was a business critical web application developed using Visual Studio 2005 (ASP.NET 2.0 framework) and SQL Server 2000 as the backend database. Like any other standard software development project, the delivery schedule for this was also very tight and the client requested that I implement database encryption as quickly as possible, with minimal impact on the existing application. I completed that assignment on time and within budget using Transparent Data Encryption (TDE), a new full database encryption technique introduced in SQL Server 2008. In this article, I will demonstrate the implementation of TDE.
Why Transparent Data Encryption (TDE)
I had to spend 1/3 of the entire project time in feasibility study and chose the best technique for encrypting the existing database from the available options.
SQL server 2000 doesn't provide advance encryption features. Developers need to write extended stored procedures to implement encryption but for this project that was not feasible as modification of all the existing stored procedures would have taken much more time than the client's specified timeframe. There are few third party tools available in the market like DbEncrypt. Using these tools developers can implement encryption for SQL server 2000 databases, but my client didn't agree to spending the extra money for a DbEncrypt license.
My customer was already using SQL server 2005, so I planned to migrate the already existing SQL Server 2000 database to SQL Server 2005 and implement encryption on top of new 2005 databases. However, to implement data or cell level encryption in Microsoft SQL Server 2005, all table column data type must be varbinary, which means I needed to change all existing table structures and modify column data types to varbinary. In addition, there are few drawbacks of SQL Server 2005 data encryption:
- Ranged and equality searches are not supported in SQL Server 2005 on the encrypted data values.
- ASP.NET web application performance will be very slow while querying encrypted data because data will be decrypted before sending to front end. Also creating index or using foreign keys often do not work properly with cell-level or column-level encryption.
- Data backup taken using SQL Server 2005 "Backup" command will not have encryption. Backup data will be in readable format. That means a high data security violation.
Finally, I completed the assignment by implementing TDE. Full database level encryption is first introduced in SQL Server 2008 using TDE. TDE is the best choice for bulk encryption and as a part of the regulatory compliance implementation. Microsoft has designed TDE to provide protection for the entire database without affecting existing applications. After TDE implementation, any backup taken in tape will be as secure as the source database. Without an access key, tape data can't be used. I convinced my customer to migrate the existing SQL Server 2000 database to SQL Server 2008 and implement TDE on top of new SQL Server 2008.
Transparent Data Encryption (TDE) Implementation
I used the following five steps to migrate my existing SQL server 2000 database to 2008 and implement TDE.
1. Migrate SQL Server 2000 database to SQL Server 2008
I always prefer to take the backup (.Bak file) of the existing SQL Server 2000 database and restore it to the SQL Server 2008 database server. That's what I had done in this project. Some developers love to play with SQL scripts. They generate whole DB scripts using the SQL Server provided tool or any other third party tool and run that script in the destination 2008 server for creating the schema structure. They do data migration by using the SQL Server Wizard or executing 'INSERT INTO" statements.
2. Create the Master Key for TDE
After restoring the database in the SQL Server 2008 environment, the next step was the creation of the TDE Master key, which is actually a symmetric key. This key is required to create certificated and other asymmetric keys. See the following SQL code snippet.
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe'; GO
PASSWORD must meet the Windows password policy requirements. Remember this password. The is_master_key_encrypted_by_server column of the sys.databases catalog view in master database indicates whether the database master key is encrypted or not. See the following SQL code snippet.
GO SELECT [name], is_encrypted FROM sys.databases GO
You can drop an existing master key using DROP MASTER KEY and modify by ALTER MASTER KEY SQL commands. I had taken a backup of master key and forwarded that to another development team working on application enhancement using following SQL code.
USE master; OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe'; BACKUP MASTER KEY TO FILE = 'c:mykeyexportedbankmasterkey' ENCRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe'; GO
3. Create the TDE Certificate
The TDE Certificate is a database-level securable that follows the X.509 standard and supports X.509 V1 fields. Using CREATE CERTIFICATE a new certificate can be created.
USE master; CREATE CERTIFICATE BankAppCertificate WITH SUBJECT = 'TDE Bank Certificate' GO
Certificates are used to create symmetric keys for data encryption and also to encrypt the data directly. To learn more about TDE certificates check Books Online. To drop an existing certificate use DROP CERTIFICATE and to alter use ALTER CERTIFICATE SQL commands.
I had also taken a backup of the certificate to forward that to another development team using following SQL command.
BACKUP CERTIFICATE BankAppCertificate TO FILE = 'c:mykeyexportedbankcertificate' GO
4. Create the Encryption Key
Using the CREATE DATABASE ENCRYPTION KEY command, a database level encryption key can be generated. This key will be required for TDE implementation. It is important that developers keep in mind that the asymmetric key that is used to encrypt the database encryption key must be created in the master system database. See the following SQL statements.
GO USE MyBankDatabase; CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_192 ENCRYPTION BY SERVER CERTIFICATE BankAppCertificate GO
MyBankDatabase (mentioned in the code snippet above) is the name of the target database and BankAppCertificate (above code snippet) is the certificate created in the previous step. To edit an existing database encryption key you can use ALTER DATABASE ENCRYPTION KEY command. To delete use the DELETE DATABASE ENCRYPTION KEY command.
5. Enable the TDE Encryption Key for your database
TDE will not work until you enable the encryption for the target database. Use the following SQL command to enable encryption.
GO USE MyBankDatabase; SET ENCRYPTION ON GO
For SQL server administrators, TDE provides an easy way to implement full database encryption. TDE doesn't reduce application performance. TDE provides a comprehensive defense as the encryption stays intact with the database even it is moved to different locations. From a security point of view, TDE is best, as encryption doesn't break in backups and snapshots.