Compartilhar via


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.

 

image

 

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.

image

 

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
    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.