It’s always a good idea to be up-to-the-speed with the current market trend. As a part of my consulting I have been getting calls from clients having issues with Azure. So, I started learning Azure as well. Let us learn about how to fix the URL error.
Recently, I was trying to take backup of the database to Azure and was stuck with an error message.
Msg 3201, Level 16, State 1, Line 4
Cannot open backup device ‘https://sqlauthsqlbackups.blob.core.windows.net/backups/master.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.
Before I move ahead, I should share that there are two kinds of backup methods available. In both methods we need to create credentials. The credential can be created using either access keys or SAS token. The method which I was using was with SAS token.
My steps were very simple.
- Create a storage account.
- Generated the Shared Access Signature (SAS) from the Portal.
- Created Credential using SAS Token.
- Perform backup of the database using “Backup to URL” command.
I searched on the internet and realized that I missed one step, which is not very explicitly called out in Microsoft documentation.
WORKAROUND/SOLUTION
You need to create a Shared Access Policy and a Shared Access Signature for the new container. There are multiple ways to do it.
- Using PowerShell.
- Using Storage Explorer.
- Using Microsoft Azure Portal.
PowerShell method is explained in this article https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url You can refer code section.
The script which is created does create SAS token, access policy and provides you ready-made T-SQL which you can directly run to create credential.
I have used portal to define the access policy. Here is the screenshot for your quick reference to understand where to click and reach there.
Home > Storage accounts > Account Name > Browse blobs > Blob Name > Access Policy
Once we click on access policy, we need to add a new policy and set all permissions along with dates. After doing that I could take backups successfully.
Reference: Pinal Dave (https://blog.sqlauthority.com)
4 Comments. Leave new
OK, maybe I’m missing something, but … ASSUMING (and yes, that may be the mistake) we are talking cloud Azure and NOT on-prem, I thought one of the “advertised advantages” was they (Microsoft) handle backup’s?!?!?!
Well, Microsoft handles the backup when you are using PaaS offering called “SQL Azure Database” . If you take a Azure VM with SQL on it, then you need to take own backups of database.
Hi Pinal. Thanks for sharing your knowledge with all and sundry. With regards to this error, it may be due to an expired SAS token. If you’re looking for a way to alert yourself on expiring SAS tokens, then you can query the sys.credentials table that has values for create_date and modify_date. This is based on the assumption that you create the credential pretty close to the date the SAS token is created.
In case someone comes across this – Something to check is whether or not the storage account is Standard or Premium – You cannot backup to URL if the storage account is Premium