Azure Automation – Run As Account migration plan

If you’re utilizing an Azure Automation account in your data project, you’ve likely come across this information:

Azure Automation Run As Account will retire on 30 September 2023

If not, now you’re aware of it. If you’re using it in your code, it will cease to function. Microsoft has provided an excellent article outlining migration steps, accessible at the following link:

https://learn.microsoft.com/en-us/azure/automation/migrate-run-as-accounts-managed-identity

Unfortunately, surprises can arise when dealing with specific resources. In my projects, I utilize it for:

Refreshing Azure Analysis Services Tables/Partitions.
Scaling Azure SQL DB.
Performing various Azure SQL DB operations or Storage Account operations.

In this article, I aim to share with you:

How to change authorization to Managed Identity.
Adjustments to specific parts of the code, primarily focusing on authorization.
Insights into the authorization flow in Azure.
Permission requirements for Azure Resources.

LET’S GO!

TLDR (permissions/authorization/authorization object)

And below long and boring 😉 version:

Authorization principles (in Azure)

Our primary preference for authorization is to employ Managed Identity (User or System) in all applicable scenarios. If this isn’t feasible, the subsequent option involves using an Azure AD Service Principal (App Registration) with a secret or certificate.

The sole instance where a service principal is necessary is in Azure Analysis Services Database operations (e.g., Refresh Partition), which currently lack support for Managed Identity.

Managed identities that are added to database or server roles will be unable to login to the service or do any operations.

Managed identities for service principals are not supported in Azure Analysis Services.

(SOURCE) – managed identities on database or server roles

In this article, our emphasis will be on User Managed Identity (UMI), as, in my opinion, it offers more benefits than System Identity in my specific case.

Prerequsites:

MANAGED IDENTITY

To create a User Managed Identity (UMI) or enable System Identity, follow these steps:

Log in to the Azure portal and create a new resource.
Search for “managed identity” and select “User Assigned Managed Identity,” then click CREATE.
Provide the following information:
- Subscription, Resource Group, and Azure Region of the Managed Identity.
- Identity Name: I prefer following naming conventions, such as <project>-<environment>-<purpose>-umie, e.g., demo-dev-automation-umi.
After creating the Managed Identity, assign it to your Automation Account:
- Navigate to your Automation Account resource.
- Search for “identity.”

Switch to the “User Assigned” tab and click the “+ ADD” button.
Select the User Managed Identity (UMI) you have created and confirm by clicking “ADD.”
NOTE: It may take up to a few minutes for the UMI to be successfully assigned to the Automation Account.

Access the Managed Identity Resource and make note of your Client ID:

Within your Resource Group, locate the Managed Identity Resource that you can access. Clicking on it will take you to the following page:

Take note of the Client ID, as you will need to use it in your code later. Additionally, the Tenant ID might be helpful for reference.

Connect-AzAccount -Identity -AccountId <yout_Cilend_ID>

Service Principal (app registration)

The AAD Admin should create an app registration with a secret and share the following information with you. Alternatively, they can save it directly to Key Vault.

app ID
Tenant ID
Secret Value (that needs to be rotated periodically!)

More information you can find in the official documentation:

Quickstart: Register an application with the Microsoft identity platform

Authorization Flow

Authorization for Azure Analysis Services (database operations)

Pre-requirements:

To add the Service Principal (app ID) created by your administrator as an Analysis Services Admin, follow these steps:

Log in to the Analysis Services (AAS) Server, for example, via SQL Server Management Studio (SSMS), as an Administrator.
Add the Service Principal as a server administrator by either:
- Finding it on Azure AD by name.
- Manually inserting it following a specific pattern.

app: <AppID>@<tenantID>

It will look like the screenshot below in Azure Analysis Services (the Azure portal).

3. Grant the RBAC Role “Key Vault Secrets User” (or Reader) access to the Managed Identity of the Automation Account on Azure Key Vault. If granting access at this level is too permissive, consider making a grant on individual secrets.

Flow diagram:

Point 1) was completed in PreReq

For the second step, you have two options to save the mentioned app ID and secret:

Key Vault (preferred option)
Automation Account Credentials

Now, for the third step, you’ll need to authenticate using PowerShell to Azure with User Managed Identity (UMI) and connect to Key Vault. If you have any specific questions or if you’d like assistance with the PowerShell script, feel free to provide more details!

Connect-AzAccount -Identity -AccountId $UmiClientID

Where $UmiClientID is your Managed Identity Client ID

4) Retrieve the app ID and secret from Key Vault using Managed Identity and store them as PSCredential object.

$appId = Get-AzKeyVaultSecret -VaultName $KVaultName -Name $KVAppID -AsPlainText
$appSecret = Get-AzKeyVaultSecret -VaultName $KVaultName -Name $KVAppSecret -AsPlainText

