Cannot open backup device from BLOB Storage - Access is Denied

Question

I have a SQL backup in a storage container in Azure and am trying to restore the DB to my Azure SQL instance from another Azure VM through SSMS.

The public access level on the storage container is set to 'Blob'. My client has whitelisted the public IP of the Azure VM where I'm running SSMS for the restore.

I'm also logging into SSMS using an account that has db owner permissions.

The BLOB SAS I was given has full list and read-write access.

If I try to retrieve the file list OR try to do my restore, I get "Cannot open backup device..... Operating system error 5(Access is denied.). -- full error at bottom.

Any thoughts?

-- Creates credential
USE master
CREATE CREDENTIAL [https://******prodstorageaccount.blob.core.windows.net/backups]
WITH IDENTITY='SHARED ACCESS SIGNATURE'
, SECRET = 'removed for posting'
GO

-- Test retrieving file list from URL -- GENERATES ERROR BELOW
RESTORE FILELISTONLY FROM URL =
'https://******prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'

-- Restore from URL -- GENERATES ERROR BELOW
RESTORE DATABASE GGC_iMIS_TEST FROM URL =
'https://******prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'

Msg 3201, Level 16, State 2, Line 11
Cannot open backup device 'https://******prodstorageaccount.blob.core.windows.net/backups/GGC_iMIS-5-10-21.BAK'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 11
RESTORE FILELIST is terminating abnormally.

Answer 1

Hi,

Please take a look on following blog.

Troubleshooting potential backup/restore issues on Azure SQL Managed Instance

If you are getting an error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall.

Also a similar post.
https://dba.stackexchange.com/questions/217800/not-able-to-create-database-or-restore-database-on-the-azure-sql-managed-instanc

Answer 2

The issue at hand seems related to this, and we've attempted this resolution, but no luck:

https://simplifysql.blogspot.com/2019/10/whitelisting-your-managed-instance.html