{"id":1152,"date":"2020-09-25T07:00:00","date_gmt":"2020-09-25T04:00:00","guid":{"rendered":"https:\/\/www.dataplatform.gr\/?p=1152"},"modified":"2025-04-30T13:30:25","modified_gmt":"2025-04-30T10:30:25","slug":"ti-einai-to-tde-kai-ti-schesi-echei-me-to-gdpr-par","status":"publish","type":"post","link":"https:\/\/www.dataplatform.gr\/en\/ti-einai-to-tde-kai-ti-schesi-echei-me-to-gdpr-par\/","title":{"rendered":"What is TDE and how does it relate to GDPR (example in SQL Server)"},"content":{"rendered":"

Since 2018 when its European legislation started GDPR<\/strong> (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.<\/p>\n\n\n\n

That's where its technology comes in TDE<\/strong> (Transparent Data Encryption<\/strong>) currently supported RDBMS<\/strong>.<\/p>\n\n\n\n

But what is TDE in practice?<\/h2>\n\n\n\n

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. <\/p>\n\n\n\n

That protects us<\/h2>\n\n\n\n

If a malicious user has his hands on the hard drives of the base or the data files or any of the backups, he will not be able to intercept data if he does not have the Masters<\/strong> <\/strong>Key<\/strong> and its backup certificate<\/strong> together with the private key and its password.<\/p>\n\n\n\n

Of course, its activation comes with some performance impact...<\/h4>\n\n\n\n

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<\/strong>, cell level encryption<\/strong> and always encrypt<\/strong>. So it cannot be considered as a one-size-fits-all solution\u2026!<\/p>\n\n\n\n

<\/p>\n\n\n\n

How to enable TDE on a database in SQL Server<\/h2>\n\n\n\n

First we need to build one master key encryption <\/em><\/strong>by setting a code:<\/p>\n\n\n\n

USE master;\nGO\n\nCREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'passw0rd'\nGO<\/pre>\n\n\n\n
Commands completed successfully.<\/code><\/pre>\n\n\n\n
We will see that at the dmv a key appears to have been made with the name MS_ServiceMasterKey<\/em><\/strong>:<\/p>\n\n\n\n
select * from sys.symmetric_keys<\/pre>\n\n\n\n
\"\"
01<\/figcaption><\/figure>\n\n\n\n
Then we create our certificate with a name:<\/p>\n\n\n\n
CREATE CERTIFICATE tdeCert WITH SUBJECT = 'TDE Certificate', EXPIRY_DATE='20990909';<\/pre>\n\n\n\n
Commands completed successfully.<\/code><\/pre>\n\n\n\n
We see in the dmv of the certificates that it was made with the name we gave it:<\/p>\n\n\n\n
select * from sys.certificates<\/pre>\n\n\n\n
\"\"
02<\/figcaption><\/figure>\n\n\n\n
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:<\/p>\n\n\n\n
*The script we run to restore it is in the comment.<\/em><\/p>\n\n\n\n
BACKUP CERTIFICATE tdeCert TO FILE = 'C:\\backup\\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\\backup\\tde.pvk' ,  ENCRYPTION BY PASSWORD =  'passw0rd' ); \n\n--create certificate tdeCert from file ='C:\\backup\\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\\backup\\tde.pvk' ,  DECRYPT\u0399\u039f\u039d BY PASSWORD = 'passw0rd' ); <\/pre>\n\n\n\n
Then we choose the database we want to encrypt and create it encryption key<\/strong><\/em> with the following algorithm according to the certificate we made before:<\/p>\n\n\n\n
use translation\ngo\n\nCREATE DATABASE ENCRYPTION KEY\nWITH ALGORITHM = AES_256\nENCRYPTION BY SERVER CERTIFICATE tdeCert;<\/pre>\n\n\n\n
With the following command it is activated in the database:<\/p>\n\n\n\n
ALTER DATABASE translation\nSET ENCRYPTION ON;<\/pre>\n\n\n\n
Commands completed successfully.<\/code><\/pre>\n\n\n\n
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<\/strong>, which means that the encryption process has started and will end in status 3<\/strong> where is the Encrypted<\/strong><\/em>:<\/p>\n\n\n\n
SELECT\n[db].name,\n[db].is_encrypted,\n[k].encryption_state,\n[k].percent_complete,\nCASE [k].encryption_state\nWHEN 0 THEN 'Not Encrypted'\nWHEN 1 THEN 'Unencrypted'\nWHEN 2 THEN 'Encryption in progress'\nWHEN 3 THEN 'Encrypted'\nWHEN 4 THEN 'Key change in progress'\nWHEN 5 THEN 'Decryption in progress'\nWHEN 6 THEN 'Protection change in progress '\nELSE 'Not Encrypted'\nEND AS 'Desc'\nFROM\nsys.dm_database_encryption_keys [k]\nRIGHT JOIN\nsys.databases [db] ON [k].database_id = [db].database_id<\/pre>\n\n\n\n
\"\"
03<\/figcaption><\/figure>\n\n\n\n
\/* EncryptState\n0\nNo database encryption key present, no encryption\n1\nUnencrypted\n2\nEncryption in progress\n3\nEncrypted\n4\nKey change in progress\n5\nDecryption in progress\n6\nThe certificate or asymmetric key encrypting the DEK is being changed\n*\/<\/code><\/pre>\n\n\n\n
<\/p>\n\n\n\n
How we remove TDE in SQL Server<\/h2>\n\n\n\n
First we disable it in the database:<\/p>\n\n\n\n
USE MASTER\nGO\n\nALTER DATABASE translation SET ENCRYPTION OFF\nGO<\/pre>\n\n\n\n
Commands completed successfully.<\/code><\/pre>\n\n\n\n
We run the query on the dmv until we see that it has become status 1, i.e. that it has been done decrypt <\/strong><\/em>the database:<\/p>\n\n\n\n
select * from sys.dm_database_encryption_keys where 1=1\nand database_id in (db_id('translation'))<\/pre>\n\n\n\n
\"\"
04<\/figcaption><\/figure>\n\n\n\n
Delete the encryption key:<\/p>\n\n\n\n
USE translation\nGO\n\nDROP DATABASE ENCRYPTION KEY\nGO<\/pre>\n\n\n\n
Commands completed successfully.<\/code><\/pre>\n\n\n\n
We check that it no longer exists:<\/p>\n\n\n\n
select * from sys.dm_database_encryption_keys where 1=1\nand database_id in (db_id('translation'))<\/pre>\n\n\n\n
 (0 rows affected)<\/code><\/pre>\n\n\n\n
To remove the certificate and master key from the entire instance<\/h4>\n\n\n\n
We also delete the certificate:<\/p>\n\n\n\n
use master go\n\ndrop certificate tdeCert;<\/pre>\n\n\n\n
And finally we delete the master key encryption: <\/p>\n\n\n\n
drop master key<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Great attention!!!<\/h2>\n\n\n\n
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...<\/p>\n\n\n\n
<\/p>\n\n\n\n
How to restore a database with TDE to another SQL Server Instance<\/h2>\n\n\n\n
First we transfer to the target instance, the backup of the certificate (C:\\backup\\tde.cer<\/em>), the private key (C:\\backup\\tde.pvk<\/em>) and the database backup (C:\\backup\\translation_full.bak<\/em>).<\/p>\n\n\n\n
We create a new one master key encryption<\/strong><\/em>(only if it doesn't already exist):<\/p>\n\n\n\n
CREATE MASTER KEY ENCRYPTION BY PASSWORD  = 'passw0rd'\nGO<\/pre>\n\n\n\n
Then we restore it certificate <\/em><\/strong>with the backup file, the private key and its password:<\/p>\n\n\n\n
create certificate tdeCert from file ='C:\\backup\\tde.cer' WITH PRIVATE KEY ( FILE = 'c:\\backup\\tde.pvk' ,  DECRYPT\u0399\u039f\u039d BY PASSWORD = 'passw0rd' ); <\/pre>\n\n\n\n
And now we can restore the database:<\/p>\n\n\n\n
RESTORE DATABASE [translation] \nFROM DISK = N'C:\\backup\\translation_full.bak' WITH STATS = 10;\nGO<\/pre>\n\n\n\n
<\/p>\n\n\n\n
Sources:<\/h2>\n\n\n\n