I'm trying to create an Azure Automation Runbook to write messages to an existing Azure Storage Queue using information from a SQL Query. The below works perfectly in Powershell ISE on my Windows 10 machine, but I'm getting an error when testing it in Azure the Automation.
The script is:
Connect-AzureRmAccount Get-AzureRmSubscription Set-AzureRmContext -SubscriptionId <our subscription id> $resourceGroup = "our resource group" $storageAccountName = "our storage account name" $queueName = "our queue name" $queue = Get-AzureRmStorageQueueQueue -resourceGroup $resourceGroup -storageAccountName $storageAccountName -queueName $queueName $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "our Azure SQL connection string" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = $("SELECT SourceId FROM dbo.batches GROUP BY SourceId HAVING SourceId > 101") $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $Table = new-object system.data.datatable $SqlAdapter.Fill($Table) | out-null $SqlConnection.Close() $compArray = @($Table | select -ExpandProperty SourceId) foreach ($array in $compArray) { Add-AzureRmStorageQueueMessage -queue $queue -message @{"SourceId"=$array;"RetryCount"=0;} } Again, this works perfectly in Powershell on my local machine, but in Azure Automation, I get this error:
Failed Queue <our queue name> could not be retrieved/created from Storage Account <our storage account> on resource group (Queue <our queue name> could not be retrieved/created from Storage Account <our storage account> on resource group ) Set-AzureRmContext : Run Connect-AzureRmAccount to login. At line:3 char:1 + Set-AzureRmContext -SubscriptionId <our subscription id> ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Set-AzureRmContext], PSInvalidOperationException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Profile.SetAzureRMContextCommand Get-AzureRmStorageAccount : No subscription found in the context. Please ensure that the credentials you provided are authorized to access an Azure subscription, then run Connect-AzureRmAccount to login. At C:\Modules\User\AzureRmStorageQueue\AzureRmStorageQueueCoreHelper.psm1:86 char:19 + ... aContext = (Get-AzureRmStorageAccount -ResourceGroupName $resourceGro ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Get-AzureRmStorageAccount], ApplicationException + FullyQualifiedErrorId : Microsoft.Azure.Commands.Management.Storage.GetAzureStorageAccountCommand Get-AzureStorageQueue : Could not get the storage context. Please pass in a storage context or set the current storage context. At C:\Modules\User\AzureRmStorageQueue\AzureRmStorageQueueCoreHelper.psm1:88 char:94 + ... ue]$queue = Get-AzureStorageQueue -Name $queueName -Context $saContex ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [Get-AzureStorageQueue], InvalidOperationException + FullyQualifiedErrorId : InvalidOperationException,Microsoft.WindowsAzure.Commands.Storage.Queue.GetAzureStorageQueueCommand New-AzureStorageQueue : Could not get the storage context. Please pass in a storage context or set the current storage context. At C:\Modules\User\AzureRmStorageQueue\AzureRmStorageQueueCoreHelper.psm1:92 char:95 + ... ue]$queue = New-AzureStorageQueue -Name $queueName -Context $saContex ... + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : CloseError: (:) [New-AzureStorageQueue], InvalidOperationException + FullyQualifiedErrorId : InvalidOperationException,Microsoft.WindowsAzure.Commands.Storage.Queue.NewAzureStorageQueueCommand Could someone assist me in what I'm missing here? (I have confirmed that the AzureRmStorageQueue module is installed in Azure Automation.)
