How to Configure Database Encryption | Enable Transparent Data Encryption (TDE) in SQL Server

Transparent Data Encryption (TDE) is a feature in SQL Server that encrypts the entire database (data and log files) at the storage level. TDE protects the database from media theft and ensures that data cannot be read or written to disk in an unencrypted format. TDE uses the Advanced Encryption Standard (AES) algorithm with a 256-bit key to encrypt the database. It also encrypts the database backups, so that they can be safely stored off-site.

With TDE, encryption and decryption of the data occurs at the page level, and it is transparent to the applications and users accessing the data. This means that the SQL Server database engine handles the encryption and decryption process, and the application does not need to make any changes to support TDE.

TDE is available in all DataCenter/ Standard Edition /Enterprise Edition of SQL Server and this feature is available from SQL Server 2008 and later upgrade versions.

To configure database encryption on SQL Server, you can use the built-in encryption feature called Transparent Data Encryption (TDE). Here are the general steps to enable TDE on a database:

Create a master key: This key is used to encrypt the private key of the database encryption key. You can use the following command to create a master key:

USE MASTER
GO
       CREATE MASTER KEY
       ENCRYPTION BY PASSWORD = 'your_password'
GO

For Example:

USE MASTER
GO
       CREATE MASTER KEY ENCRYPTION
       BY PASSWORD='&Daily@Learn$QL#'
GO

2. Create or obtain a certificate: This certificate is used to protect the private key of the database encryption key. You can use the following command to create a certificate:

USE MASTER
GO
     CREATE CERTIFICATE my_certificate
         WITH SUBJECT = 'TDE CertificateName';
GO

For Example:

USE MASTER
GO
     CREATE CERTIFICATE Encrypted_Cert
         WITH SUBJECT = 'Database_Encryption';
GO

3. Create a database encryption key: This key is used to encrypt the data in the database. You can use the following command to create a database encryption key:

USE DATABASENAME
GO
    CREATE DATABASE ENCRYPTION KEY
           WITH ALGORITHM = AES_256
           ENCRYPTION BY SERVER CERTIFICATE my_certificate;
GO

For Example:

USE CustomerDB
GO
    CREATE DATABASE ENCRYPTION KEY
           WITH ALGORITHM = AES_256
           ENCRYPTION BY SERVER CERTIFICATE EncryptionTDE_Cert;
GO

Fig: **Create a database encryption key**

4. Enable TDE on the database: This will encrypt the data in the database. You can use the following command to enable TDE on a database:

USE DATABASENAME
GO
    ALTER DATABASE your_dbName
    SET ENCRYPTION ON;
GO

Example:

USE CustomerDB
GO
    ALTER DATABASE CustomerDB 
    SET ENCRYPTION ON;
GO

5. Now you can see on database Encryption status using a SELECT statement

--Find TDE(Transparent Data Encryption) Certificate on SQL Server
SELECT C.NAME  FROM MASTER SYS.CERTIFICATES
 WHERE C.NAME='EncryptionTDE_Cert'

6. Backup the certificate and private key: To protect the encryption key and certificate, you should back up the certificate and private key to a secure location.

BACKUP CERTIFICATE my_certificate
    TO FILE = 'path_to_backup_file'
    WITH PRIVATE KEY (FILE = 'path_to_private_key_file',
                      ENCRYPTION BY PASSWORD = 'your_password');

Example:

BACKUP CERTIFICATE EncryptTDE_Cert
    TO FILE = 'D:\CustomerDB\EncryptTDE_Cert'
    WITH PRIVATE KEY (FILE = 'D:\CustomerDB\EncryptTDE_Cert\EncryptTDE.pvk',
                      ENCRYPTION BY PASSWORD = '&Daily@Learn$QL#');

Leave a Comment Cancel Reply