September 3, 2014 at 3:32 am
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
September 6, 2014 at 1:00 pm
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.
September 23, 2014 at 4:12 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 23, 2014 at 4:34 am
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
September 23, 2014 at 5:19 am
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.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 23, 2014 at 7:16 am
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
September 23, 2014 at 7:28 am
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
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
November 23, 2016 at 6:54 am
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.
November 23, 2016 at 9:06 am
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 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply