TDE and Transaction Logs

Question

TDE and Transaction Logs

cdavis

SSC Enthusiast

Points: 175
More actions
October 8, 2020 at 2:19 am

#3793882

Howdy,
Question: Is it possible to decrypt a Transaction Log taken off a database against which TDE is applied?
Context: Business is transferring a portion of functionality to an outsourcer, this requires us to shift a substantial quantity of SQL data in a short timeframe. We'd like to use a delta approach to minimise the data quantity at final cutover - essentially sending full backups prior then sending weekly transaction log backups until cutover occurs.
Problem Space: not surprisingly, our security architects do not want to share our Production TDE certificates with the other organisation. Hence, we need to give them un-encrypted information. Easy enough for the initial full backup (restore to a staging server that has the Prod TDE cert, decrypt, backup, send) but complex for the Transaction Logs - hence the question.
Hope I've been able to describe the problem, any & all help greatly appreciated
cheers,
chris
April 7, 2021 at 7:48 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

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

Cebisa SSC Enthusiast Points: 151 More actions · Answer 1

Unusual decision by the security team. You are going them all your data and a tde certificate would protect the backup in transit, but security is worried about a certificate?

Could you ask security to give you a new tde certificate that you can share?

You have already applied the certificate, which encrypts the data files. If you remove the tde certificate it's thumbprint is still in the transaction backup files. You can test this by creating a small database, applying tde, removing it and trying to restore it on another instance

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 2

There is no way to decrypt it (I mean, maybe there's some deep level hack, but nothing functional within normal operations).

I agree with @Cebisa. Get a certificate you can share. Use that. For crying out loud, they have the data. What the heck does a certificate matter?

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

cdavis SSC Enthusiast Points: 175 More actions · Answer 3

Thanks Cebisa & Grant - your sentiments reflect mine, they already have the data so why is there a problem with a cert :). But, as I'm sure you guys know, there can always be obstacles that are illogical, I just have to work my way through them. Closing out the option of decrypting the logs is one step in the right direction.

Thanks for your assistance, greatly appreciated

chris

Grant Fritchey SSC Guru Points: 398679 More actions · Answer 4

Good luck on it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

arjunranamca Newbie Points: 2 More actions · Answer 5

Hi All,

Just want to clarify on some basic knowledge on TDE.

In TDE, encryption is performed at page level. It encrypts .mdf pages as well as .ldf pages. It means whatever the data on that page is also encrypted ? Please help me to understand the TDE process ( behind the scene).

arjunranamca Newbie Points: 2 More actions · Answer 6

Hi Grant

Just want to clarify on TDE.

In TDE, encryption is performed at page level. It encrypts .mdf pages as well as .ldf pages. It means whatever the data on that page is also encrypted ? Please help me to understand the TDE process ( behind the scene).