Backup to URL was introduced in SQL Server 2012 SP1 CU2 and allows backups to be taken directly to an Azure Storage Account. This post will expand on the evolving syntax when using Backup to URL and what I would consider to be best practice for Storage Account access.

If you would like a more general overview of backup techniques in Azure, then please check out the following article: https://blog.coeo.com/backing-up-databases-in-microsoft-azure

Starting with SQL Server 2012, there was one authentication method when using Backup to URL and that was to use either of the Storage Account keys for access. Granting complete access to the Storage Account deviated from security best practice and is only revocable by cycling the Storage Account key used.

From SQL Server 2016 the ability to use Shared Access Signatures (SAS) was made available. This meant that instead of full Storage Account access a much more granular control could be put in place. For example a SAS is created with Read, Write and List permission, which is valid for a certain time period. This is far more appropriate as the permission is reduced as well as being time limited, but if we want to remove or change this access immediately this can only be done by cycling the Storage Account key used. There is no concept of SAS removal and it simply remains valid.

If a SAS token is generated against a container policy there is much greater flexibility. This policy holds the permissions and time limiting factors instead of the SAS token. Creating this policy allows for the permissions to be changed as needed for example write permissions could be removed. If the policy is deleted all SAS tokens generated against it are invalidated and without the need to cycle the storage keys.

In summary there are three methods to access Azure Storage and ideally we want to be using SAS tokens generated against a container policy.

1. Storage Account key(s) - full access, revoked by cycling Storage Account key(s).
2. SAS - restricted access, revoked by cycling storage account key(s).
3. SAS based on container policy - restricted access, revoked by altering/deleting policy or cycling Storage Account key(s).

SQL Server - Credential and Backup

In order to access an Azure Storage Account we must first authenticate. Authentication can occur using either a Storage Account key(s) or a SAS token. If you are using SQL Server 2016 or newer then you will want to use SAS.

Below are two syntax examples using each method to backup a database.

If you haven't got the <secret> data needed for these queries below, please see the Secrets section, below.

Syntax 1. Use the Storage Account key and explicitly specify the credential. The Credential stores a Storage Account key. Identity is the Storage Account name.

CREATE CREDENTIAL [c1] WITH IDENTITY = 'testjgstorage',SECRET = '<StorageAccountKey>';
BACKUP DATABASE [master] TO URL = 'https://testjgstorage.blob.core.windows.net/backups/masterA.bak'
WITH CREDENTIAL = 'c1';

Syntax 2. Use a SAS token and the credential is no longer needed. The Credential stores the SAS token and is named as the URL accessed. Identity is set as the string SHARED ACCESS SIGNATURE.

