none
Can't restore backup from TDE server even after applying SP2. backup created with encryption off.

    Question

  • This worked before - customer has 2008 R2 SP2 Enterprise with database protected with TDE. He turns TDE off, backsup, zip, sends to me, enables encryption

    Today that stopped working. I have 2008 R2 64 Enterprise. After failures I applied SP2. Still no joy - backup proceeds to 100%, then give message below - can't find server certificate:

    Restore failed for Server 'daves390'.  (Microsoft.SqlServer.SmoExtended)

    ADDITIONAL INFORMATION:

    System.Data.SqlClient.SqlError: Cannot find server certificate with thumbprint '0x237262F9B9E8A751C896B93ECBA618D59F63E469'. (Microsoft.SqlServer.Smo)

    Any help would be appreciated!

    Wednesday, July 10, 2013 12:38 AM

Answers

  • After turning TDE off, although command finishes immediately, the database is not instantly decrypted. It takes some time (potentially very long time for large database and busy systems) to decrypt entire database.

    Is the decryption process really finished they can check with:

    select * from sys.dm_database_encryption_keys

    There is percent_complete and ENCRYPTION_STATE column:

    -- 0 = No database encryption key present, no encryption
    -- 1 = Unencrypted
    -- 2 = Encryption in progress
    -- 3 = Encrypted
    -- 4 = Key change in progress
    -- 5 = Decryption in progress
    -- 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

    If ENCRYPTION_STATE is not 1 or 0, database was not fully decrypted. Also, the log file is encrypted, that also might be the issue if you got within the backup a portion of the log file that is still encrypted.

    Encrypt/decrypt is very resource intensive operation and when decrypted they expose their data files. Maybe it would be safer and less resources wasted if they give you a backup of a certificate, since you are allowed to read that data?

    • Marked as answer by DaveO17 Wednesday, July 10, 2013 9:38 PM
    Wednesday, July 10, 2013 1:15 AM

All replies

  • After turning TDE off, although command finishes immediately, the database is not instantly decrypted. It takes some time (potentially very long time for large database and busy systems) to decrypt entire database.

    Is the decryption process really finished they can check with:

    select * from sys.dm_database_encryption_keys

    There is percent_complete and ENCRYPTION_STATE column:

    -- 0 = No database encryption key present, no encryption
    -- 1 = Unencrypted
    -- 2 = Encryption in progress
    -- 3 = Encrypted
    -- 4 = Key change in progress
    -- 5 = Decryption in progress
    -- 6 = Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.)

    If ENCRYPTION_STATE is not 1 or 0, database was not fully decrypted. Also, the log file is encrypted, that also might be the issue if you got within the backup a portion of the log file that is still encrypted.

    Encrypt/decrypt is very resource intensive operation and when decrypted they expose their data files. Maybe it would be safer and less resources wasted if they give you a backup of a certificate, since you are allowed to read that data?

    • Marked as answer by DaveO17 Wednesday, July 10, 2013 9:38 PM
    Wednesday, July 10, 2013 1:15 AM
  • Thank you for the response. We were able to restore the backup.

    We will take your advice and set our system up with a copy of their certificate.

    Wednesday, July 10, 2013 9:40 PM