Enabling Transparent Data Encryption on SQL Server 2014
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.
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.
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;
From 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:
More from Stedman Solutions:
Steve and the team at Stedman Solutions are here for all your SQL Server needs.
Contact us today for your free 30 minute consultation..
We are ready to help!
Is it possible to use a PKI cert from out existing AD CS infrastructure?
Nicholas,
Yes, you can use a PKI cert from AD. Here is the link to a post a Microsoft describing how to do it. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/fe7ada0d-c7ea-4b70-a6d8-aae868fa0f0d/pki-certificates?forum=sqlsecurity
Steve Stedman
Thanks for pointing in me in the correct direction. Have now got it working. Took a little while as l needed to patch my SQL 2014 server to at least SP1 CU2, otherwise you cannot import the cert.
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 ?
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.
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.
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
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
You mention backing up the certificate, do you also need to back up the private key? If so, how is that done?
Thank you!
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?
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