Transparent Data Encryption (TDE) is a feature in SQL Server that encrypts data at rest, specifically data and log files. When TDE is enabled on a database, it reads the page from the data files to the buffer pool, encrypts the page, and writes it back to disk. This provides an additional layer of security for your sensitive data.

To enable TDE on a database, you need to follow these steps:

1. Create a master key
2. Create a certificate in the master database
3. Create a database encryption key (DEK)
4. Enable encryption on the database

Creating a Master Key

The first step in enabling TDE is to create a master key in the master database. The master key is encrypted by the service master key at the instance level. Here is an example of how to create a master key:

USE master
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPassword';
GO

Creating a Certificate in the Master Database

After creating the master key, you need to create a certificate in the master database. The certificate is protected by the database master key created in the previous step. Here is an example of how to create a certificate:

CREATE CERTIFICATE MyCertificate WITH SUBJECT = 'Used to encrypt MyDatabase';
GO

Creating a Database Encryption Key (DEK)

Once the certificate is created in the master database, you can create a database encryption key (DEK) in the user database. The DEK is encrypted by the certificate created in the previous step. Here is an example of how to create a DEK:

USE MyDatabase
GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyCertificate;
GO

Enabling Encryption on the Database

After creating the DEK, you can enable transparent data encryption (TDE) on the database. Here is an example of how to enable TDE:

ALTER DATABASE MyDatabase SET ENCRYPTION ON;
GO

You can also enable encryption by setting the value to true for the option “Enabled Encryption” in the database properties.

To check the encryption status of a database, you can use the dynamic management view “sys.dm_database_encryption_keys”. Here is an example of how to check the encryption status:

SELECT DB_NAME(database_id) AS DatabaseName, encryption_state_desc AS EncryptionStatus
FROM sys.dm_database_encryption_keys;

Once the database is encrypted, you can see the encryption status as “Encrypted” in the result.

Conclusion

In this article, we have learned how to enable Transparent Data Encryption (TDE) on a database in SQL Server. By following the steps outlined above, you can add an extra layer of security to your sensitive data. If you have any questions, please feel free to ask in the comments section below.