Migrating azure runbook to use system identity running azure analysis services tsml queries

Question

We have an azure runbook that is using RunAs accounts, including Classic Run As accounts, that will soon be retired. A quick search I found this nice article "https://www.thelazyadministrator.com/2023/03/09/migrate-your-runbooks-in-azure-automation-to-managed-identities/" that guided me migrating to System identity. That worked nice but the powershell code is also running azure analysis service code to run TSML script to refresh the data model. This runbook is being run every 20 minutes to add new data to the cube/data model.

Below I have added parts of the powershell code. Up till now, I used service principal credentials with clientid, tenantid and CertificateThumbprint. For authentication, this has been replaced by System identity. That was the easy aport

But these ids have also been used by azure analysis services. I need to rewrite these code lines. I have read some place that I can change applicationid to objectid from System identity. But I have not tried this out yet. I think I need to replace the app:@ with app:@. Hope any of you have more to add and guide me through this.

regards Geir

========================== need to change this to use system identity here too ==== $amoAzureASServer.Connect("Data Source=$AzureAsServerFullName;User ID=app:" + $ApplicationId + "@" + $TenantId + ";Provider=MSOLAP;Persist Security Info=True;Impersonation Level=Impersonate;Password=cert:" + $CertificateThumbprint)

=========================

======== the powershell code ====

param
(
    [Parameter (Mandatory = $false)]
    [object] $WebhookData ,

    [Parameter (Mandatory = $false)]
    [string] $serverName="aas****",

    [Parameter (Mandatory = $false)]
    [string] $resourceGroupName = "RSG-****",

    [Parameter (Mandatory = $false)]
    [string] $CubeDatabaseName="cube",

    [Parameter (Mandatory = $false)]
    [string] $CubeTableName="cubetable",

    [Parameter (Mandatory = $false)]
    [string] $CubePartitionName="cube_table_daily"
)

$ErrorActionPreference = "Continue";

$ApplicationId="********************"

$TenantId="************************"

$CertificateThumbprint="***********************" //should be taken away

$objectId = "*****************"

$SubscriptionId="************************"

[int]$startMs = (Get-Date).Millisecond

# New authentication code, works
try 
{
    "Logging in to Azure"
    Connect-AzAccount -Identity
}
catch
{
    Write-Error -Message $_.Exception
}


if($WebhookData){
    $parameters=(ConvertFrom-Json -InputObject $WebhookData.RequestBody) 

    # Get single parameter from the set of parameters
    $serverName = $Parameters.AnalysisServicesServerName
    $resourceGroupName = $Parameters.ResourceGroupName
    $CubeDatabaseName = $Parameters.DatabaseName
    $CubeTableName = $Parameters.TableName
    $CubePartitionName = $Parameters.PartitionName
    #$PartitionProcessOperation = $Parameters.PartitionProcessOperation

    # Write the value of the parameter 
    #Write-Output "CredentialName: $($parameters.CredentialName)"
    Write-Output "AnalysisServicesServerName: $($parameters.AnalysisServicesServerName)"
    Write-Output "ResourceGroupName: $($parameters.ResourceGroupName)"
    Write-Output "CubeDatabaseName: $($parameters.DatabaseName)"
    Write-Output "CubeTableName: $($parameters.TableName)"
    Write-Output "CubePartitionName: $($parameters.PartitionName)"

    if($parameters.callBackUri) {$callBackUri=$parameters.callBackUri}

    $ErrorMessage = "" 
}    

$asServer = Get-AzAnalysisServicesServer  -ResourceGroupName $resourceGroupName -Name $serverName

"Current Azure AS status: $($asServer.State)"

if ($asServer.State -eq "Paused")
{
    $asServer | Resume-AzAnalysisServicesServer  -Verbose
}

