Can’t change the database encryption key because no database encryption key set

This error message occurs when you are trying to change the database encryption key for Transparent Data Encryption (TDE) in SQL Server, but TDE has not been enabled on the database. TDE is a feature in SQL Server that provides encryption for the entire database, including the transaction log.

To resolve this error, you must first enable TDE on the database. Here’s how:

1. Connect to the database using SQL Server Management Studio (SSMS).
2. Right-click the database you want to encrypt and select Properties.
3. In the Properties window, click on the Options page.
4. In the dropdown menu next to “Encryption enabled”, select True.
5. Click OK to save the changes.

CREATE CERTIFICATE YourTDECertificateName
WITH SUBJECT = 'Your TDE Database Encryption Name';

USE YourDatabase;

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE YourTDECertificateName;

Once TDE is enabled on the database, you can then create an encryption key and set it as the database encryption key. The process for doing this will depend on the specific version of SQL Server that you are using, but it typically involves using the CREATE CERTIFICATE and ADD ENCRYPTION BY CERTIFICATE commands in T-SQL.

In conclusion, the “no database encryption key set” error occurs when you are trying to change the database encryption key for TDE, but TDE has not been enabled on the database. To resolve this error, you must first enable TDE on the database, and then create an encryption key, and set it as the database encryption key.

Note: Make your that your TDE Certificate Name same as you are writing the SQL code is same.