Hello

I have a question regarding Transparent Data Encryption. I have enabled TDE on a database using the below steps:

1. Create a master key encryption by a password.

2. Create a certificate in the the user database named 'TDE_Test' protected by the master key.

3. Create database encryption key 'TDE_Test' using the certificate created in Step 2.

4. Enable encryption by using the command 'ALTER DATABASE  TDE_Test SET ENCRYPTION ON' 

I understand that if I need to copy this TDE encrypted database to a different SQL Instance, I have to copy the certificate from the source Instance to the destination Instance.

Now my question is, do the Service Master Key and Database Master Key come into the picture here anywhere?

Are these related to TDE in any way?

Do I have to take regular backups of the Service Master Key & Database Master Key as part of regular maintenance for the SQL Instance that has a TDE encrypted database?

Thanks

Satya

• Edited by Satya Krishna Saturday, September 12, 2015 12:02 AM

I understand that if I need to copy this TDE encrypted database to a different SQL Instance, I have to copy the certificate from the source Instance to the destination Instance.

Yes this is correct, you would typically copy and create the certificate on the different SQL Instance, if it didn't exist. The Service Master Key and Database Master Key do not need to be transferred.

Now my question is, do the Service Master Key and Database Master Key come into the picture here anywhere?

TDE makes use of these Keys, however backups of these keys are generally to enable quick recovery from local issues such as corruption and deletion, much like standard database backups.

As per https://msdn.microsoft.com/en-us/library/aa337546.aspx

'The database master key is used to encrypt other keys and certificates inside a database. If it is deleted or corrupted, SQL Server may be unable to decrypt those keys, and the data encrypted using them will be effectively lost. For this reason, you should back up the database master key and store the backup in a secure off-site location.'

and https://msdn.microsoft.com/en-nz/library/ms190337.aspx

'The service master key should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server.'