Where $KVaultName is a variable that refers to your Key Vault name and $KVAppID are variables that refer to Key Vault entry names.

Store App ID and Secret as PSCredentails

$appSecret   = ConvertTo-SecureString -String $appSecret -AsPlainText -Force
$_Credential = New-Object System.Management.Automation.PSCredential $appId, $appSecret

5) Refresh Partitions / Tables using Invoke-ASCmd

Invoke-ASCmd `
     -Database $CatalogAAS `
     -Server $ServerAASAdress `
     -ServicePrincipal `
     -Credential $_Credential `
     -Query $queryProcess

Where

$CatalogAAS – is AAS Database Name (e.g. MyDatabse)
$ServerAASAdress – is Full AAS server Address (e.g. asazure://westeurope.asazure.windows.net/devtabular01)
$_Credential – credentials defined in point 4)
$queryProcess – is query (JSON) that contains refresh information

Authorization for Azure Analysis Services (Service Operation)

For operations on the control plane, such as scaling Azure Analysis Services, the process is simpler. Instead of requiring a Service Principal, you need to grant the RBAC role on the Azure Access Control blade. The role options include:

Contributor (built-in role)
Custom role with required AAS privileges, such as:
- Microsoft.AnalysisServices/servers/read
- Microsoft.AnalysisServices/servers/write
- Microsoft.AnalysisServices/servers/suspend/action

For more information on creating custom roles, you can refer to the documentation provided:

Azure custom roles

Azure resource provider operations

Diagram Flow:

We log in to Azure using Managed Identity:

Connect-AzAccount -Identity -AccountId $UmiClientID

We execute scaling tabular:

Set-AzAnalysisServicesServer -Name $analysisServerName -ResourceGroupName $resourceGroupName -Sku $sku

Where the variables refer to:

$analysisServerName: name of tabular (last part after /, not full server uri) e.g. “demotabular“
$resourceGroupName: tabular resource group name e.g. “DEMO-DEV-AAC-RG“
Sku: Sku from pricing tier e.g. “S2”

NOTE: Remember that the context of execution needs to be the tenant and subscription of your AAS service.

Authorization for Azure SQL Server

Authorization for managed identity is supported in SQL Server, but we must take an additional step if we want to use Invoke-Sqlcmd => get a token for Managed Identity for an Azure SQL Server resource.

Pre-requiremnts:

You have to create USER in SQL database and grant privileges (or role) to perform the necessary actions.

Example:

CREATE USER [my-user-managed-identity-name] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [my-user-managed-identity-name];

That will allow managed identity to log in to the SQL database and perform any operation that is allowed by role [db_datareader].

Flow diagram:

We need to retrieve a token from Azure AD using the following code:

$resource= "?resource=https://database.windows.net/"
$client_id="&client_id=$UmiClientID"
$url = $env:IDENTITY_ENDPOINT + $resource + $client_id 
$Headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"  
$Headers.Add("Metadata", "True")
$headers.Add("X-IDENTITY-HEADER", $env:IDENTITY_HEADER) 
$accessToken = Invoke-RestMethod -Uri $url -Method 'GET' -Headers $Headers

Where $UmiClientID is our Mnaged Identity Client ID.

We execute queries using Invoke-Sqlcmd, passing an access token:

$serverId = Invoke-Sqlcmd `
                       -ServerInstance $SQLServerName `
                       -Database $SQLDBName `
                       -AccessToken $accessToken.access_token `
                       -Query $querySQLaboutInstance

Where $SQLServerName is name of Azure SQl Server Name $SQLDBName is SQL Database Name and $Query is query we want to execute on SQL Server e.g. :

SELECT MAX(exec_time) from tech.audit_execution

Environment Separation

I’ve configured a single automation account with three User Managed Identities (UMIs). In my code, I pass the $Environment variable during execution, dynamically assigning the UMI Client ID based on the provided parameter value

if ($Environment -eq "DEV")
  {
      $UmiClientID = "xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
  }
  elseif ($Environment -eq "UAT")
  {
      $UmiClientID = "yyyyyyyy-yyyy-yyyy-yyyy-yyyyyyyyyyyy"
  }
  elseif ($Environment -eq "PROD")
  {
      $UmiClientID = "zzzzzzzz-zzzz-zzzz-zzzz-zzzzzzzzzzzz"
  }

Each managed identity is housed within its respective resource group, and each has its own set of permissions at the resource group level (e.g., Blob Reader, Contributor, etc.). My strategy involves creating a custom role tailored to the specific minimum permissions needed.

For each environment, there exists a dedicated Service Principal, and the corresponding secrets are securely stored in the environment’s key vault.

Diagram: