September 29, 2016 at 12:29 pm
I've read several articles and examples about backup encryption. My question is when I have multiple databases (but not all) on the same SQL Server than need to have encrypted backups do I create multiple master keys and certificates or just one master key and certificate for all?
Thanks,
Terrie
September 29, 2016 at 12:47 pm
I would expect that would depend on the needs and requirements of your environment.
A secure type environment might require you to use a separate certificate for each database (even if all the databases on the server belonged to the same group / org / division / application,) while a less secure environment might be OK with all the databases being backed up using the same certificate for all.
Your best option would be to discuss which route to go, or if you even need to go down this road, with your boss and likely a company lawyer (hey! Lawyers *DO* have uses beyond boat anchors! :hehe: ) But start with your boss.
September 29, 2016 at 1:38 pm
jasona.work (9/29/2016)
I would expect that would depend on the needs and requirements of your environment.A secure type environment might require you to use a separate certificate for each database (even if all the databases on the server belonged to the same group / org / division / application,) while a less secure environment might be OK with all the databases being backed up using the same certificate for all.
Your best option would be to discuss which route to go, or if you even need to go down this road, with your boss and likely a company lawyer (hey! Lawyers *DO* have uses beyond boat anchors! :hehe: ) But start with your boss.
+1
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 29, 2016 at 1:40 pm
Thanks for replying. So I can use a different certificate for each database I want to encrypt. What about the master key. Is that the same for all or can there be one for each?
September 29, 2016 at 2:11 pm
Terrie (9/29/2016)
Thanks for replying. So I can use a different certificate for each database I want to encrypt. What about the master key. Is that the same for all or can there be one for each?
I believe that you can only have one master key per-server, so it would be one key for all the certificates.
But, from working with TDE, keep in mind that the master key is only to protect the certificate on that instance. If you backup the certificate (a MUST) and want to restore it to another server, you would create a NEW master key on the new server, so that when you import the certificate, it gets protected by the new servers master key.
September 29, 2016 at 2:30 pm
So if I want to restore an encrypted backup from one server to another and I already have a master key on the target server for whatever reason. Do I just restore the certificate that I (previously) backed up (and safely stored) for my encrypted backup? Thanks for the replies.
Terrie
September 29, 2016 at 3:35 pm
There is one master key per database. Poorly named, and has nothing to do with master database.
The master key protects the certificate(s) you use for the backup encryption. You need to backup this certificate(s) and protect that (password, ACL, disk encryption, etc).
During a restore, the certificate is opened and used to decrypt the keys protecting the backup. If the certificate is not available, you can't decrypt the backup.
If you need to restore on the same server, the certificate is there. If you are on a new server, then you restore the certificate on the new server (you'll need a master key there, not the same one, but a master key in the master db) and then restore the db.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
September 30, 2016 at 8:21 am
So I set up a test and in simple terms this is what I did and it worked! The master key and certificates are stored in the master database. Thanks for all your help! Terrie
Create master key on Server1
Backup master key on Server1
Create Certificate1 for DB1
Backup Certificate1 for DB1
Create Certificate2 for DB2
Backup Certificate2 for DB2
Backup DB1 with Certificate1
Backup DB2 with Certificate2
Copy .bak, .cer and .pvk files for DB1 and DB2 to Server2
Create master key on Server2
Backup master key on Server2
Open master key
Create Certificate1 from copied .cer, .pvk
Create Certificate2 from copied .cer, .pvk
Restore DB1
Restore DB2
Close master key
September 30, 2016 at 9:08 am
Congrats!
I was going to set up a test tonight on this, because from the way Steve was talking (and I was misunderstanding) it sounded like the cert would be in the DB you were trying to backup, which didn't make sense.
Looks like I did have it right.
Again, glad it worked for you and glad to help!
September 30, 2016 at 10:32 am
Glad it worked and nice test.
Apologies if I didn't explain well.
The cert protects a key that is actually used to encrypt the backup. This key is stored in the backup, separate from the cert, for security reasons.
Certs used for TDE and backup encryption need to be in master, protected with a Database Master Key in master. This DMK has nothing to do with a Database Master Key you may have in your individual databases.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 7, 2016 at 5:36 am
Terrie (9/29/2016)
I've read several articles and examples about backup encryption. My question is when I have multiple databases (but not all) on the same SQL Server than need to have encrypted backups do I create multiple master keys and certificates or just one master key and certificate for all?Thanks,
Terrie
The DMK on each instance should be unique, this is not an integral part of the backup encryption and the certificate. The DMK on the instance merely encrypts the certs private key whilst the cert resides on the instance.
Terrie (9/29/2016)
So I can use a different certificate for each database I want to encrypt. What about the master key. Is that the same for all or can there be one for each?
Different cert for each db might be overkill, different cert for each instance would be my preference.
Just ensure that each instance has a unique DMK created, this is not used when restoring an encrypted backup to another server
Terrie (9/29/2016)
So if I want to restore an encrypted backup from one server to another and I already have a master key on the target server for whatever reason. Do I just restore the certificate that I (previously) backed up (and safely stored) for my encrypted backup? Thanks for the replies.Terrie
DMK would already exist, you need a backup of the cert and the database backup itself.
See my article at this link
http://qa.sqlservercentral.com/articles/Encryption/109028/[/url]
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply