TDE and Transaction Logs

  • 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

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • 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

  • 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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Encryption is performed at the page level in the database file. When Transparent Data Encryption (TDE) is enabled for the database, it reads the page from the data files to the buffer pool, encrypts the page, and writes it back to disk.

    https://wallarm.com/

Viewing 6 posts - 1 through 6 (of 6 total)

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