Transparent Data Encryption

Transparent data encryption (TDE) is a feature in SQL server to secure the database by encrypting entire SQL server with real-time I/O encryption and decryption of data and log files. With TDE, it can prevent malicious party who steals physical media like drives or backup tapes can restore or attach the database and browse its data.


Working of Transparent Data Encryption

TDE Architecture
  1. Operating System Level
    2.

    At this level (the lowest level of database), it will encrypting the Service Master Key with DPAPI (Windows feature used to protect sensitive data, like encryption keys).

    The Service Master Key is crucial because it provides the top layer of encryption management for the entire SQL Server instance.

  2. SQL Server Instance Level
    3.

    Each database instance has a "master" and it contain a Database Master Key. It will be encrypted by the Service Master Key.

  3. Master Database Level
    4.

    A certificate is needed to encrypt the Database Encryption Key (DEK) when performs TDE. The Database Master Key is responsible for creating the certificate.

  4. Certificate Generation
    5.

    The certificates will act as a middle layer of security which uses to encrypt the Database Encryption Key in user database.

  5. User Database Level
    6.

    Database Encryption Key (DEK) is created in this level to encrypt the actual data in user database.

  6. Data Encryption
    7.

    Once the DEK is in place, the entire user database is encrypted using the DEK by setting the database encryption on with the SQL command


Configuration of TDE in SQL Server

Backup the certifications and encryption key (DEK) is an important things to process data recovery and database restoring. If you need to restore the database to another server (for instance, during disaster recovery or server migration), you will need the certificate and its private key to access the encrypted data. If you lost these backups, you won't be able to attach or restore the encrypted database files.

Since TDE is configured, now its only able to restore the database with certificate and DEK. If you want to restore the database in other instance, you should do the following steps.

  1. Copy the backup certificate and DEK to new instance path
    2.
    Copy the backup certificate and DEK to new instance path
  2. Create master Key and certificate inside instance
    3.
    Create master Key and certificate inside instance
  3. Restore the database
    4.
    Restore the database
    Restore the database

References