What is TDE and how does it relate to GDPR (example in SQL Server)
Certifications:
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Linux - 1 July 2024
- How do we automate the process of checking the integrity of databases in SQL Server without using a maintenance plan - 10 May 2024
- How can we connect Oracle Database to SQL Server using Oracle Gateway on Windows - 5 April 2024
Since 2018 when its European legislation began GDPR (data protection) in Europe all companies started looking for solutions on how to implement the data protection policy immediately with as little impact as possible.
That's where its technology comes in TDE (Transparent Data Encryption) currently supported RDBMS.
But what is TDE in practice?
TDE applies encryption at the level of the physical files ie on the hard disk. The data files, log files and backups when activated apply the encryption policy.
That protects us
If a malicious user has his hands on the hard drives of the database or the data files or any of the backups, he will not be able to intercept data if he does not have the Masters Key and its backup certificate together with the private key and its password.
Of course, its activation comes with some performance impact...
It should also be mentioned that TDE does not protect data at the network level nor does it appear encrypted in a query at the base as is the case with data masking, cell level encryption and always encrypt. So it cannot be considered as a one-size-fits-all solution…!
How to enable TDE on a database in SQL Server
First we need to build one master key encryption by setting a code:
USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd' GO
Commands completed successfully.We will see that at the dmv a key appears to have been made with the name MS_ServiceMasterKey:
select * from sys.symmetric_keys
Then we create our certificate with a name:
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate', EXPIRY_DATE='20990909';
Commands completed successfully.We see in the dmv of the certificates that it was made with the name we gave it:
select * from sys.certificates
An important procedure before proceeding is to backup the certificate by defining a name for the file that will be created as a private key and its code:
*The script we run to restore it is in the comment.
BACKUP CERTIFICATE tdeCert TO FILE = 'C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' , ENCRYPTION BY PASSWORD = 'passw0rd' ); --create certificate tdeCert from file ='C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' , DECRYPTΙΟΝ BY PASSWORD = 'passw0rd' );
Then we choose the database we want to encrypt and create it encryption key with the following algorithm according to the certificate we made before:
use translation go CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256 ENCRYPTION BY SERVER CERTIFICATE tdeCert;
With the following command it is activated in the database:
ALTER DATABASE translation SET ENCRYPTION ON;
Commands completed successfully.With the following query, we see what state the encryption is in. Immediately after activating it we will see that it will be on status 2, which means that the encryption process has started and will end in status 3 where is the Encrypted:
SELECT [db].name, [db].is_encrypted, [k].encryption_state, [k].percent_complete, CASE [k].encryption_state WHEN 0 THEN 'Not Encrypted' WHEN 1 THEN 'Unencrypted' WHEN 2 THEN 'Encryption in progress' WHEN 3 THEN 'Encrypted' WHEN 4 THEN 'Key change in progress' WHEN 5 THEN 'Decryption in progress' WHEN 6 THEN 'Protection change in progress ' ELSE 'Not Encrypted' END AS 'Desc' FROM sys.dm_database_encryption_keys [k] RIGHT JOIN sys.databases [db] ON [k].database_id = [db].database_id
/* EncryptState
0
No database encryption key present, no encryption
1
Unencrypted
2
Encryption in progress
3
Encrypted
4
Key change in progress
5
Decryption in progress
6
The certificate or asymmetric key encrypting the DEK is being changed
*/How we remove TDE in SQL Server
First we disable it in the database:
USE MASTER GO ALTER DATABASE translation SET ENCRYPTION OFF GO
Commands completed successfully.We run the query on the dmv until we see that it has become status 1, i.e. that it has been done decrypt the database:
select * from sys.dm_database_encryption_keys where 1=1
and database_id in (db_id('translation'))
Delete the encryption key:
USE translation GO DROP DATABASE ENCRYPTION KEY GO
Commands completed successfully.We check that it no longer exists:
select * from sys.dm_database_encryption_keys where 1=1
and database_id in (db_id('translation'))
(0 rows affected)To remove the certificate and master key from the entire instance
We also delete the certificate:
use master go drop certificate tdeCert;
And finally we delete the master key encryption:
drop master key
Great attention!!!
In the event that we activate TDE in a database, if in some disaster we have lost the certificate with its private key, we must know that there will be no going back...
How to restore a database with TDE to another SQL Server Instance
First we transfer to the target instance, the backup of the certificate (C:\backup\tde.cer), the private key (C:\backup\tde.pvk) and the database backup (C:\backup\translation_full.bak).
We create a new one master key encryption(only if it doesn't already exist):
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'passw0rd' GO
Then we restore it certificate with the backup file, the private key and its password:
create certificate tdeCert from file ='C:\backup\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\backup\tde.pvk' , DECRYPTΙΟΝ BY PASSWORD = 'passw0rd' );
And now we can restore the database:
RESTORE DATABASE [translation] FROM DISK = N'C:\backup\translation_full.bak' WITH STATS = 10; GO
