How to Apply Transparent Data Encryption (TDE) in SQL Server
- Imagicle UC Suite (any version)
- Microsoft SQL Server 2016, 2017, 2019 Standard/Enterprise Edition
Use this procedure if you need to apply Transparent Data Encryption (TDE) to MS-SQL Server. TDE secures data in:
- Database Data Files
- Database Transactional Log Files
- Database Snapshots
- Database Backups
Here you can find more information about what TDE is and how it works.
Before to proceed, check below points:
- Imagicle database is already created
- You have access to MS SQL Server Management Studio (or another SQL browser tool)
How to configure TDE
This is the procedure summary to apply TDE encryption to the Imagicle database:
- Create Database Master Key
- Create Certificate
- Backup Certificate (not mandatory, but strongly suggested)
- Create Database Encryption Key
- Enable Encryption
Create Database Master Key (DMK)
The first task implies Database Master Key (DMK) creation, which is a symmetric key to protect other keys. Thanks to this key, you can encrypt all the encryptable objects within the database, such as Symmetric Keys, Asymmetric Keys and Certificates.
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<ChooseAVerySecurePassword>';
Now you should create the certificate which is protected by previously generated Database Master Key. This certificate protects the the Database Encryption Key (DEK) created in the next steps.
USE master; CREATE CERTIFICATE MyImagicleDBCertificate WITH SUBJECT= 'Imagicle DEK Certificate';
This task, even though not mandatory, is very important, because if the certificate gets lost, all the encrypted data are lost and there will be no way to recover them.
BACKUP CERTIFICATE MyImagicleDBCertificate TO FILE = 'C:\CertBackup\ImagicleDEKCertificate.cer' WITH PRIVATE KEY ( FILE = 'C:\CertBackup\ImagicleCertPrivateKey.key', ENCRYPTION BY PASSWORD = '<ChooseAVerySecurePassword>' );
Create Database Encryption Key
Now you need to create the main key, which is the Database Encryption Key (DEK). This key is protected by the certificate generated at the steps above. The DEK is stored in the database itself and you can have only one DEK in each database. You can adopt different encryption algorithms: in below query sample, AES_256 is used and Imagicle database is named "IAS", which is the default name. If you are using another name, please remember to amend the query accordingly.
USE IAS; GO CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE MyImagicleDBCertificate;
As last task, you should turn the encryption on for the Imagicle database. The following query starts a thread to encrypt the entire database using the Database Encryption Key. In below query sample, the Imagicle database is named "IAS" (default name). If you are using another name, please remember to amend the query accordingly.
ALTER DATABASE IAS SET ENCRYPTION ON;
The encryption task is scheduled on background threads by SQL Server.
How to monitor encryption task
Starting from SQL Server 2019, it is possible to monitor the encryption process and suspend/resume it as well.
To check the encryption status, please run this query (assuming Imagicle database is named "IAS"):
SELECT db_name(database_id) as db_name, percent_complete, * FROM sys.dm_database_encryption_keys WHERE db_name(database_id)='IAS'
Click here for more information about the meaning of the columns.
The following query suspends the encryption (Imagicle database name is "IAS"):
ALTER DATABASE IAS SET ENCRYPTION SUSPEND;
The following query resumes the encryption (Imagicle database name is "IAS"):
ALTER DATABASE IAS SET ENCRYPTION RESUME;
This article was:
Thank you for your feedback!
|Database extended proprieties cause the backup tool to fail on...||Error at the updating database phase during the Imagicle UC...|