SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Sql Server Backup Encryption Certificate


Sql Server Backup Encryption Certificate

Author
Message
TJ Roche
TJ Roche
SSC-Enthusiastic
SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)SSC-Enthusiastic (176 reputation)

Group: General Forum Members
Points: 176 Visits: 105
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
goher2000
goher2000
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4426 Visits: 1829
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.



Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)SSC Guru (217K reputation)

Group: General Forum Members
Points: 217012 Visits: 18594
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" ;-)
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum









































































































































































SQLServerCentral


Search