TDE and Transaction Logs

  • cdavis

    SSC Rookie

    Points: 35

    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

  • Cebisa

    SSC Journeyman

    Points: 97

    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: 396751

    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

  • cdavis

    SSC Rookie

    Points: 35

    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: 396751

    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

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

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