CREATE CREDENTIAL [https://testjgstorage.blob.core.windows.net/backups]
WITH IDENTITY='SHARED ACCESS SIGNATURE',SECRET = '<SharedAccessSignature>'
BACKUP DATABASE [master] TO URL = 'https://testjgstorage.blob.core.windows.net/backups/masterB.bak';

• Gotcha: If the backup file exists it will throw an error.
• Gotcha: If the storage account is of BlobStorage type you can only use SAS. (BlobStorage accounts support only block and append blobs, and not page blobs.) 
• Gotcha: Premium storage is not supported.

Check other restrictions here: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url

Secrets

The easiest way to retrieve the prerequisite secrets if you do not want to use the PowerShell script is to use Azure Storage Explorer.

Login using the Azure Storage Explorer, select the correct subscription and navigate to the relevant Storage Account in the Storage Accounts node. Then right click and select Copy Primary or Secondary Key (either will work).

The Shared Access Signature can be obtained by navigating to the container and within the Storage Account. Then right click and chose Get Shared Access Signature. Be sure to add the write permission otherwise the backups will fail. The value needed is named Query String and can be copied using the button available.

• Gotcha: Need to remove the leading question mark '?' from the Query String.

An Access Policy can be added by right clicking the container and choosing Manage Access Policies. Click Add to create a new entry and make sure to add write permission. Once saved the method used to generate a Shared Access Signature can be repeated, except this time specify an Access Policy when creating the Shared Access Signature.

PowerShell

Finally, here is a complete script to follow through the entire process for those that want to just set it all up and print out the T-SQL commands.

Step 1. Login to Azure

Login-AzureRmAccount

Step 2. Setup a storage account or specify existing Storage Account.

#Configuration
$ResourceGroupName = 'JonGurgul';
$LocationName = 'north europe';
$StorageAccountName = "testjgstorage";#Must be unique
$StorageContainerName = 'backups';

#Resource group
$ResourceGroup = $(Get-AzureRmResourceGroup -Name $ResourceGroupName -Location $LocationName -ErrorAction SilentlyContinue)
If(!$ResourceGroup){
New-AzureRmResourceGroup -Name $ResourceGroupName -Location $LocationName;
}

#Storage
$StorageAccount = $(Get-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName -ErrorAction SilentlyContinue)
If(!$StorageAccount){
$StorageAccount = New-AzureRmStorageAccount -Name $StorageAccountName -ResourceGroupName $ResourceGroupName -Type Standard_LRS -Location $LocationName;
}
Set-AzureRmCurrentStorageAccount -ResourceGroupName $ResourceGroupName -Name $StorageAccountName;

#Container
$Container = $(Get-AzureStorageContainer -Name $StorageContainerName -ErrorAction SilentlyContinue)
If(!$Container){
New-AzureStorageContainer -Name $StorageContainerName -Permission Off;
}

Step 3. 

#Settings
$PolicyName = "$($StorageContainerName)_accesspolicy";
$AccountKeys = Get-AzureRmStorageAccountKey -ResourceGroupName $ResourceGroupName -Name $StorageAccountName;
$URL = "https://$StorageAccountName.blob.core.windows.net/$StorageContainerName"
$StorageContext = New-AzureStorageContext -StorageAccountName $StorageAccountName -StorageAccountKey $($AccountKeys[0].Value);


#Storage Account Key
Write-Output "CREATE CREDENTIAL c1 WITH IDENTITY = '$StorageAccountName',SECRET = '$($AccountKeys[0].Value)'";
Write-Output "`nBACKUP DATABASE [master] TO URL = '$URL/master1.bak' WITH CREDENTIAL = 'c1';";

#Container SAS
$StartTime = Get-Date;
$EndTime = $StartTime.AddHours(24.0);
$ContainerSASToken = New-AzureStorageContainerSASToken -Context $StorageContext -Container $StorageContainerName -Permission "rwl" -StartTime $StartTime -ExpiryTime $EndTime;
#Write-Output $($ContainerSASToken.Substring(1))
Write-Output "CREATE CREDENTIAL [$URL] WITH IDENTITY='SHARED ACCESS SIGNATURE',SECRET = '$($ContainerSASToken.Substring(1))'";
Write-Output "`nBACKUP DATABASE [master] TO URL = '$URL/master2.bak';";

#Container Policy SAS
$StoredAccessPolicy = $(Get-AzureStorageContainerStoredAccessPolicy -Context $StorageContext -Container $StorageContainerName -Policy $PolicyName -ErrorAction SilentlyContinue)
If(!$StoredAccessPolicy){
$StoredAccessPolicy = New-AzureStorageContainerStoredAccessPolicy -Context $StorageContext -Container $StorageContainerName -Permission "rwl" -ExpiryTime $(Get-Date).AddYears(10) -Policy $PolicyName;
}
$ContainerSASTokenWithPolicy = New-AzureStorageContainerSASToken -Context $StorageContext -Container $StorageContainerName -Policy $PolicyName;

#do not specify the following when using sas from policy. -StartTime -ExpiryTime
#$ContainerSASTokenWithPolicy = $(Get-AzureStorageContainer -Context $StorageContext -Name $StorageContainerName).CloudBlobContainer.GetSharedAccessSignature($null,$PolicyName).Substring(1);
#Write-Output $($ContainerSASTokenWithPolicy.Substring(1));
Write-Output "CREATE CREDENTIAL [$URL] WITH IDENTITY='SHARED ACCESS SIGNATURE',SECRET = '$($ContainerSASTokenWithPolicy.Substring(1))'";
Write-Output "`nBACKUP DATABASE [master] TO URL = '$URL/master3.bak';";