Simplifying creation of SQL Credentials with Shared Access Signature ( SAS ) tokens on Azure Storage with Powershell
In SQL Server 2014 a new feature was introduced that enables SQL Server Data files to be stored on Azure Storage. For more details see SQL Server Data Files in Windows Azure and Tutorial: SQL Server Data Files in Windows Azure Storage service
In order to create a database with files on Azure Blob storage, you will need to create one or more credentials. To create a credential you will need to create a shared access policy and then generate a SAS token (Create and Use a Shared Access Signature) on that policy. The manual generation of this can be cumbersome in particular if you want to create a database with many files on several containers. To facilitate this, the attached PowerShell script enables you to specify a container name and the number of containers to create and it will create those containers and generate the ‘create credential’ statements required into a text file. If you already have the container(s) created, it will just generate the ‘create credential’ statement(s).
Prerequisites:
- Install Azure PowerShell
- Run the cmdlet Get-AzurePublishSettingsFile or go to https://manage.windowsazure.com/publishsettings to download certificate information . If you already have your AzurePublishSettingsFile imported in PowerShell you can skip this part and the parameter from the command line example shown below.
- If you do not have a container or multiple containers created, the script will create them for you. If you already have a container created, just provide the name and number of containers. Follow nomenclature in the example below.
PowerShell script parameters:
PublishSettings File: Path to file saved after running Get-AzurePublishSettingsFile (Optional)
SubscriptionName : Specifies the name of the storage account to be connected.
StorageAccountName : Specifies the name of the storage account to be connected.
ContainerName : Specifies the name of container.
NumContainers : Number of containers which uses the creates a container with the name followed by a number ( Example : sqlcontainer1, sqlcontainer2 ), if the container exists it will only apply the policy and permissions.
StoredAccessPolicy : Specifies one or more specified name of stored access policy. If the policy exists, it will clear existing permissions and reset the permissions to Read/Write/List/Delete.
StartTime : Specifies Start Time for the StoredAccessPolicy applied to the container.
ExpiryTime : Specifies Expiry Time for the StoredAccessPolicy applied to the container.
LogFilePath : Specifies the path to the log file which logs the Create Credential Statements to use with SQL Server
Example:
.\SASPolicyTokens.ps1 -PublishSettingsFile "d:\temp\AzureSettings.publishsettings" -SubscriptionName "MyAzureAccount" -StorageAccountName "denzilrstorage1" -ContainerName "sqlcontainer" -NumContainers 2 -StoredAccessPolicy "SQLPolicy" -StartTime "3/1/2015" -ExpiryTime "3/1/2016" -LogFilePath "D:\Temp\creds.txt"
Output file contents:
The output file generated by –LogFilePath parameter will have a TSQL script. Open the script in SSMS and run the commands to create your credentials without modification.
Once you run the script and create the credentials above you can now test the database creation.
USE [master]
GO
CREATE DATABASE [SQLDB_XI]
CONTAINMENT = NONE
ON PRIMARY
(NAME = N'SQLDB_XI_data', FILENAME = N'https://denzilrstorage1.blob.core.windows.net/sqlcontainer1/TestDB1Data.mdf')
LOG ON
(NAME = N'SQLDB_XI_log', FILENAME = N'https://denzilrstorage1.blob.core.windows.net/sqlcontainer2/TestDB1Log.ldf')
GO
Note: This method works on normal Azure Storage. Azure Premium Storage for storing database files using SQL Server 2014 is not yet supported and thus was not tested.
Looking at the Azure Portal under your Storage account, you should see 2 containers and the files that the database was created on.
PowerShell Script:
#---------------------------------------------------------------------------------
#The sample scripts are not supported under any Microsoft standard support program or service. The sample scripts are provided AS IS without warranty
#of any kind. Microsoft further disclaims all implied warranties including, without limitation, any implied warranties of merchantability or of fitness for
#a particular purpose. The entire risk arising out of the use or performance of the sample scripts and documentation remains with you. In no event shall
#Microsoft, its authors, or anyone else involved in the creation, production, or delivery of the scripts be liable for any damages whatsoever (including,
#without limitation, damages for loss of business profits, business interruption,loss of business information, or other pecuniary loss) arising out of the use
#of or inability to use the sample scripts or documentation, even if Microsoft has been advised of the possibility of such damages
#---------------------------------------------------------------------------------
#requires -Version 3.0
<#
.SYNOPSIS
This script can be create stored access policy of a container in Windows Azure and generates a Create Credentials Statement for SQL to use.
.PARAMETER PublishSettingsFile
Specifies the PublishSettings FIle downloaded after running Get-AzurePublishSettingsFile.
.PARAMETER SubscriptionName
Specifies the Azure Subscription Name.
.PARAMETER StorageAccountName
Specifies the name of the storage account to be connected.
.PARAMETER ContainerName
Specifies the name of container.
.PARAMETER NumContainers
Number of containers which uses the Container Name followed by a number ( Example : Sqlcontainer1, SqlContainer2 )
.PARAMETER StoredAccessPolicy
Specifies one or more specified name of stored access policy.
.PARAMETER StartTime
Specifies Start Time for the StoredAccessPolicy applied to the container
.PARAMETER ExpiryTime
Specifies Expiriy Time for the StoredAccessPolicy applied to the container
.PARAMETER LogFilePath
Specifies the path to the log file which logs the Create Credential Statements.
.EXAMPLE
.\SASPolicyTokens.ps1 -PublishSettingsFile "c:\temp\AzureSettings.publishsettings" -SubscriptionName "AzureAcct" -StorageAccountName "denzilrstorage1" -ContainerName "sqlcontainer" -NumContainers 2 -StoredAccessPolicy "TestPolicy" -StartTime "1/1/2015" -ExpiryTime "1/1/2016" -LogFilePath "D:\Temp\creds.txt"
#>
# Run Get-AzurePublishSettings to save the PublishSettings file
# or go to https://manage.windowsazure.com/publishsettings
Param
(
[Parameter(Mandatory=$false)]
[String]$PublishSettingsFile,
[Parameter(Mandatory=$true)]
[String]$SubscriptionName,
[Parameter(Mandatory=$true)]
[String]$StorageAccountName,
[Parameter(Mandatory=$true)]
[String]$ContainerName,
[Parameter(Mandatory=$true)]
[int]$NumContainers,
[Parameter(Mandatory=$true)]
[String]$StoredAccessPolicy,
[Parameter(Mandatory=$true)]
[DateTime]$StartTime,
[Parameter(Mandatory=$true)]
[DateTime]$ExpiryTime,
[Parameter(Mandatory=$false)]
[String]$LogFilePath
)
If((Get-Module -Name Azure) -eq $null)
{
Import-Module Azure
}
#Check if Windows Azure PowerShell Module is avaliable
If((Get-Module -Name Azure) -eq $null)
{
Write-Warning "Install Windows Azure Powershell from https://www.windowsazure.com/en-us/downloads/#cmd-line-tools"
}
Else
{
if ($LogFilePath)
{
If (Test-Path $LogFilePath)
{
Remove-Item $LogFilePath
}
}
if ($PublishSettingsFile)
{
Import-AzurePublishSettingsFile $PublishSettingsFile -ErrorAction SilentlyContinue -ErrorVariable IsPublisSettingsFileExist | Out-Null
#Check existance
If($IsPublisSettingsFileExist.Exception -ne $null)
{
Write-Host "Run cmdlet Get-AzurePublishSettingsFile to download settings file or enter correct path to parameter PublishSettingsFile: $PublishSettingsFile"
Write-Host $IsPublisSettingsFileExist
exit $LASTEXITCODE
}
}
Select-AzureSubscription $SubscriptionName -ErrorAction SilentlyContinue -ErrorVariable IsSubscriptionExist | Out-Null
If($IsSubscriptionExist.Exception -ne $null)
{
Write-Host "Incorrect Subscription entered: $SubscriptionName"
Write-Host $IsSubscriptionExist
exit $LASTEXITCODE
}
Get-AzureStorageAccount -StorageAccountName $StorageAccountName -ErrorAction SilentlyContinue -ErrorVariable IsStorageExists | Out-Null
#Check existance of storage account
If($IsStorageExists.Exception -ne $null)
{
Write-Host "Invalid Storage account: " $IsStorageExists
exit $LASTEXITCODE
}
$StorageAccountKey = (Get-AzureStorageKey -StorageAccountName $StorageAccountName).Primary
$Creds = New-Object Microsoft.WindowsAzure.Storage.Auth.StorageCredentials("$StorageAccountName","$StorageAccountKey")
$CloudStorageAccount = New-Object Microsoft.WindowsAzure.Storage.CloudStorageAccount($creds, $true)
$CloudBlobClient = $CloudStorageAccount.CreateCloudBlobClient()
For ($i=1; $i -le $NumContainers; $i++)
{
Write-Verbose "Getting the container object named $ContainerName."
$NewContainer = $ContainerName + [string] $i
$BlobContainer = $CloudBlobClient.GetContainerReference($NewContainer)
$ContainerCreated = $BlobContainer.CreateIfNotExists();
#Create an access policy instance
$SharedAccessBlobPolicy = New-Object Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPolicy
#Sets start time and expiry time for access policy
$SharedAccessBlobPolicy.SharedAccessStartTime = $StartTime
$SharedAccessBlobPolicy.SharedAccessExpiryTime = $ExpiryTime
$PermissionValue = 0
$PermissionValue += [Int][Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPermissions]::Read
$PermissionValue += [Int][Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPermissions]::Write
$PermissionValue += [Int][Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPermissions]::List
$PermissionValue += [Int][Microsoft.WindowsAzure.Storage.Blob.SharedAccessBlobPermissions]::Delete
#Sets permission of stored access policy
$SharedAccessBlobPolicy.Permissions = $PermissionValue
$ContainerPermission = $BlobContainer.GetPermissions()
Write-Verbose "--Create a stored access policy '$StoredAccessPolicy'."
$ContainerPermission.SharedAccessPolicies.Clear()
$ContainerPermission.SharedAccessPolicies.Add($StoredAccessPolicy,$SharedAccessBlobPolicy)
$ContainerPermission.PublicAccess = [Microsoft.WindowsAzure.Storage.Blob.BlobContainerPublicAccessType]::Off
$BlobContainer.SetPermissions($ContainerPermission)
Write-Verbose "Getting Shared Access Signature."
$SasContainerToken = $BlobContainer.GetSharedAccessSignature($null,$StoredAccessPolicy);
$token = $SasContainerToken.SubString(1)
[string] $CreateCredentialString = "Create Credential [" + [string] $BlobContainer.Uri +"] With identity='Shared Access Signature',SECRET = '$token'"
Write-Host $CreateCredentialString
Write-Host "GO"
if($LogFilePath)
{
Write-Output $CreateCredentialString | Out-File $LogFilePath -Append
Write-Output "GO" | Out-File $LogFilePath -Append
}
}
}
Denzil Ribeiro
Program Manager SQL/Azure CAT
Comments
Anonymous
August 16, 2015
Thanks for the handy PS Script! This worked really well. One thing to note. I don't know if this was intended or not. I created a container manually in my environment called 'backup' When I set the NumContainers parameter to 1 it appended the #1 to the container, so I had created Backup, this tool created Backup1.Anonymous
August 22, 2015
Josh, This simplifies creation SAS credentials for multiple containers, so creates multiple containers and associated credentials but if you want a single container, you can modify the powershell script easily, don't have to loop through etc.Anonymous
August 18, 2016
Does this work for Managed backups on sql2014? It creates it own container.I saw it was redesigned for 2016.- Anonymous
August 22, 2016
This should apply to 2014 as well as it requires a credential.
- Anonymous
Anonymous
January 04, 2017
This is great an all, but doesn't work with Storage Accounts as part of a Resource Group. If you can update the sample, that might help. The Get-AzureRMStorageAccount is a simple enough change, but not sure how to get past the Get-AzureSTorageKey which seems to not work either.