Enabling Transparent Data Encryption on SQL Server databases

SQL Server has a built-in encryption TDE mechanism (Transparent Data Encryption) encrypts the data residing in the database or in backups on physical media.

Before you begin

TDE is only available on the Enterprise edition of Microsoft™ SQL Server 2012 and higher. For the Standard edition option, read Encrypting, backing up, and restoring a SQL Server database with EFS.

About this task

To enable TDE on SQL Server, you must have the normal permissions associated with creating a database master key and certificates in the master database. You must also have CONTROL permissions on the user database.

Enabling encryption is a common task for database administrators; for convenience, we have provided a SQL script to use which is suitable for a typical SQL Server configuration: EnableTDE.zip. (If file doesn't download, right-click the link and save the file to your hard drive.)

Note: For upgrade users:
  1. To improve database upgrade performance, enable TDE after the database upgrade has completed.
  2. While you can perform these steps at any time, the database will not be encrypted until you have completed the steps. Enabling TDE before the upgrade process will protect your database throughout the upgrade and afterwards.

Procedure

  1. Open the SQL Management Studio of your installation of SQL Server 2012 or 2014.
  2. Connect to the database you want to encrypt. This will help ensure the database has been created and is available.
  3. Go to the location where you downloaded the EnableTDE.zip file. Extract the file and open the script. (File > Open > File). You will notice several commands that will be executed on the server.
  4. Before you execute the script, you must set three fields for your environment. In the comments section of the script, they are all marked with 'ACTION REQUIRED' :
    1. DECLARE @MKPassword: The Master Key Password used to create the master key in the [master] database.
    2. DECLARE @DatabaseName : The name of the database you want to enable encryption on.
    3. (Optional) DECLARE @BackupPassword: The Certificate Backup Password. This password is used to secure the certificate backup and is required to restore the certificate on another machine.
  5. After the fields have been updated, launch the script (Query >Execute). How the script enables TDE on SQL Server.
  6. After the script has completed, the result will be displayed in the 'Messages' window of SQL Management Studio.
    Note: You can also verify through SQL Management Studio. Right-click on 'Database Name->Tasks->Manage Database Encryption. You will see that the check box for 'Set Database Encryption On is selected.

Results

Important: Once completed, be sure to write down the passwords used in this script, and make a copy of the certificate backup. The certificate backup consists of two files, AppScanEntCert.bak and AppScanEntCert.pvk. They will be stored with the database .mdf file, by default in the folder:
  • (SQL 2014) C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA
  • (SQL 2012) C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA