SQL Server on Azure Virtual Machines
An Azure service that is used to provision Windows and Linux virtual machines.
374 questions
How to restore certificates used for the Azure SQL VM Automated Backup
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(6.4, 94%, 10%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EKR%3C/text%3E%3C/svg%3E)
Keiran Royston
0
Reputation points
I'm testing out encryption for encrypting the SQL backups taken for Azure SQL VM databases, however, I'm unable to restore the certificates generated when setting up encryption as I cannot find the .pvk files.
Are these stored in another location or are the certificates restored using another method? I can find the certificates in the 'automaticbackup' container of the storage account but not any .pvk files, which the 'CREATE CERTIFICATE FROM FILE' T-SQL command requires.
Breadcrumb to get to Automated backups
SQL virtual machine > VIRTUAL MACHINE NAME HERE > Backups > Automated Backup
Values set
Encryption: Enable
Password
Confirm Password.
{count} votes
-
Oury Ba-MSFT 18,941 Reputation points Microsoft Employee2024-07-26T18:02:53.9266667+00:00 @Keiran Royston Thank you for reaching out.It seems like you’re having trouble locating the .pvk files needed for the ‘CREATE CERTIFICATE FROM FILE’ T-SQL command. The .pvk files (private key files) might not be directly accessible in the storage account’s ‘automaticbackup’ container.
To restore a private key to an existing certificate in the database, use the ALTER CERTIFICATE statement.
When performing a backup, the files will be ACLd to the service account of the SQL Server instance. If you need to restore the certificate to a server running under a different account, you'll need to adjust the permissions on the files so that they're able to be read by the new account.
Permissions
Requires CONTROL permission on the certificate and knowledge of the password that is used to encrypt the private key. If only the public part of the certificate is backed up, this command requires some permission on the certificate and that the caller hasn't been denied VIEW permission on the certificate.
-
Oury Ba-MSFT 18,941 Reputation points Microsoft Employee
2024-08-06T17:23:13.0333333+00:00 @Keiran Royston We haven't heard back from you since my last comment. Could you please share the current status of the issue.
Regards,
Oury
-
Oury Ba-MSFT 18,941 Reputation points Microsoft Employee
2024-08-15T17:16:34.6266667+00:00 @Keiran Royston We haven't heard back from you since my last comment. Could you please share the current status of the issue.
Regards,
Oury
-
Keiran Royston 0 Reputation points
2024-08-21T15:54:53.3466667+00:00 Hello.
Thank you for responding.In the situation where a Virtual Machine is lost and needs to be created from scratch, would the private keys used by the certificate to encrypt backups be kept in Azure somewhere or would we need to back these up as part of a separate process?
Sign in to comment
Sign in to answer