Microsoft SQL Server 2019
How active encryption on SQL servers databases?
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.