1

The MSDN article on SQL Server TDE states "Create a database encryption key and protect it by the certificate". What does protect mean? The database encryption key is not created from the certificate?

2

"Create a database encryption key and protect it by the certificate". What does protect mean?

This is from the encryption hierarchy and protection. When using TDE you need to create at least two keys:

  1. Server Certificate to protect the DEK
  2. Database Encryption Key (DEK)

The way this works is as follows:

  1. The Service Master Key (SMK) protects the database master key (DMK). When we say "protects" we mean encrypts. This could be said as, "The SMK encrypts the DMK."
  2. The DMK in the master database "protects" (Encrypts) the Server Certificate.
  3. The Server Certificate "protects" (Encrypts) the Database Encryption Key (DEK).

This means, in order to OPEN the DEK to decrypt the database data you need to open the Server Certificate. To open the Server Certificate you'll need to open the Database Master Key (DMK). To open the DMK you'll need to open the SMK.

This is normally done automatically for you as part of automatic key decryption if all levels of the hierarchy are setup properly.

Thus, if we attempt to restore the database using TDE to another instance and that instance does not have the server certificate then it will not be able to read the database and cannot open the database. This is why the Server Certificate is required and we can open it properly.

Encryption Hierarchy: https://msdn.microsoft.com/en-us/library/ms189586.aspx

2

"Create a database encryption key and protect it by the certificate". What does protect mean?

It means that TDE uses the Database Encryption Key (DEK) to encrypt/decrypt the database BUT the Database Encryption Key REQUIRES the existence of the server certificate in order to do this. Thus, the Database Encryption Key is 'protected' by its requirement that the server certificate exist.

This 'requirement' is quite obvious if you back up a TDE database and try to restore or attach it to another Sql Server instance that does not have that server certificate. You'll get an error similar to this:

Missing Server Certificate

My answer to a related question here provides some additional information about TDE requirements.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.