certificate problem for restoring a database even if no database or backup encryption

  • vincent.D_FR

    Mr or Mrs. 500

    Points: 522

    Hello,

    I have a problem that seems to be a bug existing in SQL 2008 R1/R2 http://support.microsoft.com/kb/2300689/en-us ). But I work on SQL 2014, so I don't know what to think.

    I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B".

    There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".

    I look at sys.databases : not encrypted.

    I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.

    I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :

    Msg 33111, Level 16, State 3, Line 1

    Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I don't understand why. It really seems that it's the same bug that on SQL 2008.

    Any idea, anyone ?

    Thanks in advance,

    Regards,

    Vincent

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714093

    vincent.D_FR (9/3/2014)


    Hello,

    I have a problem that seems to be a bug existing in SQL 2008 R1/R2 http://support.microsoft.com/kb/2300689/en-us ). But I work on SQL 2014, so I don't know what to think.

    I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B".

    There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".

    I look at sys.databases : not encrypted.

    I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.

    I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :

    Msg 33111, Level 16, State 3, Line 1

    Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I don't understand why. It really seems that it's the same bug that on SQL 2008.

    Any idea, anyone ?

    Thanks in advance,

    Regards,

    Vincent

    A few things. I assume this is TDE, correct? You didn't specify and there are other ways to encrypt.

    Second, how did you back up? If you didn't use INIT, you could have two backups in there that are causing issues.

    Third, there were issues early on with disabling encryption. You might try restarting the instance, then running a backup and seeing if that can be restored.

  • Perry Whittle

    SSC Guru

    Points: 233678

    vincent.D_FR (9/3/2014)


    Hello,

    I have a problem that seems to be a bug existing in SQL 2008 R1/R2 http://support.microsoft.com/kb/2300689/en-us ). But I work on SQL 2014, so I don't know what to think.

    I did tried the encryption on server "A" for database "AdventureWorks2012". Then I tried to restore to server "B".

    There was the certificate issue, and I thought "of course : it's encrypted ! Let's deactivate it". So here I go "ALTER DATABASE AdventureWorks2012 SET ENCYRPTION OFF".

    I look at sys.databases : not encrypted.

    I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.

    I move my backup to my other server where encryption was never configured (so no certificate, nothing...), and I have the error :

    Msg 33111, Level 16, State 3, Line 1

    Cannot find server certificate with thumbprint '0xFA130E58C999C4919B8975999C83A75A403B11D8'.

    Msg 3013, Level 16, State 1, Line 1

    RESTORE DATABASE is terminating abnormally.

    I don't understand why. It really seems that it's the same bug that on SQL 2008.

    Any idea, anyone ?

    Thanks in advance,

    Regards,

    Vincent

    When restoring the protected backup to a new server you also need to restore a copy of the certificate used for the backup. See my guide at this link[/url]

    Steve Jones - SSC Editor (9/6/2014)


    I assume this is TDE, correct?

    No, the OP is using encrypted backups in 2014, it's a new feature

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Gail Shaw

    SSC Guru

    Points: 1004424

    Perry Whittle (9/23/2014)


    Steve Jones - SSC Editor (9/6/2014)


    I assume this is TDE, correct?

    No, the OP is using encrypted backups in 2014, it's a new feature

    Where do you see that? OP says:

    I backup using no encryption, I verify using msdb.dbo.backupset : not encrypted.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Perry Whittle

    SSC Guru

    Points: 233678

    aplogies, my mistake. Even so if it's TDE the link i posted still applies, it details how to backup and restore the cert to a new server, which is the same whether using encrypted backups or backups of TDE protected databases.

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • vincent.D_FR

    Mr or Mrs. 500

    Points: 522

    Thanks for your answers.

    Yes, it was about TDE.

    I will look at you link very soon and try to apply your suggestions.

    Regards,

    Vincent

  • Perry Whittle

    SSC Guru

    Points: 233678

    vincent.D_FR (9/23/2014)


    Thanks for your answers.

    Yes, it was about TDE.

    I will look at you link very soon and try to apply your suggestions.

    Regards,

    Vincent

    OK, just pull out the sections relating to

    • creating a new self signed cert
    • backup up a cert
    • create a cert from a backup

    -----------------------------------------------------------------------------------------------------------

    [font="Tahoma"]"Ya can't make an omelette without breaking just a few eggs"[/font] 😉

  • Prometheus112

    SSC-Addicted

    Points: 403

    Perry, all due respect, but as I was reading the OP, it sounds like he is using TDE, decrypting the databases, taking a backup (of the decrypted databases) and then trying to move them to a new instance. He should not have to move the certs at ALL. That is the point of decrypting the databases before backing them up. I am also having this problem on SQL 2012 SP3: you decrypt the databases, back them up, move the backups to your new server, and VOILA!! you still need the *&% cert because it says that a thumbprint is missing. If I mistake the OPs question, let me know, but my issue is still an issue regardless.

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714093

    I've tested this on SQL 2016, but it should be the same on 2014. I didn't have two 2014 instances to play with at the moment.

    When you decrypt the database, I assume you're running

    ALTER DATABASE TDE_Primer

    SET ENCRYPTION OFF

    ;

    However, the Database Encryption Key still exists at this point. If you run this:

    SELECT

    db.name,

    db.is_encrypted,

    dm.encryption_state,

    dm.percent_complete,

    dm.key_algorithm,

    dm.key_length

    FROM

    sys.databases db

    LEFT OUTER JOIN sys.dm_database_encryption_keys dm

    ON db.database_id = dm.database_id;

    GO

    You'll likely see encryption status as 1, not 0.

    This means if you take a backup, there's still a DEK, which does need the certificate for decryption. What I was able to do is this:

    DROP DATABASE ENCRYPTION KEY

    THEN, take a backup. This backup file will not have a DEK, and should just restore.

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

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