SQL backup to Azure URL Blob: “Operating system error 50(The request is not supported.)”

I wanted to setup an on-premises SQL server to backup directly to Azure Blob storage, using Microsoft’s walk-through: https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-use-azure-blob-storage-service-with-sql-server-2016?view=sql-server-ver16.

However, there is a mistake in the provided Powershell script – so things won’t work as expected.

TLDR; Script truncates Shared Access Signature – add “s” at start of SECRET

The script on the above page (in Step 1) ends by giving you a T-SQL command that includes the Shared Access Signature for the container/policy it creates, something like this:

Shared Access Signature=  v=<date>&si=<backuppolicy>&sr=c&sig=<verylongsignaturestring>
Credential T-SQL
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] WITH IDENTITY='Shared Access Signature', SECRET='v=<date>&si=<backuppolicy>&sr=c&sig=<verylongsignaturestring>'

However, the script drops the first character from the signature/secret – for things to work you need to add the letter “s” to the beginning of the Secret when you put this into SQL Server Management Studio, like this:

USE master
CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] WITH IDENTITY='Shared Access Signature', SECRET='sv=<date>&si=<backuppolicy>&sr=c&sig=<verylongsignaturestring>'
GO

Then your BACKUP DATABASE TO URL command will work.

The error you got that brought you here

If you use Microsoft’s script as-is and then try to run:

-- To permit log backups, before the full database backup, modify the database to use the full recovery model.
USE master;
ALTER DATABASE <YourDatabase>
   SET RECOVERY FULL;

-- Back up the full AdventureWorks2022 database to the container that you created in section 1
BACKUP DATABASE <YourDatabase>
   TO URL = 'https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/<YourDatabase>_onprem.bak'

You’ll get:

Msg 3201, Level 16, State 1, Line 4
Cannot open backup device ‘https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>/<YourDatabase>_onprem.bak’. Operating system error 50(The request is not supported.).
Msg 3013, Level 16, State 1, Line 4
BACKUP DATABASE is terminating abnormally.

Initially I thought this was a “your version of SQL doesn’t support this”, or maybe “your Windows version” – but after a bit of searching it became clear that this is a generic permissions issue.

But permissions should be fine- I used a Microsoft provided script! 🙂

The mistake in the script

The script stores the returned AzStorageContainerSASToken in a variable called $sas.

# Gets the Shared Access Signature for the policy
$sas = New-AzStorageContainerSASToken -name $containerName -Policy $policyName -Context $storageContext

When presenting this to the user, this variable is trimmed using Substring() to drop the first character:

Write-Host ‘Shared Access Signature= ‘$($sas.Substring(1))”

And:

# Outputs the Transact SQL to the clipboard and to the screen to create the credential using the Shared Access Signature
Write-Host ‘Credential T-SQL’
$tSql = “CREATE CREDENTIAL [{0}] WITH IDENTITY=’Shared Access Signature’, SECRET='{1}’” -f $cbc.Uri,$sas.Substring(1)

But you need that first character!

After running the script you can just put $sas into Powershell to see the full value of the variable.

Theory on why

Based on comments I’ve seen while researching this issue (https://serverfault.com/questions/795767/sql-server-2016-web-backup-to-url) it looks like credentials used to be presented with an errant question mark at the start – which wasn’t part of the SAS so needs to be removed before use.

I suspect that the script in the walk-though took account of this and dropped the first character of the returned string so things would work.

My theory is that later someone fixed the “errant question mark” issue, so that Azure returned the SAS ready-to-use – but this script wasn’t updated – so it was now removing “s”, and not “?”.

If they had set the script to check if the first character was a “?” and remove that rather than just doing a substring(1) it would still work today…

Update – they’ve fixed it!

Well, kinda – on a different page:

https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url?view=sql-server-ver16.

This page has code that trims the key – but only if it’s a question mark – perfect!

# Sets up a Stored Access Policy and a Shared Access Signature for the new container
$policy = New-AzStorageContainerStoredAccessPolicy -Container $containerName -Policy $policyName -Context $storageContext -ExpiryTime $(Get-Date).ToUniversalTime().AddYears(10) -Permission “rwld”
$sas = New-AzStorageContainerSASToken -Policy $policyName -Context $storageContext -Container $containerName
Write-Host ‘Shared Access Signature= ‘$($sas.TrimStart(‘?’))”