if ($asServer.FirewallConfig -ne $null)
{
    for ($i = 0; $i -lt $asServer.FirewallConfig.FirewallRules.Count; $i++)
    {
        $rule = $asServer.FirewallConfig.FirewallRules[$i];
        if ($rule.FirewallRuleName -eq "AzureAutomation")
        {
            $asServer.FirewallConfig.FirewallRules.Remove($rule);
            $i--;
        }
    }

    #backup the firewall rules
    $rulesBackup = $asServer.FirewallConfig.FirewallRules.ToArray()

    $ipinfo = Invoke-RestMethod http://ipinfo.io/json

    #add a new AzureAutomation firewall rule
    $newRule = New-AzAnalysisServicesFirewallRule  -FirewallRuleName "AzureAutomation" -RangeStart $ipinfo.ip  -RangeEnd $ipinfo.ip
    $asServer.FirewallConfig.FirewallRules.Add($newRule)
    Set-AzAnalysisServicesServer  -ResourceGroupName $resourceGroupName -Name $serverName -FirewallConfig $asServer.FirewallConfig

    "Updated Azure AS firewall to allow current Azure Automation Public IP: " + $ipinfo.ip
}
else
{
    "Azure AS Firewall is off"
}

function InstallAndLoadTOM {
    $null = Register-PackageSource -Name nuget.org -Location http://www.nuget.org/api/v2 -Force -Trusted -ProviderName NuGet;
    $install = Install-Package Microsoft.AnalysisServices.retail.amd64 -ProviderName NuGet -SkipDependencies;
    if ($install.Payload.Directories -ne $null)
    {
        $dllFolder = $install.Payload.Directories[0].Location + "\" + $install.Payload.Directories[0].Name + "\lib\net45\"
        Add-Type -Path ($dllFolder + "Microsoft.AnalysisServices.Core.dll")
        Add-Type -Path ($dllFolder + "Microsoft.AnalysisServices.Tabular.Json.dll")
        Add-Type -Path ($dllFolder + "Microsoft.AnalysisServices.Tabular.dll")
        $amoAzureASServer = New-Object -TypeName Microsoft.AnalysisServices.Tabular.Server 
        "Loaded Tabular Object Model assemblies"
    }
}

InstallAndLoadTOM

[string]$AzureAsServerFullName = $asServer.ServerFullName;
        
$amoAzureASServer = New-Object -TypeName Microsoft.AnalysisServices.Tabular.Server 
$amoAzureASServer.Connect("Data Source=$AzureAsServerFullName;User ID=app:" + $ApplicationId + "@" + $TenantId + ";Provider=MSOLAP;Persist Security Info=True;Impersonation Level=Impersonate;Password=cert:" + $CertificateThumbprint)
"Connected to Azure AS"

$tmsl = '{
  "refresh": {
    "type": "clearValues",
    "objects": [
      {
        "database": "' + $CubeDatabaseName + '",
        "table": "' + $CubeTableName + '", 
        "partition": "' + $CubePartitionName + '"
      }
    ]
  }
}'
"$tmsl"

$results = $amoAzureASServer.Execute($tmsl);
foreach ($message in $results.Messages)
{
    if ($message.GetType().FullName -eq "Microsoft.AnalysisServices.XmlaError")
    {
        throw $message.Description
    }
    if ($message.GetType().FullName -eq "Microsoft.AnalysisServices.XmlaWarning")
    {
        "Warning $($message.Description)"
    }
}
"Done process full of database"

#restore old firewall config
if ($asServer.FirewallConfig -ne $null)
{
    #reset firewall to the state it was in before this script started
    $asServer.FirewallConfig.FirewallRules.Clear()
    $asServer.FirewallConfig.FirewallRules.AddRange($rulesBackup)
    Set-AzAnalysisServicesServer -ResourceGroupName $resourceGroupName -Name $serverName -FirewallConfig $asServer.FirewallConfig
    "Reset Azure AS firewall rules"
}

# If called from a webhook, use callBackUri to notify task is complete
If ($callBackUri)
{
    $Body = @{
        StatusCode = "200"
    }
    Write-Output "Invoke call back to ADF with status 200.."
    #Invoke-RestMethod -Method Post -Uri $CallBackUri -Body $Body
    Invoke-WebRequest -Uri $callBackUri -Method Post -Body $Body
}

#END TIMER
[int]$endMs = (Get-Date).Millisecond

# Calculate elapsed time
Write-Output "Automation Job Done. Elapsed time was $($startMs - $endMs )"