SQL Server 2012 : Encryption Support (part 4) - Transparent Data Encryption - Enabling TDE, Backing Up the Certificate

1/22/2014 12:39:32 AM

Enabling TDE

Once you’ve created a certificate-protected DEK for the database, you can start the encryption process. Encryption occurs in the background and does not interfere with applications that are concurrently accessing the database. Use the ALTER DATABASE statement and specify the SET ENCRYPTION ON clause to start encrypting the MyDB database, as shown in Example 4:

Example 4. Enabling transparent data encryption on a database.

-- Enable TDE on the database

-- Verify TDE is enabled on the database
SELECT name, is_encrypted
FROM sys.databases
WHERE is_encrypted = 1

The results of the query into the sys.database catalog view indicate that MyDB is the only encrypted database on the server:

name                           is_encrypted
------------------------------ ------------
MyDB 1


Encrypting one or more databases results in the encryption of tempdb as well. This can have a performance impact for unencrypted databases on the same server instance. Because the encryption in tempdb is implicit, is_encrypted is returned as 0 (false) by sys.databases for tempdb.

From this point forward, the MyDB database and all of its backups will be encrypted. If an unauthorized party somehow gains access to the physical media holding any backups, the backups will be useless without the certificate protecting the DEK.

You can also query the dynamic management view sys.dm_database_encryption_keys to see all the DEKs and to monitor the progress of encryption (or decryption, when you disable TDE) running on background threads managed by SQL Server. This view returns the unique database ID that can be joined on sys.databases to see the actual database name. For example, if you run the query in Example 5 after enabling TDE, you can obtain information about the DEK and background encryption process.

Example 5. Querying for DEKs and encryption progress.

-- Monitor encryption progress
sys.dm_database_encryption_keys INNER JOIN sys.databases
ON sys.dm_database_encryption_keys.database_id= sys.databases.database_id

If this query is executed after you enable TDE but before SQL Server has completed encrypting the entire database in the background, you will get results similar to the following (note that the database in the current example is so small that the encryption will complete too quickly for you to ever see an in-progress result such as this):

name       encryption_state percent_complete key_algorithm    key_length
---------- ---------------- ---------------- ---------------- -----------
MyDB 2 78.86916 AES 128

(1 row(s) affected)

The value returned by encryption_state tells you the current status of encryption (or decryption), as follows:

  • 1 = Unencrypted

  • 2 = Encryption in progress

  • 3 = Encrypted

  • 4 = Key change in progress

  • 5 = Decryption in progress (after ALTER DATABASE…SET ENCRYPTION OFF)

Certain database operations cannot be performed during any of the “in progress” states (2, 4, or 5). These include enabling or disabling encryption, dropping or detaching the database, dropping a file from a file group, taking the database offline, or transitioning the database (or any of its file groups) to a READ ONLY state.

Backing Up the Certificate

It is extremely important to back up the server certificates you use to encrypt your databases with TDE. Without the certificate, you will not be able to access the encrypted database or restore encrypted database backups (which, of course, is the point of TDE). Attempting to restore an encrypted database without the certificate will fail with an error similar to this from SQL Server:

Msg 33111, Level 16, State 3, Line 1
Cannot find server certificate with thumbprint '0x6B1FEEEE238847DE75D1850FA20D87CF94F71F33
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In addition to the certificate itself, the certificate’s private key must also be saved to a file and protected with a password. Use the statement in Example 6 to back up the server certificate to a file and its private key to a separate password-protected file.

Example 6. Backing up the TDE certificate and its private key.

USE master

BACKUP CERTIFICATE MyEncryptionCert TO FILE='C:\Demo\Backups\MyEncryptionCert.certbak'

This statement creates two files: MyEncryptionCert.certbak is a backup of the server certificate, and MyEncryptionCert.pkbak is a backup of the certificate’s private key protected with the password Pr!vK3yP@ssw0rd. Password protection is absolutely required when backing up the certificate’s private key. Both of these files and the password will be needed to restore an encrypted database backup onto another server or instance. At the risk of stating the obvious, these backup files and the private key password should be closely safeguarded.


You can—and some would say, should—add an extra layer of protection by encrypting the private key using a separate password (rather than by just the master key) at the time you create a certificate. This can be done using the ENCRYPTION BY PASSWORD clause in the CREATE CERTIFICATE statement. In such a case, you would need to provide that password with the DECRYPTION BY PASSWORD clause in your BACKUP CERTIFICATE statement, or the statement will fail. As a result, no one could then back up the certificate without knowing its private key password.

Restoring an Encrypted Database

Before an encrypted database can be restored elsewhere, the server certificate that its DEK is encrypted by must be restored first. And if the target instance does not have a DMK, one must be created for it before the server certificate can be restored, as shown in Example 7:

Example 7. Creating a new database master key on the target SQL Server instance.

-- Simulate disaster requiring recovery to a new server
USE master

-- Lose the database

-- Lose the TDE certificate

-- Lose the DMK and create a new one

To restore the server certificate from the backup files made in Example 6, use an alternative form of the CREATE CERTIFICATE statement, as shown in Example 8:

Example 8. Restoring the TDE certificate from its backup files.

FROM FILE='C:\Demo\Backups\MyEncryptionCert.certbak'

This statement restores the MyEncryptionCert server certificate from the certificate backup file MyEncryptionCert.certbak and the certificate’s private key backup file MyEncryptionCert.pkbak. Naturally, the password provided in the DECRYPTION BY PASSWORD clause must match the one that was used when the certificate’s private key was backed up or the certificate will fail to restore. With a successfully restored certificate, you can then restore backups of MyDB, or any other encrypted database whose DEK is based on the MyEncryptionCert certificate.

  •  SQL Server 2012 : Authentication and Authorization (part 2) - User-Schema Separation,Execution Context
  •  SQL Server 2012 : Authentication and Authorization (part 1) - How Clients Establish a Connection, Password Policies
  •  SQL Server 2012 : SQL Server Security Overview
  •  SQL Server 2012 : Working with Transactions - Transactions in SQL CLR (CLR Integration)
  •  SQL Server 2012 : Distributed Transactions (part 4) - Using a Resource Manager in a Successful Transaction
  •  SQL Server 2012 : Distributed Transactions (part 3) - Distributed Transactions in the .NET Framework - Writing Your Own Resource Manager
  •  SQL Server 2012 : Distributed Transactions (part 2) - Distributed Transactions in the .NET Framework
  •  SQL Server 2012 : Distributed Transactions (part 1) - Distributed Transaction Terminology, Rules and Methods of Enlistment
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 6) - Reversing the Implementation of TDE
  •  Protecting SQL Server Data : TRANSPARENT DATA ENCRYPTION (part 5) - Verifying TDE - Verification through Backup and Recovery
    Video tutorials
    - How To Install Windows 8 On VMware Workstation 9

    - How To Install Windows 8

    - How To Install Windows Server 2012

    - How To Disable Windows 8 Metro UI

    - How To Change Account Picture In Windows 8

    - How To Unlock Administrator Account in Windows 8

    - How To Restart, Log Off And Shutdown Windows 8

    - How To Login To Skype Using A Microsoft Account

    - How To Enable Aero Glass Effect In Windows 8

    - How To Disable Windows Update in Windows 8

    - How To Disable Windows 8 Metro UI

    - How To Add Widgets To Windows 8 Lock Screen
    programming4us programming4us
    Top 10
    Free Mobile And Desktop Apps For Accessing Restricted Websites
    MASERATI QUATTROPORTE; DIESEL : Lure of Italian limos
    TOYOTA CAMRY 2; 2.5 : Camry now more comely
    KIA SORENTO 2.2CRDi : Fuel-sipping slugger
    How To Setup, Password Protect & Encrypt Wireless Internet Connection
    Emulate And Run iPad Apps On Windows, Mac OS X & Linux With iPadian
    Backup & Restore Game Progress From Any Game With SaveGameProgress
    Generate A Facebook Timeline Cover Using A Free App
    New App for Women ‘Remix’ Offers Fashion Advice & Style Tips
    SG50 Ferrari F12berlinetta : Prancing Horse for Lion City's 50th