0

I have SQLServer 2022 Developer on my machine. A customer has send me a SQLS 2019 backup file. When I attempt to restore it into a fresh DB, I get:

Cannot find server certificate with thumbprint '0xbiglongnumber'.

I wanted them to send an unencrypted backup, I am not sure if that is what they have done (is there any way to tell?).

Failing that, I had them send me the certificate .cer file and the password .pvk file. Following these instructions, I created a master key on my local machine, which I can see as ##MS_DatabaseMasterKey## in sys.symmetrickeys. I then imported the certificate using:

CREATE CERTIFICATE TDECer  
  FROM FILE = N'C:\Backups\TDECert.cer'
  WITH PRIVATE KEY ( 
    FILE = N'C:\Backups\TDECert.pvk',
  DECRYPTION BY PASSWORD = 'THE_PASSWORD_THEY_PROVIDED'
  );
GO

This succeeded, and I can see the resulting certificate in sys.certificates:

TDECer  256 1   MK....

This should leave two steps, creating a DB password and turning on encryption. So I did:

CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDECer;
GO

This fails:

Cannot find the certificate 'TDECer', because it does not exist or you do not have permission.

I am the only user account on this machine and have full admin privs. Googling the topic turns up nothing cogent. Did I miss a step?

5
  • To clarify, backing up a TDE database is always encrypted (because the source database is encrypted). You can then ALSO use backup encryption using the same or a different certificate, which will add a new layer of encryption which will have to be resolved. If they are using TDE then the only way for them to send you a backup that is NOT encrypted, is to disable TDE on the source database, wait for that process to complete, then take an unencrypted backup and send that to you. Feb 13 at 14:04
  • @Charlieface - perhaps I am confused on this point, but when I performed the CREATE CERT I was "USE"d to the target DB. Is that not enough? Do I have to specify the target somehow? SELECT * FROM sys.certificates does not indicate a DB name. Feb 13 at 14:22
  • @JonathanFite - as you likely noticed, this is all new to me, I work entirely client-side and TDE is simply something I have never had to deal with previously. But... if I were to turn encryption on my local copy using their certificate and key, then I should be able to restore it, yes? If that is correct, the issue is that I cannot turn on encryption due to the error reported above. They swear up and down they did not turn on any additional encryption on the backup. Is there any way to know? Feb 13 at 14:26
  • Sorry the CERTIFICATE needs to be in master but the DEK needs to be in the relevant database. As mentioned, backup/restore and TDE are orthogonal and use separate encryption chains, do not confuse them. So I'm unclear what you are trying to do now: do you want to switch on TDE on your local copy you have restored, or are you still trying to restore an encrypted backup? Feb 13 at 15:27
  • @Charlieface - All I want to do is get the client data up and running on my machine so I can produce SQL scripts for some custom reporting. The customer performed a "normal backup" and sent me the results. When I attempt to restore, I get an error (in another thread, sorry) about missing the "thumbprint" of the client's cert, and the restore fails. The page above suggests the solution is to turn TDE on for the local copy and restore. Perhaps that is not the right solution, but it's all I could find online. Feb 13 at 15:41

1 Answer 1

1

Cannot find server certificate with thumbprint '0xbiglongnumber'.

This succeeded, and I can see the resulting certificate in sys.certificates:

The certificate in sys.certificates should have a thumbprint that matches 0xbiglongnumber. If it does, then you have the correct certificate for that specific set of encrypted data. These certificates should be located in the master database.

If they do not match, you'll need that specific certificate and private key from the customer. Note that there can be up to two certificates that are encrypting data at any point in time for TDE due to certificate rotation, and thus it may require more than a single certificate (this is rare), additionally, a database that has had TDE turned off is not fully decrypted as any log that was previously encrypted will still be encrypted and require the certificates to use.

2
  • > umbprint that matches 0xbiglongnum The cert with this number is found in master. Feb 15 at 18:52
  • Great, then either bring the database online or drop it if it's there and rerun the restore. Feb 15 at 21:06

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.