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
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.
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe';
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.
SELECT [name], is_encrypted FROM sys.databases
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
OPEN MASTER KEY DECRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe';
BACKUP MASTER KEY TO FILE = 'c:mykeyexportedbankmasterkey'
ENCRYPTION BY PASSWORD = '23987hVJ#Kh95234nl0zBe';
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.
CREATE CERTIFICATE BankAppCertificate
WITH SUBJECT = 'TDE Bank Certificate'
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
I had also taken a backup
of the certificate to forward that to another development team using following
BACKUP CERTIFICATE BankAppCertificate TO FILE = 'c:mykeyexportedbankcertificate'
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.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_192
ENCRYPTION BY SERVER CERTIFICATE BankAppCertificate
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
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.
SET ENCRYPTION ON
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.