Encrypting A Database Using Transparent Data Encryption In SQL Server

Knowing how to encrypt a database can be a valuable skill when dealing with sensitive data.  In many organizations, database encryption may not be standard practice.  However, in some cases you may find it a useful tool should you need to create a backup and store it offsite or send a copy to a 3rd party.  Transparent Data Encryption allows you to encrypt an entire database by using a Database Encryption Key, or DEK that gets stored in the database boot record.  There are multiple levels of protection available with this method as the DEK is protected by the database master key and the database master key is protected by the service master key.

There are several necessary steps you need to perform in order to successfully encrypt your database.  Additionally, it is always a good idea to create a backup of the server certificate in the master database.  If you do not create a backup of the server certificate and the database server goes down you will not be able to access the data in the TDE encrypted database.

The first step is to create a master encryption key.  The syntax for performing most of these steps is pretty straight forward.  The script below demonstrates creating a master encryption key.

Here I am using a very simple password.  If you were encrypting a sensitive production database you would likely want to use a stronger password.  If you would like to learn more about automating the creation of strong passwords see my post on Using SQL To Create A Randomly Generated Password With Custom Length.

Once the master key has been created, the next step is to create the certificate that SQL Server will use to actually encrypt the database.  The following script will handle this.

Now that you have created the master encryption key and certificate you should back up the certificate to the master database as discussed previously.  The following script will do this for you.  Its worth noting that the password used to back up the certificate does not have to be the same as the one used to create the master encryption key.  In fact, I recommend using a different password if you are encrypting a production database.

Once the certificate has been backed up to the master database you can now create the database encryption key for the database you want to encrypt.  For the purposes of this example I will be encrypting the AdventureWorks2012 database.

The following script will create the database encryption key.  There are several algorithms available to use within SQL Server but the standard is 128-bit AES encryption.

The final step is to encrypt the database.  To do this, simply alter the database and set ENCRYPTION to ON as shown below.

Your database should now be encrypted.  There are several ways to verify this but I find the script below to be the quickest.  I have created this script to simplify the translation of the encryption_state on the sys.dm_databae_encryption_keys dynamic management view.

When you run this script you should, at the very least, see two databases listed.  Whenever a database is encrypted using Transparent Data Encryption, the TempDB database is also encrypted.

You can also verify the physical location of the certificate and backup certificate by navigating to the default backup directory C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA


It is possible your location could be different but if you did not make any modifications to the location it should be here.

Database encryption is a vital tool for managing the security of your database.  I hope this helps you better understand the steps and techniques required to successfully encrypt your SQL Server databases.