Problem:
You run an SQL backup to URL and get the following error:Msg 3201, Level 16, State 1, Line 1 Cannot open backup device 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks_full_20190420.bak'. Operating system error 50(The request is not supported.). Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally.
Cause:
This is an Access Denied error. To be able to back up to Azure Blob Storage, your backup command must have a valid credential that has access to the blob storage. This can be done in two ways.1. Shared Access Signature
If you are using Shared Access Signature, your credential should have the container URL and the shared access signature token. This is the recommended way since this is more secure. The shared access token has read and write permissions to a specific container, compared to the identity/storage account and access key which have permissions to all containers. If you are using this, make sure your access policy for it is configured so it can access the blob storage
Script to Create the Credential
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = '<mycredentialname>') CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>' ,SECRET = '<mystorageaccountaccesskey>';
Script to Take the Backup
BACKUP DATABASE AdventureWorks2016 TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak'; GO
2. Identity and Access Key
This is the classic way of accessing your blob storage. The credential should have the storage account as the identity and the storage access key as its secret.
Script to Create the Credential
IF NOT EXISTS (SELECT * FROM sys.credentials WHERE name = '<mycredentialname>') CREATE CREDENTIAL [<mycredentialname>] WITH IDENTITY = '<mystorageaccountname>' ,SECRET = '<mystorageaccountaccesskey>';
Script to Take the Backup
Review your credentials, make sure your access key is correct and, if you're using SAS, make sure that the access policy is defined and you are pointing to the correct container that it has access to. For more information on URL backups as well as script samples, you can refer at Microsoft's SQLDOC.BACKUP DATABASE AdventureWorks2016 TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak' WITH CREDENTIAL = '<mycredentialname>' ,COMPRESSION ,STATS = 5; GO
