SQL 2014 Backup Encryption

  • 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

  • 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.

  • 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

  • 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?

  • 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.

  • 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

  • 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.

  • 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

  • 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!

  • 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.

  • 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://www.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