Sql-server – How does Transparent Data Encryption actually work

encryptionsql server

I've been trying to understand how TDE works with databases (particularly SQL Server). I've read numerous MSDN articles, blogs, info websites etc, but still cant get my head around all of it.

This is my understanding of TDE so far (with my assumptions & some questions):

  • A system key is created on installation of SQL Server -> but where is this stored??
  • The user creates a master key -> Now… Is this encrypted using symmetric key encryption and stored in the master db? If this is correct, then I assume that KEY = System key and TEXT = master key?
  • The master key is used to create a certificate -> this is then also stored in the master DB?
  • A per database encryption key is then generated (which is key for encrypting the DB data with symmetric encryption)
  • The certificate then encrypts the database encryption key, which is then stored in the database it was created for.

Now, for the last step then, would the process be this:

Encrypt(Certificate,Database Key) = Encrypted Database Key, then store EDK into database, then Encrypt(Database key, Database)?

Are my assumptions correct?
And then how does the data get decrypted? Because surly you cant retrieve the database key? Also, if someone gets hold of the certificate, doesn't that compromise the database?

I appreciate any assistance with this.

Best Answer

OK, In case anyone wants to know, I got to the bottom of this.

The chain is pretty easy to work out:

Create Service Master Key (automatically done upon install)

Create Database Master Key (user creates their own key)

SymetricEncrypt(Service Key,Database Master Key) -> Store Encrypted Master Key into Master DB

Create Certificate

SymetricEncrypt(Database Master Key, Certificate Private Key) -> Store Encrypted private key in Master DB

Create Database Encryption Key (system creates a random key, but must be manually added to database)

ASymetricEncrypt(Certificate, Database Encryption Key) -> Store Encrypted DEK into database.

So, to decrypt the database data, you'll need to:

Use the SMK to decrypt the DMK

Use the DMK to decrypt the cert private key

Use the certificate to decrypt the DEK

Use the DEK to decrypt the database

The starting point is the Service Master Key... how is this stored (if you encrypt it, you can't retrieve data, and if left in plaintext your system is exposed).

The answer is, the SMK is encrypted with Data Protection API, which is a service built into windows.

Basically, data is encrypted with "secrets" from your user profile. So the same user profile must be used to decrypt the SMK.

Now that I know this, I can use DPAPI in my applications to encrypt my encryption keys :)

Related Question