Automating download of SQL backups stored on Azure via powershell

Question

I'm trying to create a script that will download SQL backups from Azure before using docker to build containers. I created a service principal using these instructions: https://docs.microsoft.com/en-us/powershell/azure/create-azure-service-principal-azureps?view=azps-2.4.0

I'm using the following code to try to start the downloads but it fails with the following error:

$tenantID = '6ed674z5-my tenant ID-802730b05737'
$passwd = ConvertTo-SecureString 'x43my long passwordR69' -AsPlainText -Force
$pscredential = New-Object System.Management.Automation.PSCredential('079054cd-my application ID-0b19d8ar6e77', $passwd)
Connect-AzAccount -ServicePrincipal -Credential $pscredential -TenantId $tenantId

$containerName = "mycontainer"
$location = "westus2"
$resourceGroup = "myresourcegroup"
$storageAccount = Get-AzStorageAccount
$ctx = $storageAccount.Context
Get-AzStorageBlob -Container $ContainerName -Context $ctx |  Get-AzStorageblobcontent -Destination ".\dbase\backups" -Force

Error:

Get-AzStorageAccount : 'this.Client.SubscriptionId' cannot be null.
At C:\dev\thcguard\launch.ps1:9 char:19
+ $storageAccount = Get-AzStorageAccount
+                   ~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : CloseError: (:) [Get-AzStorageAccount], ValidationException
    + FullyQualifiedErrorId : Microsoft.Azure.Commands.Management.Storage.GetAzureStorageAccountCommand

Where am I going wrong? If I just use Connect-AzAccount and manually log in the download code works.

I looked at the storage blob and it says Authentication method: Access key (Switch to Azure AD User Account). I have a hunch that one way to solve this might be switching to AD user account, but I need the access key method to do the SQL backups in the first place... — Daemach
I don't know how to dump the contents of a variable but since it's set using Get-AzStorageAccount it's likely just an error. This code all works if I log in manually using just Connect-AzAccount — Daemach
Add an extra line after your script that is just: $storageAccount — Skuld
After the Connect-AzAccount line, Add this line Set-AzContext -SubscriptionId "my azure subscription id" -TenantId "my tenant id" — Thomas

Thomas Thomas · Accepted Answer · 2019-07-17T18:39:50

Service principals don't have a default subscription. While connecting using a user account, Connect-AzAccount fectches the default subscription. In your case, you need to specify the subscription you want to connect to.

You can adjust your code like that:

$tenantId = "{my-tenant-id}"
$subscriptionId = "{my-subscription-id}"
$applicationId = "{my-application-id}"
$password = ConvertTo-SecureString "{my-password}" -AsPlainText -Force
$psCredential = New-Object System.Management.Automation.PSCredential($applicationId, $password)
Connect-AzAccount -ServicePrincipal -Credential $psCredential -TenantId $tenantId -SubscriptionId $subscriptionId

Make sure your service principal can access your storage account. You can assign permission to the storage account, using the Access control (IAM) blade of the storage account:

Automating download of SQL backups stored on Azure via powershell

1 Answers