April 12, 2016 at 7:44 am
I have been investigating about TDE since a couple of days and have come across these following questions. Please help me seek clarity on these. Thanks.
1. Do I have to backup the Service Master Key? If yes, why?
2. Do I have to backup the Database Master Key? If yes, why?
3. Where the Database Master Key should be created - at the "user" database level or at "master" database level? What are the differences between both approaches and which is advisable?
4. While restoring backups of encrypted databases on a new instance we can have following 2 situations:
a. Restore the database master key and certificate from their backup files and then restore the database
b. Create the database master key on the new instance, restore the certificate from the backup file and then restore the database.
What is the preferred approach?
April 12, 2016 at 8:30 am
gaurav_ghiya (4/12/2016)
1. Do I have to backup the Service Master Key? If yes, why?
Yes, It's unique to the instance and used to encrypt objects within the instance
gaurav_ghiya (4/12/2016)
2. Do I have to backup the Database Master Key? If yes, why?
Yes, it's used to protect database level objects
gaurav_ghiya (4/12/2016)
3. Where the Database Master Key should be created - at the "user" database level or at "master" database level? What are the differences between both approaches and which is advisable?
For TDE the DMK should be created in the master database as it's used server wide. The certificate you use for TDE is also stored in the master database, its private key is protected by the DMK whilst it resides on the instance.
gaurav_ghiya (4/12/2016)
4. While restoring backups of encrypted databases on a new instance we can have following 2 situations:a. Restore the database master key and certificate from their backup files and then restore the database
No, just need to backup the certificate from the source and then restore to the new instance, then restore the database
gaurav_ghiya (4/12/2016)
b. Create the database master key on the new instance, restore the certificate from the backup file and then restore the database.
Yes
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 13, 2016 at 12:35 am
Thanks Perry for all your valuable inputs. They were all very helpful. However, I am still not completely clear on question #1 and #2. If we should backup the Service Master Key and Database Master Key, could you please elaborate the situations/events in which these backups would be ever required?
April 13, 2016 at 12:49 am
1. Service master key is the instance level and master key can be database level. I do not see the need to backup the master key as you can't delete the master key as long as it is encrypting a certificate.
2. I think it is a better practice to store the master key in the master database because only the sysadmins have access to the master database.
3. While restoring a TDE enabled database to a different instance, you can create a different master key. However, you need to backup the certificate and its private key then import the certificate(by the way you can rename the certificate) using the private key to the destination before restoring the database.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply