Sample Header Ad - 728x90

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

1 vote
1 answer
2207 views
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://.blob.core.windows.net//path/to/file.BAK ?"
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):
SQL
CREATE CREDENTIAL [https://.blob.core.windows.net/]  
  -- 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 = '';
GO

RESTORE HEADERONLY FROM
URL = N'https://.blob.core.windows.net//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://.blob.core.windows.net//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. `` is in format
sv=&st=&se=&sr=c&sp=rl&sig=
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. ` and ` 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.
Asked by pygri (63 rep)
Nov 26, 2021, 10:52 AM
Last activity: Nov 30, 2021, 10:51 AM