How to refresh powerBI XMLA endpoint dataset using with system managed Identity?

Question

In azure automation account we used below powershell script to refresh powerbi dataset using run as account. but now Microsoft retired this feature and we need to migrate using system identity. Can anyone help us how we can migrate below code using system identity to refresh powerbi dataset?

$servicePrincipalConnection = Get-AutomationConnection -Name "runasAccount" 
Invoke-ProcessASDatabase -ServicePrincipal -ApplicationId $ServicePrincipalConnection.ApplicationId -CertificateThumbprint $ServicePrincipalConnection.CertificateThumbprint -TenantId $ServicePrincipalConnection.TenantId `
                                        -server $pbiInstanceName `
                                        -DatabaseName $pbiModelName `
                                        -RefreshType DataOnly `

Answer 1

@Nikunj Patel , apologies for the delayed response. Please find below information related to this migration based on your use case.

1. Note that "Azure Managed Identity" can only be used in Azure and for Azure resource, i.e., if you are trying to use managed identity against resources in Azure, Managed Identity can help you. However, if you are planning to authenticate against resources which are not in Azure or not in same Azure tenant, managed identity would not work. For details, see managed identity to access a resource in a different directory/tenant.
2. For your specific requirement, where you used "RunAs Account" as auth mechanism to PowerBI services, managed identity might not be a better choice. The suggestion as provided above by Vasim uses "Microsoft.Azure.Services.AppAuthentication.AzureServiceTokenProvider" which is available as a .NET library (and not PowerShell module). Therefore, it might work locally on your machine but not in Azure Automation account where you do not have access to the installed .NET dlls and everything has to be PS module. For it to work, you would have to follow the steps as mentioned here - Get a token using PowerShell. This method uses the REST API method to get the token.
3. For your scenario, an easier approach would be to use Azure Automation Connections. These are available under "Shared Resources" for Azure Automation (along with credentials, certificates and variables etc.) You can create a connection of Type "AzureServicePrincipal" which closely resembles the RunAs Account.
   Using this method, you do not have to change much in the code and only the -Name "runasAccount" will change with the new ConnectionName.

To beging with, you would have to create

1. Create an Azure Active Directory application and service principal that can access resources. As you are already using certificate based auth with RunAs, use that method when creating Service Principal.
2. Make sure that you have assigned the required access to this new service principal in the PowerBI workspace/dataset.
3. Create connection in Azure Automation Account under "Shared Resource --> Connection" and give it a new name.
4. Update the first line in your code with the name of the new connection.

You could also use the script available below to create the Connection automatically with a new self-signed certificate - https://github.com/azureautomation/runbooks/blob/master/Utility/AzRunAs/Create-RunAsAccount-Updated.ps1 - The permission assignment will still need to be done.

Hope this helps.