SQL server restore from backup files on azure storage blob container (using SAS token)

Question

I have set up a SAS token in an azure blob storage container. It is currently valid, as demonstrated by the fact that this command in windows cmd.exe works:

azcopy list "https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/path/to/file.BAK?<SAS token>"

and return as output:

INFO: ; Content Length: 7.58 GiB

Now if I issue the following command in SQL server via SSMS (server runs locally on the same machine that issues the azcopy command, and the values in <>-brackets are identical as above. Note that I have no "/" at the end of the container name and no "?" in the token, following Microsoft documentation's recommendations):

CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] 
 -- this name must match the container path, start with https and must not contain a forward slash at the end
WITH IDENTITY='SHARED ACCESS SIGNATURE' 
 -- this is a mandatory string and should not be changed 
 , SECRET = '<SAS token>';
GO
RESTORE HEADERONLY FROM
URL = N'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/path/to/file.BAK';

I get an error after the RESTORE command:

Msg 3201, Level 16, State 2, Line 28
Cannot open backup device 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/path/to/file.BAK'. Operating system error 3(The system cannot find the path specified.).
Msg 3013, Level 16, State 1, Line 28
RESTORE HEADERONLY is terminating abnormally.

I checked and double checked that there is no typo in the paths, and that the tokens are identical. <SAS token> is in format

sv=<date>&st=<datetime>&se=<datetime>&sr=c&sp=rl&sig=<sig_token>

As a final point, if I DROP the credential before RESTORE, I receive a different error message

Operating system error 86(The specified network password is not correct.).

so it looks like the token works...

I would appreciate some suggestions on what could possibly go wrong here, or how I could troubleshoot why it's behaving different than the azcopy utility.

EDIT 1: A comment has been made about case sensitivity of the storage file path.

<mystorageaccountname> and <mystorageaccountcontainername> are lower case both in Microsoft Azure Storage Explorer and in the azcopy and SQL commands.

path/to/file.BAK is in mixed case, and also identical in Microsoft Azure Storage Explorer and in the azcopy and SQL commands.

• pygri: Please let me know if something is unclear in the question as stated, I will be happy to provide additional information if needed.
• GMassDBA: just a hunch - could you confirm the casing on the file path (including file name and extension) is the same in your restore command and the azure storage container? IIRC I had something similar happen to me and that somehow made it work.
• pygri: @GMassDBa thanks for your comment. Do you mean to make sure the file path is exactly equal without upper/lower case differences? In that case, yes, the /path/to/file.BAK part of both the azcopy and the SQL command is identical to how it is displayed in the MS Azure Storage Explorer program. Same for the storage account and container name.

Answer 1

I found the reason for the problem.

To list the file using azcopy, it is enough to assign Read permission in the SAS settings.

However, in order to execute a command such as RESTORE HEADERONLY, you need to have both Read and Write permission in the SAS settings.

After creating a new SAS token with Read & Write properties, it worked.