SQL Server 2008 Transparent Data Encryption getting started

By:   |   Updated: 2008-06-06   |   Comments (19)   |   Related: 1 | 2 | 3 | 4 | 5 | 6 | > Encryption


Problem

While reviewing the new features in SQL Server 2008, we noticed Transparent Data Encryption.  This sounds very interesting.  Could you provide us with an explanation and the details to implement it?

Solution

TDE is a new feature in SQL Server 2008; it provides real time encryption of data and log files.  Data is encrypted before it is written to disk; data is decrypted when it is read from disk.  The "transparent" aspect of TDE is that the encryption is performed by the database engine and SQL Server clients are completely unaware of it.  There is absolutely no code that needs to be written to perform the encryption and decryption.  There are a couple of steps to be performed to prepare the database for TDE, then the encryption is turned on at the database level via an ALTER DATBASE command.

We've probably all heard of incidents where backup tapes containing sensitive information have been lost or stolen.  With TDE the backup files are also encrypted when using just the standard BACKUP command once encryption is turned on for the database.  The data in the backup files (or on disk) is completely useless without also having access to the key that was used to encrypt the data.

Before we dive in to the steps to implement TDE, let's take a minute to discuss encryption at a very high level.  The Wikipedia definition of encryption is "the process of transforming information (referred to as plaintext) using an algorithm (called cipher) to make it unreadable to anyone except those possessing special knowledge, usually referred to as a key".  To encrypt some data, I choose an available algorithm, supply a key and I now have encrypted data.  To decrypt the encrypted data, I choose the same algorithm and supply the key.  The security provided by encryption is based on the strength of the algorithm and protection of the key.  There are two types of keys - symmetric and asymmetric.  With a symmetric key, the same value is used to encrypt and decrypt the data.  An asymmetric key has two components - a private key and a public key.  I use the private to encrypt data and someone else must use the public key to decrypt the data.  To recap, the symmetric key or private key of the asymmetric key pair must be stored securely in order for encryption to be effective.

Now let's walk through an example of how to implement TDE.  Books on Line lists the following four steps to implement TDE on a particular database:

  • Create a master key
  • Create or obtain a certificate protected by the master key
  • Create a database encryption key and protect it by the certificate
  • Set the database to use encryption

Create a Master Key

A master key is a symmetric key that is used to create certificates and asymmetric keys.  Execute the following script to create a master key:

USE master;
CREATE MASTER KEY 
ENCRYPTION BY PASSWORD = 'Pass@word1';
GO

Note that the password should be a strong one (i.e. use alpha, numeric, upper, lower, and special characters) and you have to backup (use BACKUP MASTER KEY) and store it in a secure location.  For additional details on master keys refer to our earlier tip Managing SQL Server 2005 Master Keys for Encryption.

Create a Certificate

Certificates can be used to create symmetric keys for data encryption or to encrypt the data directly.  Execute the following script to create a certificate:

USE master;
CREATE CERTIFICATE TDECert 
WITH SUBJECT = 'TDE Certificate'
GO

Note that certificates also need to be backed up (use BACKUP CERTIFICATE) and stored in a secure location.  For additional details on certificates, refer to our earlier tip SQL Server 2005 Encryption - Certificates 101.

Create a Database Encryption Key

A database encryption key is required for TDE.  Execute the following script to create a new database and a database encryption key for it:

CREATE DATABASE mssqltips_tde
GO
USE mssqltips_tde;
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECert
GO

In order to work with TDE the encryption key must be encrypted by a certificate (a password will not work) and the certificate must be located in the master database. 

Enable TDE

The final step required to implement TDE is to execute the following script:

ALTER DATABASE mssqltips_tde
SET ENCRYPTION ON
GO
SELECT [name], is_encrypted FROM sys.databases
GO

You can query the is_encrypted column in sys.databases to determine whether TDE is enabled for a particular database.

Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2008-06-06

Comments For This Article




Friday, February 26, 2021 - 9:58:25 AM - RAYMOND BARLEY Back To Top (88306)
For error number 33106 the message is Cannot change database encryption state because no database encryption key is set. Review the steps to make sure they have all been completed; in particular the Create a Database Encryption Key step.

I didn't find error number 3317 in sys.messages.

Friday, February 26, 2021 - 6:57:24 AM - Ray Barley Back To Top (88304)
The best thing to do to try and determine what's wrong is to go to this link: https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver15

It's from 2019 so it's reasonably up to date. The commands look the same but I may have missed something.

When you get TDE enabled backups are automatically encrypted because the database is encrypted. You do not need to specify any encryption options when you perform the backup.

Wednesday, February 24, 2021 - 4:25:56 PM - ome Back To Top (88297)
Hi, i am wondering is this post still legit? As Microsoft said encrypted backup starts from SQL 2014. i am trying to encrypt database using same quires but last part of creating Encryption Key and Enable TDE is not working. Fails with error Msg 33106 and 3317.

Thanks

Monday, June 9, 2014 - 3:10:34 PM - Ray Barley Back To Top (32160)

According to http://msdn.microsoft.com/en-us/library/bb934049%28v=sql.105%29.aspx "A certificate that has exceeded its expiration date can still be used to encrypt and decrypt data with TDE" This is for SQL Server 2008R2.  I would be careful relying on this because if you don't have the certificate you cannot access the database.

Monday, June 9, 2014 - 12:52:13 PM - Stuart Housden Back To Top (32158)

I am wondering what would happen if the certificate expired.  E.g. I encrypt a database that isn't used often (e.g. in a testing environment) and then when I come back to use it again the certificate is expired.  Can I still access the database?  If not, can I recover the situation by creating a new certificate?

Many thanks,

