SQL Server: How to enable Transparent Data Encryption (TDE) on a Database?

In the previous post, I had shared basic details about the Transparent Data Encryption (TDE) of the SQL Server.

Whenever we require encrypting of our data or database, we should enable the TDE on a SQL Server Database Level.
After enabling, It will encrypt data files, page files, and backup files of the database.

Below are the necessary steps and T-SQL script to enable TDE on a Database.


  • Create a master key.
  • Create or obtain a certificate protected by the master key.
  • Create a database encryption key and protect it by the certificate.
  • Set the database to use encryption.

    Sample script to enable TDE:

    Create the master key in Master Database:

    Create a certificate:

    Use the database to enable TDE:

    Associate the certificate to the Database:

    Enable encryption on the Database:

    Anvesh Patel

    Leave a Reply

    Be the First to Comment!

    Notify of