How to fix SQL backup to URL failure – operating system error 50

Posted in: Cloud, Microsoft SQL Server, Technical Track
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

BACKUP DATABASE AdventureWorks2016 TO URL = ‘https://<mystorageaccountname>.blob.core.windows.net/<mycontainername>/AdventureWorks2016.bak’ WITH CREDENTIAL = ‘<mycredentialname>’ ,COMPRESSION ,STATS = 5; GO

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.

 

email

Interested in working with Pio? Schedule a tech call.

Lead Microsoft Data Platform Consultant

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *