Understanding transparent data encryption in SQL Server 2008
Hilary Cotter, Contributor
Transparent Data Encryption (TDE) is a new feature in SQL Server 2008 designed to encrypt your
database files, database backups and temporary database (tempdb). As you request data from your
database, it will be decrypted in real time, and TDE will not prevent any user authorized to enter
your database from accessing and reading your tabular data.
Transparent data encryption – why use it?
The PCI DSS (Payment Card Industry Data Security Standard) requires that each of your databases
and backups are secured, and TDE is intended primarily to help companies running SQL Server 2008
meet the terms of those compliance guidelines.
Keep in mind that TDE won't satisfy all of your security or compliance requirements on its
Dig Deeper
-
People who read this also read...
-
Premium Access
Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States.
Privacy
This was first published in June 2009
own.
It is instead part of a suite of features provided by SQL Server 2008 to help DBAs achieve
compliance. The DBA will still need to ensure that sensitive data is encrypted by the encryption
algorithms, and network and system administrators must ensure that the Windows servers, network and
link between the Web and application servers are secure. Developers are still responsible for
making sure that communication from the client to the Web server is secure or encrypted.
Considerations before using TDE
Before you implement transparent data encryption on your SQL Server you should consider several
factors.
For example, any company using TDE in SQL Server may notice a slight performance degradation as
data is encrypted while being written to disk, and decrypted when being read from the disk. This
hit is mainly due to increased CPU requirements. The data file, transaction log and backups will be
the same size as with a database that does not have TDE enabled.
Database compression ratios for encrypted database backups are far more cost-effective for
unencrypted backups. This may require increased storage requirements for your backups, and an added
fee may be incurredif you are transferring those encrypted backup files offsite.
Database compression ratios for encrypted database backups are much less when compared to those
of unencrypted backups. This may require increased storage requirements for your backups, and added
costs if you are transferring those encrypted backup files offsite.
While securing backups can also be done natively in SQL Server via a password, this is
considered a weak option. Most tape backup solutions now include encryption on the fly while
writing to tape devices. While in the past this technology was slow, there have been considerable
advances in tape encryption over the past few years. Still, these developments will not prevent a
hacker from accessing your SQL Server, nor will they hinder their efforts to detach your database
files, copy them to another SQL Server, attach them and read your database contents. Database file
encryption is required by most compliance regulations.
Below are some other important factor to take into account before implementing transparent data
encryption:
- Using TDE requires a database encryption key (DEK) and any certificate that you may have used
for the DEK. You will need this key when restoring your backups.
- If you are using TDE, instant file initialization is disabled. Instant file initialization is a
feature of Windows Server 2003 that SQL Server 2005 can take advantage of where database growth
times are extremely fast, as the underlying space in the file system does not need to be zeroed
out. If you are log shipping or database mirroring a transparent data encryption database, TDE will
need to be enabled on the secondary, or mirror server.
- FILESTREAM data will not be encrypted. FILESTREAM is a feature of SQL Server 2008 where
varbinary columns can be stored in the file system and asynchronously streamed to the client.
- Read-only file groups in your database will have to be made writable to enable TDE to encrypt
the database contents. They can then be made read-only again.
- Enabling a database for transparent data encryption may take some time, and some database
operations will not be enabled during this conversion period. Consult Microsoft's page on understanding TDE
for more information on what these limitations are.
- Replication is "TDE unaware", and replicated data will not be encrypted. In other words,
replication network traffic will be plain text as always, as will the replication snapshot files.
The DBA will need to account for this in the compliance effort.
- Full-text indexing will extract textual data from varbinary and image columns into the file
system momentarily during the index process. This data will be plain text and not encrypted.
Microsoft recommends that you do not full-text index data stored in the varbinary/image
columns.
Enabling transparent data encryption in SQL Server
2008
To enable TDE you will fist need to create a Service Master Key (SMK). To do this, use the
following statement in your master database:
Create Master Key Encryption By Password = 'MyPassword'
You will then need to protect the DEK with a certificate which you will be able to transfer to
another server should you need to restore the TDE protected database there. You can achieve this by
using the following statement:
CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'My Certificate'
You will then need to backup the certificate into the file system, along with the private key.
Ensure that you keep both of these files in a secure, known location. If you loose these files you
will be unable to restore your database and read its contents.
BACKUP CERTIFICATE MyCertificate TO FILE = 'c:\temp\MyCertificateBackup.bck'
WITH PRIVATE KEY (
FILE = 'c:\Temp\MyPrivateKey.key',
ENCRYPTION BY PASSWORD = 'MyPassword');
You will now need to create a database encryption key encrypted with the above certificate.
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE MyCertificate
Now you can enable transparent data encryption on your database by using the following
command:
ALTER DATABASE myDatabase SET ENCRYPTION ON
Finally, you can monitor the progress or state of the encryption conversion by querying the
following DMV:
Select db_name(database_id), encryption_state from
sys.dm_database_encryption_keys
The important thing to remember about transparent data encryption for SQL Server is that it's
not a one stop encryption solution. It also does not encrypt sensitive data in your database, but
rather the data files and backups. You will still need to protect sensitive data by encrypting
individual columns to only allow authorized people to view them.
ABOUT THE AUTHOR
Hilary Cotter, SQL Server MVP, has been involved in IT for more than 20 years as a
Web and database consultant, and has worked with SQL Server for 11 years. Cotter is Director of
Text Mining at RelevantNoise, dedicated
to indexing blogs for business intelligence. Microsoft first awarded Cotter the Microsoft SQL
Server MVP award in 2001. Hilary Cotter can be contacted at [email protected].
Disclaimer:
Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.
