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


TDE and Logshipping


TDE and Logshipping

Author
Message
PearlJammer1
PearlJammer1
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7313 Visits: 1645
Hi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks
TheSQLGuru
TheSQLGuru
SSC Guru
SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)SSC Guru (122K reputation)

Group: General Forum Members
Points: 122172 Visits: 8990
PearlJammer1 - Wednesday, February 7, 2018 5:29 AM
Hi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks

I have never come across this particular configuration sequence at any client, but my understanding is that there is no restoring of a TDE protected database without the TDE encryption stuff being in place/restored on the secondary.


Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
jasona.work
jasona.work
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44298 Visits: 16677
PearlJammer1 - Wednesday, February 7, 2018 5:29 AM
Hi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks

Most likely what you need to do is import / restore into SQL Server the certificate you're using for TDE on the primary server. Just copying those files to the secondary server does not allow SQL to use them.

As for removing the files from the primary server, yes, you can do that, as the files on disk are backups of the certificates held in SQL Server itself.

If you run this:
select *from sys.certificates;

do you see your encryption certificate on BOTH servers?

PearlJammer1
PearlJammer1
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7313 Visits: 1645
Hi
Yes I only see my encrypted certificate on the primary.
I guess I have to restore these on the secondary then (as you say merely copying them to a directory does not do anything).
jasona.work
jasona.work
SSC-Forever
SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)SSC-Forever (44K reputation)

Group: General Forum Members
Points: 44298 Visits: 16677
PearlJammer1 - Wednesday, February 7, 2018 7:14 AM
Hi
Yes I only see my encrypted certificate on the primary.
I guess I have to restore these on the secondary then (as you say merely copying them to a directory does not do anything).

That would be correct. Although generally, I'd lean towards creating a new master key on the secondary server, then restore the TDE certificate using that new master key. It'll still be able to decrypt your TDE backups.

However, and bear in mind I don't use log shipping, it's entirely possible you're going to need to "re-seed" your secondary from a fresh, TDE-protected full database backup. I'd even guess it's going to be required. You *might* be able to sneak around that by restoring the certificate to the secondary, then enabling TDE on the secondary database using the certificate.
Perry Whittle
Perry Whittle
SSC Guru
SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)SSC Guru (214K reputation)

Group: General Forum Members
Points: 214212 Visits: 18590
PearlJammer1 - Wednesday, February 7, 2018 5:29 AM
Hi
We already have an established Log Shipping environment.The secondary has the databases in the 'Non recovery mode'
I have just enabled TDE on the primary server to one of the databases that is getting log shipped.
The master Key and certificate have been backed up to the primary server.
I copied and pasted these 2 files over to the secondary server (but interestingly cannot actually see them in the location i pasted to)
Since TDE was turned on, I am getting the LSAlert every 2 mins saying the database has a restore threshold of 45 mins and is out of sync. No restore was performed for 46 minutes......
If i check the job history the LSRestore job it has not failed.
Does the Log shipping Secondary need to have these Master certificate/Private key files restored for Log shipping to work?
Also, can I remove the Master certificate and private key files off the Primary C drive to a secure network location without breaking TDE?
Thanks


The secondary server needs to have a backup of the cert restored from the primary in order for TDE to be complete.
You already have a backup of the cert as you indicated, so go to the secondary instance and if no database master key already exists in the master database, then create one.
Once this is done create as new cert on the secondary specifying the backup of the primary cert.
See my article at this link for creating certs from backups

http://www.sqlservercentral.com/articles/Security/122707/

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

"Ya can't make an omelette without breaking just a few eggs" ;-)
PearlJammer1
PearlJammer1
SSCertifiable
SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)SSCertifiable (7.3K reputation)

Group: General Forum Members
Points: 7313 Visits: 1645
Thanks everyone for all your input.
I have done what was suggested, that is, on the secondary I created a new master key on the secondary, and created a new certificate from the backup of the primary cert.
As an extra step, and I don't know if this was required but it worked anyway, was that I removed the already restoring logshipped database that was present before TDE was turned on, and I re-established Logshipping from the primary to the secondary (this extra bit was done after I'd created the TDE configurations). What I did notice was that the database full backup that gets restored to initialise Log shipping (25Gb .bak file) did take considerably longer probably due to the overhead of encryption process.
Thanks for all your help guys.
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