April 29, 2015 at 8:37 pm
Hi All,
I have a following scenario. I have a Prod Server A having TDE enabled on 2 of those databases. I have a Prod Server B having TDE enabled on 3 of those databases. Now I have to create a single Dev server Server C for all the above 5 databases residing on the two servers. So how can I restore all the 5 database backup files on server C.
Does it mean that I need to copy the certificates and Keys from both the Prod server to this Dev Box and then restore the backup files. Once done, I can enable the encryption ON on those 5 database on Dev box or is there any different approach.
Also how will tempdb behave in this scenario.
Thanks in advance.
Sameer
April 30, 2015 at 5:14 am
Hi Sameer,
Yes, you will have to copy the certificates and keys from both the prod servers to the DEV box.
tempdb will also get encrypted on the DEV. Below are steps for restoring encrypted DBs.
1. Copy certificate and private key backup files to DEV server.
2. Create a DMK on the DEV if not already created.
3. Restore/recreate the TDE certificate by using the respective original server certificate backup file; in your case two. The passwords used while recreating certificate HAS to be the same as the password that were used when the backup was taken.
4. Open the DMK if not opened.
5. Restore database from the DB backup files.
6. Check encryption status.
-- Ninad
April 30, 2015 at 6:47 am
ninad.soman0 (4/30/2015)
Yes, you will have to copy the certificates and keys from both the prod servers to the DEV box.
Incorrect, you do not need the DMK from server a and b, just the backups of the certificates.
Before you restore these on server c you'll need to create a DMK on that server, but it is not dependant on any other servers DMK.
ninad.soman0 (4/30/2015)
Tempdb will also get encrypted on the DEV box.
This is correct
ninad.soman0 (4/30/2015)
The passwords used while recreating certificate HAS to be the same as the password that were used when the backup was taken.
To be specific, the decryption by password key you supply during the restore will be the same as the encryption by password you supplied during the backup of the certificate, this is protecting the private key backup. See below for an example
Backup Cert
BACKUP CERTIFICATE [MyNewCert] TO
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY(
FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky',
ENCRYPTION BY PASSWORD = 'P@sswordt0encryptbackup')
Restore Cert
CREATE CERTIFICATE [MyNewCert] AUTHORIZATION [dbo]
FROM FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.cer'
WITH PRIVATE KEY (
DECRYPTION BY PASSWORD = 'P@sswordt0encryptbackup'
, FILE = 'E:\Bak\MSSQL12.INST1\MSSQL\Backup\MyNewCert.pky'
)
ninad.soman0 (4/30/2015)
4. Open the DMK if not opened.
If you created a DMK and restored the certificate correctly this is not necessary
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
April 30, 2015 at 7:28 am
Perry Whittle (4/30/2015)
ninad.soman0 (4/30/2015)
Yes, you will have to copy the certificates and keys from both the prod servers to the DEV box.Incorrect, you do not need the DMK from server a and b, just the backups of the certificates.
Before you restore these on server c you'll need to create a DMK on that server, but it is not dependant on any other servers DMK.
True, I meant only the cert backup and not DMK, just wasn't specific 🙁
ninad.soman0 (4/30/2015)
4. Open the DMK if not opened.If you created a DMK and restored the certificate correctly this is not necessary
Thank you ! This is going in my notes 🙂
and will take care to post more specifics in replies ..
April 30, 2015 at 10:26 am
Perry is correct. The hierarchy on an instance is
SMK
- encrypts DMK (master)
- encrypts certificate in master (can be multiples here for different dbs)
- each cert encrypts the DEK in a TDE db
- DEK encrypts the DB
To restore to a different server, you need a backup of the certificate that protects the DEK in that db. That should have a password to protect it. On a new server:
- create a DMK in master if needed.
- restore the cert backup, providing the password. This needs to be done for each different cert protecting a db.
- restore the TDE db backup. The cert being in the system will allow decryption of the DEK.
April 30, 2015 at 10:39 am
Steve Jones - SSC Editor (4/30/2015)
- encrypts certificate in master (can be multiples here for different dbs)
It encrypts the certs private key whilst it is stored on the server
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply