SQL Server – Transparent Data encryption – Steps to enable TDE with Example

In sql server 2008 r2, you can use Transparent Data Encryption (TDE) to encrypt an entire user database. TDE is used to encrypt the data and log files of the database in real time, as well as encrypting backup of the database.

TDE can be used while still adhering to laws, regulations, and standards that must be met for business requirements and database developers don not need to alter any applications. This is because the database is encrypted using an encrypt ion key that is stored in the database boot record on disk. This encrypt ion key is called the database encrypt on key and is encrypted by the database master key.


Certain considerations need taken into account when using TDE.

If database mirroring or log shipping are begin used both database are encrypted.

If any file group are read only, the encrypt ion process will fail.

If using TDE with data compression, data is compressed significantly less than if TDE was not begin used.

If FILESTREAM is enabled, it does not get encrypted

If any user database is encrypted using TDE, tempdb is automatically encrypted

If TDE is used on a database, the backup storage cannot be compressed as much as normal and therefore they should not be used together

If any user database is encrypted using TDE, data in that database will not automatically be replicated.



Several operations will be restricted during the database encrypt ion process.

While rearing for key exchange and database encrypt ion, you are not allowed to delete or detach the database, delete a file from a file group in the database, make the database or file group ready-only, or take database offline.

Restrictions on running ALTER DATABASE ENCRYPT IoN KEY, ALTER DATABASE, CREATE DATABASE ENCRYPT IoN KEY and DRop database encrypt ion key.

You cannot run these statements when you are in the process of deleting or detaching the database, taking the database offline, deleting a file from a filegroup or making the database read only.

You also cannot run these statements if you are starting to back u or restore a database or database file, using the ALTER DATABASE command or setting u a snapshot. Once you are in the process, these operations will be completely prevented.

Steps to enable Transparent data encryption in sql server

To enable TDE for a database, you first need to ensure that the database master key has been created in the master database. If one has not been created, you need to create one using the CREATE MASTER KEY Statement.


 Create Master key in sql server


once the master key is created, you need to create a certificate that is encrypted by the master key in the master database.



Create server certificate in sql server

Within the database that you want to encrypt  using TDE, you create a database encrypt ion key that encrypted using the certificate in the master database.

create database encryption key in sql server

once the database encrypt ion key is created, sql server displays a warning message. The message states that the certificate for the database encrypt ion key should be backed u along with the private key to ensure the database will be able recovered in the event that the key is lost.

When restoring a backup of a TDE-enabled database, both the encrypt ion key and private key must be available to be able to open the database. This also applies you deactivate TDE on the database because some operations may still need the key. Therefore you need to maintain backups of server certificate and the private key to prevent data loss.

Even if the certificate has expired, you can still use it to encrypt  and decrypt database on the database.

To backup the certificate and the private key, which i the database encrypt in key you need to use the BACKUP CERTIFICATE statement using the WITH PRIVATE KEY clause.


backup certificate in sql server


Once the encrypt ion keys are created, you need to enable the database for encrypt ion. you can do this using code

use master



Set encyption on for database in sql server



Using database properties

set encryption on using  database properties in sql server

The “transparent” means that the encryption is performed by the database engine and it is not visible to clients.



Leave a Reply




8 − 2 =