Multiple TDE Certificates on Same Instance

Question

Multiple TDE Certificates on Same Instance

alex.sqldba

SSChampion

Points: 10254
More actions
March 29, 2018 at 1:12 am

#403214

Morning Guys,
This , I think should be an easy question but I cant find an answer online! My google-fu must be bad.
I have multiple databases hostes on different servers, each with their own different tde certificate.
I now need to restore these onto the same instance, side by side.
Is it possible to have multiple tde certificates per instance?
If so, do I just create it from a backup as I normally would if it were a fresh install?
Cheers
Alex

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Perry Whittle SSC Guru Points: 234012 More actions · Answer 1

alex.sqldba - Thursday, March 29, 2018 1:12 AM
Morning Guys,
This , I think should be an easy question but I cant find an answer online! My google-fu must be bad.
I have multiple databases hostes on different servers, each with their own different tde certificate.
I now need to restore these onto the same instance, side by side.
Is it possible to have multiple tde certificates per instance?

Yes

alex.sqldba - Thursday, March 29, 2018 1:12 AM
If so, do I just create it from a backup as I normally would if it were a fresh install?
Cheers
Alex

you need to create each cert from a backup file into the master database on the target instance

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 2

As Perry mentioned, you can. The certs are the same as any other cert. Each needs a unique name, but you can have multiple certs in master. When you restore or open the TDE db, it just needs to find the cert with the proper thumbprint.

huishi.ca SSChasing Mays Points: 600 More actions · Answer 3

hi Steve, when restore tde enabled backup to the dest instance, say there are multiple tde certificates on the dest instance. Do I need to specify which certificate to be used to restore the tde enabled backup?

thanks

brambor Valued Member Points: 65 More actions · Answer 4

Yes. I save the certificates in the backup folder for the specific database in a 'cert' folder. When You restore you create the cert from the file and then run the restore

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 5

You don't need to specify the cert. The backup file contains information about the certificate that was used to perform the encryption. This certificate needs to be created on the instance already. Then the restore will look for the cert and perform the decryption.

https://www.sqlservercentral.com/steps/stairway-level-2-restore-a-backup-of-a-tde-database-to-another-server

It doesn't matter what's in the file system. The cert needs to be created in SQL Server.