none
Cannot restore database after TDE disabled RRS feed

  • Question

  • I have a database that has been encrypted with TDE, and I want to make a backup that can be restored without the server's certificate.  In the past I've used the following steps:

    1)  Make a copy of the existing database (usually by restoring a recent backup, encrypted, to a new database on the same server).

    2)  Run ALTER DATABASE dbname SET ENCRYPTION OFF.

    3)  Wait for decryption to finish.

    4)  DROP DATABASE ENCRYPTION KEY on the database.

    5)  Backup the now unencrypted database.

    I currently have a database, though, where these steps are not working.  On another server, when restoring the backup I get the error:  Cannot find server certificate with thumbprint '0xED16...'

    The database is set to simple recovery model, so I don't think the log files could still be encrypted.  I've also tried detaching the database and reattaching to a new server, also without success.

    Is there anything else in the database or backup that could be requiring the server certificate?  

    I should mention that we recently upgraded the server in question to SQL Server 2008 SP1 (from 2008 RTM), and that I am trying to restore the backup on a database running SQL Server 2008 SP2.  

    A few additional tests I've run:

    • I also tested another database (created before upgrading to SP1) on the same server.  It was decrypted and restored correctly on the SP2 server using the above steps.  
    • I also created a new database on the SP1 server, enabled encryption, and performed the above steps to copy and decrypt it and was NOT able to restore it on the SP2 server (same error).  I also tried to restore this database on a SQL 2008 RTM server, without success.

    Any help would be greatly appreciated.

    Monday, December 6, 2010 7:46 PM

Answers

  • Tibor,

    Thanks a ton for your help.  You were right to look at the log file.  

    After running ALTER DATABASE x SET ENCRYPTION OFF, I tried doing several CHECKPOINT commands, but my log file wouldn't seem to give up a few stubborn sections.  Instead (since I'm in simple recovery mode), I took the database offline, deleted the log file, and brought the DB back online.  I then ran DROP DATABASE ENCRYPTION KEY and took a backup.  That backup restored fine on my other server.

    Thanks again for pointing me in the right direction!

    • Marked as answer by Papercutter Tuesday, December 7, 2010 5:57 PM
    Tuesday, December 7, 2010 5:56 PM
  • AFAIK, there can be an unexpected "lag" before yuo have a complete turn-around when enabling/disabling TDE. This is because you need to make sure that tlog contains all either encrypted log records (when turning on) or all only non-encrypted log records (when turning off). My guess is that this is what (for some reason) is happening to you. Being in simple recovery model, you should be able to force a log truncation using the CHECKPOINT command in the database. But perhaps you even need to move off of the current VLF to get rid of all traces of encryption? I'm thinking something like several CHECKPOINT command, investigating the VLF layout and possibly in combination with dome some modifications in the db just to generate "dummy" log records... You find some info on investigating VLF layout here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Papercutter Tuesday, December 7, 2010 5:57 PM
    Tuesday, December 7, 2010 6:56 AM

All replies

  • Hi,

    First of all, thank you for your feedback!

    For setting up TDE, users must do the following steps :

     - Create a master key.

     - Create or obtain a certificate protected by the master key.

     - Create a database encryption key(also known as DEK) and protect it by the certificate

     - Set the database to use encryption.

    Backup files of databases that have TDE enabled are also encrypted by using the database encryption key (DEK).

    When an encrypted database is opened using SQL Server, SQL Server first opens up the boot page which contains the DEK and gets information on how to decrypt it. The thumbprint is used to find the matching certificate or assymmetric key in the master database. These keys are then used to decrypt the DEK. Finally, this decrypted DEK is used to decrypt the actual data pages as they are read from and written to disk.  This is why the certificate is required and as a result, when you restore these backups, the certificate protecting the database encryption key must be available. This means that in addition to backing up the database, you have to make sure that you maintain backups of the server certificates to prevent data loss. Data loss will result if the certificate is no longer available. For more information, see SQL Server Certificates and Asymmetric Keys.

    For more information on understanding TDE please check:

    http://msdn.microsoft.com/en-us/library/bb934049.aspx

    http://blogs.msdn.com/b/sqlsecurity/archive/2010/06/14/database-encryption-key-dek-management.aspx

     

    Hope this helps!

     

    Regards,

    Don Pinto

    SQL Server Engine

     

    Monday, December 6, 2010 9:15 PM
  • Thanks for getting back to me, Don, but I DO understand TDE.  I cannot (meaning I do not want to) export the server certificate out of SQL Server to restore the database.  I want to create a decrypted backup of the encrypted database.  I have done this in the past (many times) and this time I'm having issues.

    I do believe this has to do with having SP1 installed.  Unfortunately, I can't take down the server (as it is a production server) to see if un-installing SP1 fixes the issue.  

    We've also recently changed some SQL server settings (max memory being one).  Are there any server settings that might affect TDE?

     

    Monday, December 6, 2010 10:13 PM
  • AFAIK, there can be an unexpected "lag" before yuo have a complete turn-around when enabling/disabling TDE. This is because you need to make sure that tlog contains all either encrypted log records (when turning on) or all only non-encrypted log records (when turning off). My guess is that this is what (for some reason) is happening to you. Being in simple recovery model, you should be able to force a log truncation using the CHECKPOINT command in the database. But perhaps you even need to move off of the current VLF to get rid of all traces of encryption? I'm thinking something like several CHECKPOINT command, investigating the VLF layout and possibly in combination with dome some modifications in the db just to generate "dummy" log records... You find some info on investigating VLF layout here: http://www.karaszi.com/SQLServer/info_dont_shrink.asp
    Tibor Karaszi, SQL Server MVP | web | blog
    • Marked as answer by Papercutter Tuesday, December 7, 2010 5:57 PM
    Tuesday, December 7, 2010 6:56 AM
  • Tibor,

    Thanks a ton for your help.  You were right to look at the log file.  

    After running ALTER DATABASE x SET ENCRYPTION OFF, I tried doing several CHECKPOINT commands, but my log file wouldn't seem to give up a few stubborn sections.  Instead (since I'm in simple recovery mode), I took the database offline, deleted the log file, and brought the DB back online.  I then ran DROP DATABASE ENCRYPTION KEY and took a backup.  That backup restored fine on my other server.

    Thanks again for pointing me in the right direction!

    • Marked as answer by Papercutter Tuesday, December 7, 2010 5:57 PM
    Tuesday, December 7, 2010 5:56 PM
  • And Don, I apologize: If I had read your second linked article more carefully, I probably would have come to the correct conclusion without further help.

     

    Tuesday, December 7, 2010 7:11 PM
  • No Problem.

    Great to know that the problem was resolved.

    Regards,

    Don Pinto

    SQL Server Engine

    • Proposed as answer by Don Pinto Friday, December 10, 2010 12:14 AM
    Thursday, December 9, 2010 9:58 PM