November 26, 2021 at 3:43 pm
Hi all,
So I have an issue I'm struggling to resolve.
So we have a server in Azure (its a VM running SQL 2019) and we set up Azure to do backups to BLOB storage via the management add-in, all fine so far, it created the DB backup, the certificate, Private Key and MasterKey (for server).
Now I'm trying to restore the DB to a test restore server (also SQL 2019), so on my restore server which has a master key already.
I restore the certificate like this
CREATE CERTIFICATE TDECert
FROM FILE = 'F:\restoreTest\Server_Certificate_CCYYMMDDHHMMSS.cer'
WITH PRIVATE KEY (
FILE = 'F:\restoreTest\Server_CertificatePvk__CCYYMMDDHHMMSS.pvk',
DECRYPTION BY PASSWORD='SuperSecurePassword')
This works fine, so I know they password, key and certificate are all a set.
However when I do my restore (Filelist only for this sample).
RESTORE FILELISTONLY FROM DISK = F:\restoreTest\DB_c47a1819648e4e9d88b3111f682ccb00_20211125191506+00.bak'
I get this
Cannot find server certificate with thumbprint '0x477BB40D3618B2E162F70B62693D11F6FA3A360F'.
Msg 3013, Level 16, State 1, Line 10
RESTORE FILELIST is terminating abnormally.
To me this is saying the backup file itself is not encrypted by this certificate and there should be another one, is that right? I cant see another one anywhere in the BLOB storage.
Or am I missing something else?
Thanks,
Nic
November 26, 2021 at 8:26 pm
That sounds like your backups are encrypted. What certificate did you use to back up the database? I expect you didn't restore that certificate onto the secondary system.
SSC actually has a good writeup on backing up and restoring encrypted databases here:
https://www.sqlservercentral.com/blogs/how-to-encrypt-and-restore-your-sql-server-database-backups
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
November 26, 2021 at 10:03 pm
Exactly what I thought but its the Azure service which set up the backup encryption, the only files it lets me access in the BLOB storage are the backup file itself, the Certificate and the Private Key. I literally have no other certificate to use.
I've raised with Microsoft and am having a call with them on Monday to discuss, until then I've had to revert back to my normal backup process and not use the Azure manage ones.
Thansk,
Nic
November 29, 2021 at 7:11 pm
Go to where the backups were created and look to see if you're grabbing the right certificate:
SELECT * FROM master.sys.certificates WHERE thumbprint = 0x477BB40D3618B2E162F70B62693D11F6FA3A360F
Eddie Wuerch
MCM: SQL
November 29, 2021 at 9:47 pm
I know for Azure SQL DB, they can store the cert in Azure Key Vault. Not sure what happens if you allow them to manage your backups. Is this for sure a VM, not MI?
Can you access the original instance where the db was backed up? Possibly you can backup the certificate again.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy