Sql Server Backup Encryption Certificate

  • Greetings,
    Brace for newbie question. The business is clamoring for database encryption of backups before storing them off site in an encrypted S3 bucket, while still having our daily onsite restore for reporting reasons. Backups are currently done via the Ola scripts and automated restores are handled via a powershell script run as a sql job.  

    Our onsite restore includes the backups from 24 separate availability groups, onto one server. 

    My question is: Is it possible to create a database master key and certificate on another system, like my local host and have each member of our prod layout and the restore target use the same certificate?  Are there any cross-compatibility issues I would need to worry about i.e 2016 certs wont work on 2014, or certificates created on one domain wont work on another domain? Are there any other gotchas that you have run into that I should be aware of?

    Thanks all

  • I am not aware of cross-compatibility issues but you may want to try that , but I doubt there will be any cross-compatibility issues with SQL 2016,  and Yes , It is defiantly possible to implement one certificate on multipal SQL servers so that you can perform cross-backup/restore , In fact I have implemented one certificate on all of my SQL 2014 instances in different environments.

  • tdroche - Monday, December 18, 2017 11:22 AM

    Greetings,
    Brace for newbie question. The business is clamoring for database encryption of backups before storing them off site in an encrypted S3 bucket, while still having our daily onsite restore for reporting reasons. Backups are currently done via the Ola scripts and automated restores are handled via a powershell script run as a sql job.  

    Our onsite restore includes the backups from 24 separate availability groups, onto one server. 

    My question is: Is it possible to create a database master key and certificate on another system, like my local host and have each member of our prod layout and the restore target use the same certificate?  Are there any cross-compatibility issues I would need to worry about i.e 2016 certs wont work on 2014, or certificates created on one domain wont work on another domain? Are there any other gotchas that you have run into that I should be aware of?

    Thanks all

    If using encrypted backups ( available in sql 2014 onwards ) you would need to restore the certificate from the source server to any servers where you wish to restore copies of the databases.
    On the target instances before restoring the certificate, you would need to create a database master key in the master database if one does not exist already.
    See my article at the following link

    http://www.sqlservercentral.com/articles/Encryption/109028/

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

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

Viewing 3 posts - 1 through 2 (of 2 total)

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