The information in SQL Server Books Online about how to generate the Shared Access Signature key for SQL Server Managed Backup to Azure is (at the moment) out-of-date and also not very helpful.
https://msdn.microsoft.com/en-us/library/dn449491.aspx
So here's a Powershell script that creates the SAS key, and also will create the Storage Account and Container if they don't exist.
After creating the SAS key, this script will output a TSQL script to configure managed backup to Azure.
$subscriptionName = "My Azure Subscription"
$resourceGroupName = "SQLBackup"
$storageAcctName = "mysqlmanagedbackup"
$containerName= "backups"
$location = "centralus"
$storageSkuName = "Standard_LRS"
Login-AzureRmAccount -SubscriptionName $subscriptionName
$rg = Find-AzureRmResourceGroup | ? Name -eq $resourceGroupName
if ($rg -eq $null)
{
"Creating Resource Group $resourceGroupName"
$rg = New-AzureRmResourceGroup $resourceGroupName -Location $location
}
$sa = Find-AzureRmResource -Name $storageAcctName -ResourceGroupName $resourceGroupName -ResourceType "Microsoft.Storage/storageAccounts"
if ($sa -eq $null)
{
"Creating Storage Account $storageAcctName"
$sa = New-AzureRmStorageAccount -ResourceGroupName $resourceGroupName -Name $storageAcctName -Location $location -SkuName $storageSkuName
}
$storageKey = (Get-AzureRmStorageAccountKey -Name $storageAcctName -ResourceGroupName $resourceGroupName )[0].Value
$context = New-AzureStorageContext -StorageAccountName $storageAcctName -StorageAccountKey $storageKey
if ((get-azurestoragecontainer -context $context | ? Name -eq $containerName) -eq $null)
{
Write-Host "Creating New Storage Container $containerName"
new-azurestoragecontainer -name $containerName -permission container -context $context
}
$fullSasToken = New-AzureStorageContainerSASToken -Name $containerName -Permission rwdl -FullUri -Context $context
$containerUrl = $fullSasToken.Substring(0,$fullSasToken.IndexOf("?"))
$sasToken = $fullSasToken.Substring($fullSasToken.IndexOf("?")+1)
$enableManagedBackupScript = @"
--------------------
---BEGIN TSQL Script
--------------------
CREATE CREDENTIAL [$containerUrl]
WITH IDENTITY = 'Shared Access Signature',
SECRET = '$sasToken'
GO
EXEC msdb.managed_backup.sp_backup_config_basic
@enable_backup = 1,
@database_name = null,
@container_url = '$containerUrl',
@retention_days = 30
--------------------
---END TSQL Script
--------------------
"@
write-host $enableManagedBackupScript