Restoring Database from Azure Blob Storage failing from SSMS while using RESTORE FILELISTONLY

Question

I am trying to restore a SQL 2016 database backup file which is in Azure Blob Storage from SSMS using the below T- SQL command :

RESTORE FILELISTONLY FROM URL = 'https://.blob.core.windows.net//.bak' GO

It works fine with my normal Azure subscription. But when I use a CSP account ,I get the below error :

Cannot open backup device 'https://.blob.core.windows.net//.bak'. Operating system error 86(The specified network password is not correct.).

Any help on fixing this issue is greatly appreciated.

gjh71 gjh71 · Accepted Answer · 2017-05-15T12:17:06

Following the steps below you should be able to get the file-list. First you need to create a 'credential': e.g.

create credential [cmbackupprd-sqlbackup] 
with 
      identity = '<storageaccountname>', 
      secret = 'long-and-lengthy-storageaccountkey'

Now you can use this credential to connect to your storage-account.

restore filelistonly
from URL = 'https://yourstorageaccount.blob.core.windows.net/path/to/backup.bak'
with credential='storageaccount-credential'

Note, I'm asssuming the backup is made directly from sql to azure blob-storage. Otherwise you might need to check the blob-type.