May 20, 2020 at 12:00 am
Comments posted to this topic are about the item Restore a Backup of a TDE Database to Another Server: Level 2 of the Stairway to TDE
Vishnu Gupthan
PowershellAcademy
www.powershellacademy.com
June 1, 2020 at 10:37 am
Good one Vishnu. Made it look so simple and easy.
Ramakant Dadhichi
Technology Enthusiast
August 5, 2021 at 8:07 am
just one note to add here, if you do restore the DMK from another server, and i recommend you don't, you will need to take extra steps in order for the restores to occur seamlessly.
When restoring the master database DMK across servers, the key is not silently protected by the SMK on the new sql server instance.
If you restore the DMK from one instance to another and then restore your certificate everything will seem just fine. It's not until you attempt to restore the database that you'll get a failure, as the DMK cannot be silently opened, allowing access to the server certificate.
To resolve this you will need to perform the following:
--Open the DMK
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'blahblahblah'
--now set encryption by the SMK
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY
As already stated though, you should ensure your DMKs are unique across instances.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2025 at 8:05 pm
Hi Perry and others,
So it sounds we just create DMK on each server instead of using restore backup of DMK from another server? since you mentioned ensure the DMK are unique across instances, I read some people asked to backup DMK in a file, not sure what it is used for?
January 16, 2025 at 8:16 pm
Hi Peter and others,
So it sounds we just create DMK on each server instead of using restore backup of DMK from another server? since you mentioned ensure the DMK are unique across instances, I read some people asked to backup DMK in a file, not sure what it is used for?
correct, where you’re deploying tde or using encrypted backup just create a new DMK on each instance if you haven’t already, these are and should be unique between instances, it’s a bad idea and not best practice to start restoring dmk from one instance across others.
TDE and encrypted backup have no direct dependency on the master DMK so why would you want to start restoring it across servers anyway??
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2025 at 8:39 pm
Thank you Perry, sorry for the typo, corrected.
This answers my question clearly, I see some articles asked to backup DMK and save it to a safe place, not sure why we need to do this if we does not need it to recover to another server. Thanks
January 16, 2025 at 10:21 pm
Thank you Perry, sorry for the typo, corrected. This answers my question clearly, I see some articles asked to backup DMK and save it to a safe place, not sure why we need to do this if we does not need it to recover to another server. Thanks
yes back up the DMK and store it, there aren’t many cases to restore it but always keep backups and details of the pwd used to create the DMK in the first place
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
January 16, 2025 at 11:33 pm
Perry, for above statement: yes back up the DMK and store it, there aren’t many cases to restore it but always keep backups and details of the pwd used to create the DMK in the first place.
I understand we don't need to restore the DMK to another server.
Now if we just talk about one server, if somehow the DMK got corrupted on the server, not sure what could cause that happen, we may restore the DMK from the backup. But in this case, could we also just drop the old DMK, and recreate a new DMK with new password. If we do this, do we need to drop all the certificate and DEK on each databases and recreated them? I guess, we don't need, since you said they don't have direct dependency?
Thanks,
January 17, 2025 at 7:05 am
Now if we just talk about one server, if somehow the DMK got corrupted on the server, not sure what could cause that happen,
im not sure either, you’re really overthinking this tbh. Configure it, back up the config and leave it running.
there might be situations where you don’t want the DMK auto protected by the SMK and want to manually control who opens it, in this case you’d need the password for the DMK, but we’re talking edge cases here and not even related to TDE
we may restore the DMK from the backup. But in this case, could we also just drop the old DMK, and recreate a new DMK with new password. If we do this, do we need to drop all the certificate and DEK on each databases and recreated them? I guess, we don't need, since you said they don't have direct dependency?
Thanks,
do you know what never tried dropping DMK with certs in the database, this would leave the private keys unprotected, can’t see why you’d want to.
configure it, back it up, leave it
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply