Use Automation RunAs service principal to connect

Question

TL;DR

In summary the steps are:

1. Use the correct code (the last code in this post)
2. Manually add your app id in SSMS as either a server administrator or a database administrator

and then you can process an Azure Analysis Services cube from an Azure Automation Account without needing to create another seperate service account

Actual Question:

I am trying to process an Azure Analysis Services cube using the Azure Automation RunAs Service Principal. This is run within an Azure automation account

This code

#Get the existing AzureRunAsConnection connection
$Conn = Get-AutomationConnection -Name AzureRunAsConnection

# Login with service principal account
Login-AzureRMAccount 
    -ServicePrincipal 
    -Tenant $Conn.TenantID 
    -ApplicationId $Conn.ApplicationID 
    -CertificateThumbprint $Conn.CertificateThumbprint

# Process cube
Invoke-ProcessASDatabase -databasename "DB" -server "Server" -RefreshType "Full"

Results in

Authentication failed: User ID and Password are required when user interface is not available.

My understanding is that when you create an Azure Automation Account, it also creates a 'RunAs' account, which in turn creates a service principal account. (Although the doco seems a bit light on)

I have tried finding this principal account in Azure AD and adding it to SSAS Admins in the Azure portal, but I can't find the actual account. Do service principals actually appear as accounts?

The code below works fine, but it uses a pre saved credential but I don't want to have to manage yet another account.

# Purpose: Run a full process the SSAS cube
$AzureCred = Get-AutomationPSCredential -Name "MyCredential"

Add-AzureRmAccount -Credential $AzureCred | Out-Null
Invoke-ProcessASDatabase -databasename "MyDB" -server "MyServer" -RefreshType "Full" -Credential $AzureCred 

Update 1

I have also tried manually adding these in the SSMS membership area (after looking up the guids in the RunAs Account area in the automation account):

app:applicationid@tenantid

obj:serviceprincipalobjectid@tenantid

and I get the same error message.

I also ran the script with a non-admin user and got

The "zzz" database does not exist on the server.

So it would appear my issue is authentication, not authorisation. In other words it's not that I don't access, it's that I can't log in. I'm thinking based on that error, that -credential is not optional when calling Invoke-ProcessAsDatabase against Azure Analysis services

Also, I note that for the -credential option, the help says

If no credentials are specified, the default Windows account of the user running the script is assume

Given that Azure Analysis Services appears to only be able to use SQL credentials when connecting to a data source (no kind of AD credentials), I can only surmise that Azure Analysis Services is unable to use any kind of Azure Ad authentication for internal processes.

The annoying thing is that this isn't stated anywhere.

Update 2

So I did not read the link carefully enough. The code that works is mostly on the site posted by both answerers here. To pre authenicate to Azure Analysis Server you need to use Add-AzureAnalysisServicesAccount (The linked blog uses Login-AzureAsAccount but I couldn't get it working and couldn't find doco). You need to install powershell module "Azure.AnalysisServices" to use this.

$Conn = Get-AutomationConnection -Name AzureRunAsConnection
Add-AzureAnalysisServicesAccount -RolloutEnvironment "australiasoutheast.asazure.windows.net" -ServicePrincipal -Tenant $Conn.TenantID -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint
Invoke-ProcessASDatabase -databasename "MYDB" -server "MyServerEndpoint" -RefreshType "Full"

You can use the RunAs account with this, and afterwards you don't need to use -credential

So.. this actually works and logs in without needing a seperate credential, but now it doesn't have access to the database. Instead of a login error, I get

The "ZZZZ" database does not exist on the server.

I would appear that the RunAs account has access to the server but not the database and I can't work out to find it to give it access.

Update 3:

This is a more direct guide on how to give the app access to the model so it can be built:

Azure analysis service connection using Service principal not working

Note you can't add in the Azure portal as it won't find it. Add it "manually" in SSMS and it will work, and it will also appear in the Azure Portal as an admin

It all works now.

Update 4:

This has become a handy spot to store my discoveries around authenticating through MSI

Although this question is solved, no I want to connect to SQL Azure from something else using MSI security. No connection string supports this - none of the authentication methods in any connection string support MSI authentication. I did find this interesting link which implies you can create a connection string that supports authentication as MSI:

https://docs.microsoft.com/en-us/azure/app-service/app-service-web-tutorial-connect-msi

The bit of code of interest is:

az webapp config connection-string set 
--resource-group myResourceGroup 
--name <app name> 
--settings MyDbConnection='Server=tcp:<server_name>.database.windows.net,1433;Database=<db_name>;' 
--connection-string-type SQLAzure

I can't find any reference to the parameter --connection-string-type. But it looks like you simply exclude the authentication piece altogether.

Answer 1

Per the official documentation:

Once the service principal is created, its application ID can be assigned permissions in the Azure Analysis Services server or model roles using the following syntax. The example below adds a service principal to the server administrators group in SSMS.

I didn't see the use of Run-as option, i'd recommend checking this blog

It also contains information about storing credentials in azure automation, this will help you in not hard writing credentials in the code.

Answer 2

In your example 1, it seems not your login Azure Login-AzureRMAccount get the error log. Based on my knowledge, Invoke-ProcessASDatabase is not a Azure Power Shell cmdlet. In fact, you no need to login your Azure subscription. Only Invoke-ProcessASDatabase -databasename "MyDB" -server "MyServer" -RefreshType "Full" -Credential $AzureCred should works for you.

Yes if I supply a credential it works, but I want to use the RunAs credential. If I can't, then what is the point of it.

RunAs credential only works for login your Azure subscription, it does not stores credential for your SQL. In your scenario, you could store your SQL credential in runbook PSCredential, like your example2. In fact, in your example, you could remove Add-AzureRmAccount -Credential $AzureCred | Out-Null.

Update:

You should use following script in runbook.

$Conn = Get-AutomationConnection -Name AzureRunAsConnection 
Login-AzureASAccount -RolloutEnvironment "southcentralus.asazure.windows.net" -ServicePrincipal -ApplicationId $Conn.ApplicationID -CertificateThumbprint $Conn.CertificateThumbprint -TenantId $Conn.TenantID 
Invoke-ProcessTable -Server "asazure://southcentralus.asazure.windows.net/myserver" -TableName "MyTable" -Database "MyDb" -RefreshType "Full"

More information about this please check this blog.