Product:
Microsoft SQL Server 2019
Issue:
How active encryption on SQL servers databases?

Solution:

You need a folder on the SQL server to store the certificate, create a folder like e:\key and only give local administrators and the SQL service account access there.

You can use the same certificate for a group of SQL servers. Then it is possible to restore a database backup to one of the others server in that group – that use the same certificate.

One the first SQL server:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere12!';
GO

CREATE CERTIFICATE TDE_Cert
WITH
SUBJECT='Database_Encryption';
GO

BACKUP CERTIFICATE TDE_Cert
TO FILE = 'e:\key\TDE_Cert.cer'
WITH PRIVATE KEY (file='e:\key\TDE_CertKey.pvk',
ENCRYPTION BY PASSWORD='InsertStrongPasswordHere12!')

 

Then on every other SQL server in the group , copy above files to the e:\key folder on the next server, and do this to activate TDE:

USE Master;
GO
CREATE MASTER KEY ENCRYPTION
BY PASSWORD='InsertStrongPasswordHere12!';
GO

USE MASTER
GO
CREATE CERTIFICATE TDE_Cert
FROM FILE = 'e:\key\TDE_Cert.cer'
WITH PRIVATE KEY (FILE = 'e:\key\TDE_CertKey.pvk',
DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!' );

 

Then to enable the encryption, you need to run this on every database:

USE <DB>
GO
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert;
GO

ALTER DATABASE <DB>
SET ENCRYPTION ON;
GO

Replace <DB> with your database name.

 

Then the database and its coming backup files are encrypted. The Backup can only be restored on a server with the same certificate.

 

If you get an error like this:

The certificate, asymmetric key, or private key file is not valid or does not exist; or you do not have permissions for it.

Try by change the path from file=‘e:\key\TDE_CertKey.pvk’ to file=’e:/key/TDE_CertKey.pvk’

 

To see what databases are encrypted:

SELECT name,is_encrypted,* FROM sys.databases WHERE is_encrypted = 1

To check if the certificate is installed:

SELECT * FROM sys.certificates WHERE name = 'TDE_Cert'

 

Important: Keep your password and backup of the certificates files in a secure location. In case you need to restore a database to a new SQL server, this keys need to be restored first.

To remove encryption from a database:

ALTER DATABASE [RecoveryWithTDE]
  SET ENCRYPTION OFF;
GO

USE [RecoveryWithTDE]
GO 
DROP DATABASE ENCRYPTION KEY;

To backup the master key:

USE Master ;
Open Master Key Decryption by password = 'InsertStrongPasswordHere12!'
Backup master key to file = 'e:\key\MasterKeyName.key'
        ENCRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!';
    GO

To restore the master key to the database server:

Use master 
    restore master key
    FROM FILE = 'e:\key\MasterKeyName.key'
    DECRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!'
    ENCRYPTION BY PASSWORD = 'InsertStrongPasswordHere12!'

 

The TEMPDB database will be encrypted when you start using TDE, then it is always encrypted.

Cannot encrypt a system database. Database encryption operations cannot be performed for ‘master’, ‘model’, ‘tempdb’, ‘msdb’, or ‘resource’ databases.

More Information:

https://www.databasejournal.com/ms-sql/suspending-and-resuming-transparent-data-encryption-tde/

https://www.sqlshack.com/how-to-configure-transparent-data-encryption-tde-in-sql-server/

https://www.sqlservertutorial.net/sql-server-administration/sql-server-tde/

https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16