Enabling Transparent Data Encryption on SQL Server 2014

Download PDF

To start with for Transparent Data Encyrption (TDE) we will need to be using Enterprise (or Developer) edition of SQL Server 2014. I am using SQL Server 2014 Enterprise Edition CTP1, which is the early release of SQL Server 2014.

Next we need a database to work with.  I will just use the Sample Database for Common Table Expressions, a simple database for TDE with just a couple tables. After downloading the script and running it, we can see the following in the Object Exporer.

TDE14_1

Encrypting the Database

First lets set up the master key to be used for encryption, then create a certificate for the database we are going to encrypt.


USE master;
GO
CREATE MASTER KEY ENCRYPTION
    BY PASSWORD = 'BeSureToUseABetterPasswordThanThis!!!!!!';
GO
CREATE CERTIFICATE cteDemoCert
  WITH SUBJECT = 'cte_demo Certificate';

The next step is to create the encryption key for the database being encrypted. In this case we are encrypting the cte_demo database.

USE cte_demo;
GO

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = TRIPLE_DES_3KEY
ENCRYPTION BY SERVER CERTIFICATE cteDemoCert;

As soon as you run this you will be shown a warning stating that key has not been backed up and that you should back it up.
TDE14_2

Warning: The certificate used for encrypting the database encryption key has not been backed up. You should immediately back up the certificate and the private key associated with the certificate. If the certificate ever becomes unavailable or if you must restore or attach the database on another server, you must have backups of both the certificate and the private key or you will not be able to open the database.

Now lets backup the certificate as the warning suggests:

USE master;
GO
BACKUP CERTIFICATE cteDemoCert TO FILE = 'c:\Backups\cteDemoCert';

And the final step is to turn on TDE encryption for the database. This may take a while for larger databases.


USE cte_demo;
GO
ALTER DATABASE cte_demo SET ENCRYPTION ON;

And to check to see if everything worked, we can just query the sys.databases table to see if we the cte_demo database is now encrypted.

SELECT NAME, IS_ENCRYPTED
 FROM sys.databases;

TDE14_4From there we can see that the cte_demo database has been encrypted with transparent data encryption. Be sure to make a full database backup at this time and to safely store your backed up encryption key.

The same script will also work on SQL Server 2012.

SQL Server 2014 related articles:

Tagged with: , , , , , ,
11 comments on “Enabling Transparent Data Encryption on SQL Server 2014
  1. Nicholas Pryor says:

    Is it possible to use a PKI cert from out existing AD CS infrastructure?

  2. Mackan says:

    I cannot see that the tempdb is encrypted. If I want encryption enabled, I also want tempdb to be encrypted. how do I enable that ?
    Do I have to manually set that following the steps above on the tempdb ?

    • Pavel says:

      Did you restart the SQL service. If you do then you will see it is encrypted and there is no additional task due to encrypt tempdb.

  3. SteveStedman SteveStedman says:

    Transparent Data Encryption and TempDB

    According to Microsoft: The tempdb system database will be encrypted if any other database on the instance of SQL Server is encrypted by using TDE.

  4. lakshmi says:

    HI Steve , I have been trying to do TDE on a SQL 2014 database and havenot been successful yet.
    I am having errors when I try to backup the master certificate.
    No matter where I point to ( making sure that the sql server service account has priveleges I get his error message.
    Cannot write into file ‘D:\SQLCertificate.cert’. Verify that you have write permissions, that the file path is valid, and that the file does not already exist.

    Do you have nay idea on to fix this . Thank you in advance

    • SteveStedman SteveStedman says:

      Usually the service account that the SQL Server Service is running under needs full read/write permissions on the file system.

      Not sure why it is always trying to write to D:\, but I would start by checking the permissions of The SQL Server Service Account.

      -Steve Stedman

  5. Fred Hunter says:

    You mention backing up the certificate, do you also need to back up the private key? If so, how is that done?

    Thank you!

  6. Miles says:

    Steve – I have two questions
    1. If there is already data in the target TDE database, will the data be encrypted? Do I need to “export” > enable encryption > “import” the data in order to make certain I have all data encrypted?

    2. Is there a something in cert script where the cert does not expire?

  7. Mike Rickerts says:

    Steve,
    Does a third party cert provide “more“ security than a self-signed certificate and if so, how do we get a Certificate Signing Request (CSR) created to be used by the CA to generate the certificate? What options are necessary for the CSR?

    Thanx,
    Mike

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.