Database Encryption Key (DEK) management

re: Database Encryption Key (DEK) management

shradg — Tue, 18 Oct 2011 22:57:19 GMT

Very nice representation of the dependencies of the key in TDE.

re: Database Encryption Key (DEK) management

Jerrod — Tue, 25 Jan 2011 21:31:22 GMT

So, just to clarify. When you run the ALTER DATABASE ENCRYPTION KEY and replace the DEK with a new key, what happens to the backups that were taken previous to the key rotation. Do the old DEK and new DEK stay in the database so they can both be used to decrypt backups?

Additionally, what if you also want to replace your Certificate that is used to encrypt the DEK? My assumption is that I would create the new certificate, in the master DB, then use the new certificate to encrypt the new DEK that is set up with the Alter DB Encryption key command. Please correct me if I am thinking of this in the wrong way. Also, my assumption is that you would still need to leave the old and new certificates in the sysdb so they would be available to decrypt the old/new DEK. Is this correct?

Thanks,

Jerrod

re: Database Encryption Key (DEK) management

Raul Garcia - MS — Wed, 19 Jan 2011 00:37:01 GMT

The information in sys.dm_database_encryption_keys includes the thumbprint for the object protecting the DEK, unfortunately we missed to add an encryptor_type column that would explicitly define the nature of this protection.

The following query should be a good starting point. It is not a 100% solution since there is a very slim possibility (close to 0, but it exists) of thumbprint collision between a certificate & the EKM-based asymmetric key (the HSM would define this thumbprint).

SELECT deks.*, certs.name as encryptor_name, 'certificate' as encryptor_type FROM sys.dm_database_encryption_keys deks, sys.certificates certs WHERE deks.encryptor_thumbprint = certs.thumbprint

UNION

SELECT deks.*, asmk.name as encryptor_name, 'asymmetric key' as encryptor_type FROM sys.dm_database_encryption_keys deks, sys.asymmetric_keys asmk WHERE deks.encryptor_thumbprint = asmk.thumbprint

I hope this information helps,

-Raul Garcia

SDE/T

SQL Server Engine

re: Database Encryption Key (DEK) management

Dora_TDE — Thu, 13 Jan 2011 16:32:21 GMT

Hi,

I want to know which query can i execute to see informations about the DEK in SQL Server ?

like, when i want to see an asymmetric key i put :

select * from sys.asymmetric_keys

which one for the DEK ????

re: Database Encryption Key (DEK) management

Mei — Tue, 02 Nov 2010 19:57:14 GMT

Good information about the encryption scan.

re: Database Encryption Key (DEK) management

Zubair Ahmed Mughal - MSFT — Tue, 05 Oct 2010 19:27:02 GMT

Hi Paul,

The short answer is No. They would need the encryptor (Certificate or the Asymmetric key) to decrypt the database.

TDE encrypts the database using DEK which is encrypted by a certificate. This certificate is stored in master database and is required to decrypt the database. If someone copies your database and log files they will need the certificate (with private key) or the asymmetric key to restore it on their server. Note that the database files are encrypted so they won' be able to see you data if they even try to directly open the file.

re: Database Encryption Key (DEK) management

Paul — Thu, 05 Aug 2010 13:21:17 GMT

Hi Zubair,

I read your article but I don't understand most of it simply because I'm very very new to SQL Server. My previous db experience was only with MS Access.

Say if I use TDE on my database and the database files (mdb, log, etc..) are copied by some other people. Will they be able to see the data if they attach the database files to their SQL server?

Thanks.

re: Database Encryption Key (DEK) management

Zubair Ahmed Mughal - MSFT — Mon, 14 Jun 2010 23:39:01 GMT

The bootpage image got resized during publishing. To see the full sized image either copy the image and paste it in MS Paint, MS Word, etc. OR open this link:

blogs.msdn.com/.../8420.Bootpage.png