Stuart

Thursday, January 24, 2013 - 10:20:10 AM - Ray Barley Back To Top (21683)

I'm not sure if you have to do that but certainly it would be the conservative approach.

This is an interesting scenario that I will research as time permits.

Wednesday, January 23, 2013 - 9:36:23 AM - Wayne Back To Top (21646)

I was wondering if there is anything I need to do when moving a server with encrypted databases from one domain to a new domain. Should I remove encryption of all the databases, move the server to the new domain, and then encrypt the databases again?

Thank you,

Wayne

Sunday, January 6, 2013 - 7:02:45 PM - Ned Back To Top (21282)

Hey Ray. This is teriffic. Thanks for the clear explanations. I'm basically a programmer, but a complete newbie when it comes to Security and Encryption. We have a number of at rest databases in the organization I work for which are only used for internal purposes through in house developed applications.  We want to provide some level of security since at the moment we have none :-) It sounds like TDE would be a good first step at providing protection to our data without impacting the applications. Is this a correct assumption?

Also and please forgive this stupid question, but are there easy ways to protect data that flows from an application to a database through let's say an update statement? In other words if I have a simple ASP application running on a Server within our organization (no external facing) and enter my name and address in a form and then click OK to submit that data to the database, is my name and address being sent as plain text from the application server to the database server.

 

Thanks again and sorry for these questions.

Ned

Monday, November 19, 2012 - 3:32:56 PM - Ray Barley Back To Top (20416)

The encryption is performed automatically as you insert or update data.  The only thing you can do is query to see whether the database is encrypted or not; e.g.

SELECT [name], is_encrypted FROM sys.databases

will show you each database and whether it is encrypted or not

There is nothing needed in the connection string as far as encryption is concerned 

 

Sunday, November 18, 2012 - 11:13:54 PM - Bhudev Arya Back To Top (20402)

How i will make a connection string in visyal studio 2010 application to connect Encrypted Database. I want to know that after encrypted database the name of Table, its columns, stored procedure's name and their defination will be encrype or it will as readable by anyone.

Sunday, October 14, 2012 - 8:23:39 AM - Ray Barley Back To Top (19914)

This tip covered transparent data encryption which is at the database level.  

 

As best as I can tell there is no way to determine whether a column of data is encrypted when you are using the built-in ENCRYPTBY functions in SQL Server.  I think your only choice is a custom solution.  You could stored procedures for all of your selects, inserts and updates so that you are certain that the column is encrypted on every insert and update and decrypted on every select.  Maybe you could use a trigger to make sure that the column get encrypted on every insert and update.

 

Saturday, October 13, 2012 - 4:42:16 AM - Shekhar Back To Top (19905)

Thanks Ray...

If we encrypt a particular column in a table.. then is there a way to find before running script, data is Encrypted or decrypted in script. Problem is if data is decrypted and use to decrypted it agian it returns null. If perfom update to decrypt data (Encrypt/Decrypt update on same column) on same column all data get lost. So if somehow i can find in script that data is decrypted, we can bypass decryption step so data will not lost. Currenly using 'YES'/'NO' flag in some Reference table.

Thursday, October 11, 2012 - 6:35:22 PM - Ray Barley Back To Top (19884)

use <yourdatabasename>

go

 

SELECT [name], is_encrypted FROM sys.databases
go
 
if is_encrypted = 1 then data is encrypted; if 0 then it's not encrypted

Thursday, October 11, 2012 - 1:42:43 PM - shekhar Back To Top (19881)

Hi, I have one query.. how we can know data is already encrypted OR data data is already decrypted so that a generic procedure do not try to encrypt already encrypted data or vice-versa.. 

 

Tuesday, June 12, 2012 - 2:23:59 PM - Ray Barley Back To Top (17948)

Transparent data encryption is really the solution for making sure that someone can't read the data from outside of SQL Server.  In other words the data in the database files is encrypted and if you can access the file you can't read the data because you don't know how to decrypt it.  The transparent part is that SQL Server automatically encrypts and decrypts the data for users who have permission to access it.

User premissions can be handled n SQL Server Management Studio.  Connect to a SQL Server instance then expand the Security node in the Object Explorer.  The security model begins with logins that are given certain roles and access to databases, tables, stored procedures, etc.

There is a whole page of security tips here: http://www.mssqltips.com/sql-server-tip-category/19/security/

Tuesday, June 12, 2012 - 12:34:24 PM - stiej1977 Back To Top (17947)

So how do you allow only certain users to read the databases data, even excluding syadmins? i've tried the above, and although the db gets encrypted as you'd expect, it hasnt prevented any users on the system from reading the data held in the encrypted db. how do i achieve that?

Wednesday, April 11, 2012 - 6:23:49 PM - Ray Barley Back To Top (16861)

The CREATE CERTIFICATE T-SQL command has start_date and expiry_date parameters that you can specify for the time that the certificate is valid.  The default as start_date = current datetime and expiry_date = current datetime + 1 year.  It's probably a good idea to at least specify the expiry_date when you create a certificate.

 

You can get the details for CREATE CERTIFICATE here: http://msdn.microsoft.com/en-us/library/ms187798.aspx

 

Wednesday, April 11, 2012 - 5:25:30 PM - Mike Back To Top (16860)

Great write up.  Does the certificates need to be renewed or does it ever exipire?  Thanks.

Tuesday, July 19, 2011 - 3:13:17 PM - Ricardo Back To Top (14219)

Just a small correction. Where it says "I use the private to encrypt data and someone else must use the public key to decrypt the data", actually it's the other way around. You or anyone encrypt with the public key, and the owner of the private key decrypts with the private key.














get free sql tips
agree to terms