Use Power BI API with PowerShell for PowerBI.com & Azure SQL

I’ve decided to put together a guide, and some blockers I faced and still face with interacting with the Power BI API using a Service Principal & Azure SQL instances in hope it will help others, and perhaps even help me with some of these that I am still facing.

Service Principal Setup

For my solution I wanted to be able to run a PS script in an Azure Automation Runbook unattended to automate some manual processes. The service principal felt like the best method to use for this. Microsoft have got a guide on the setup for this.

Firstly, within Azure register an SP App (AzCli)

Then locate the API permissions, select Add a permission and find the PowerBI Service. We only require 2 Application: Tenant.Read.All Tenant.ReadWrite.All to interact with the API – nothing else is required! (Thank you to Sujit for this article which was the only one I found on the issue)

These permissions require a global administrator to approve them also.

Then, setup a secret for the principal and save this into your password manager/vault for use later.

Lastly, check the Power BI Admin portal has these setting enabled and an AAD Security group here. Make sure your Service Principal is a membr of this group.

Connecting to the API via PowerShell

Modules

For my solution, I had to import the MicrosoftPowerBIMgmt Modules into my Azure Automation Account to use them in a runbook (using a managed identity) – they seem to be dependant on each other so I would install them all to avoid problems:

• MicrosoftPowerBIMgmt
• MicrosoftPowerBIMgmt.Admin
• MicrosoftPowerBIMgmt.Capacities
• MicrosoftPowerBIMgmt.Data
• MicrosoftPowerBIMgmt.Profile
• MicrosoftPowerBIMgmt.Reports
• MicrosoftPowerBIMgmt.Workspaces

If running locally, you can install these via Install-Module in a PS terminal.

Authenticating to the API via PS

Connecting to the API via the service principal is quite straight forward – at a basic level it requires the App ID, Secret and Tenant ID you want to authenticate to, for example:

$AppId = "examplexxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$TenantId = "examplexxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxxxx"
$ClientSecret = "ClientSecretHere" 

# Create Secure Strings

$SecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText
$Credential = New-Object Management.Automation.PSCredential($AppId, $SecurePassword)


# Connect to the Power BI service
Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential

I’ve ended up opting to retrieve the secret from Key Vault. Here is an example of this (make sure you have read/get/list permissions on the vault):

Connect-AzAccount -Identity

# Get Azure App Secret - PowerBI App

$keyVault = Get-AzKeyVaultSecret -VaultName "VaultName" -Name "SecretName" -AsPlainText
$keyVault | ConvertTo-SecureString -AsPlainText -Force

# Create secure strings

$SecurePassword = ConvertTo-SecureString $KeyVault -Force -AsPlainText
$Credential = New-Object Management.Automation.PSCredential($AppId, $SecurePassword)

# Connect to the Power BI service

Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $TenantId -Credential $Credential

API Requests to PowerBI.com which have Azure SQL sources

Microsoft has lots of documentation on this, if using PowerBI.com (new type) workspaces then this will be helpful to reference to: https://docs.microsoft.com/en-us/rest/api/power-bi/ – the ‘In Group’ categories are mainly the ones you will want to use as examples for your calls.

For example, turning on the default refresh for an existing dataset would work like so:

# Get IDs
$workspace = Get-PowerBIworkspace -Name "workspace 1"
$workspaceid = $workspace.id

$dataset = Get-PowerBIDataset -WorkspaceId $workspaceid
$datasetid = $dataset.id

# Compile API Body request JSON for refresh enable 
$refresh = '{ 
  value: { 
    "enabled": true
  }
}'

# Set content to JSON 
$content = 'application/json'

# Invoke PowerBI API

Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$workspaceid/datasets/$datasetid/refreshSchedule" -Method PATCH -Body $refresh -ContentType $content -Verbose

Or to update the SQL server details for a PowerBI.com dataset:

# Get Azure SQL Instance 
$sqlserver = Get-AzSqlServer -ResourceGroupName $resourcegroupname | Select -ExpandProperty FullyQualifiedDomainName

# Get Dataset GUID Ids
$dataset = Get-PowerBIDataset -WorkspaceId $workspaceid
$datasetid = $dataset.id

# Set content to JSON 
$content = 'application/json'

# Compile API Body request JSON for SQL Server Details
$Parameters = @{
  "updateDetails"= @(
      @{
          "name"="SQL Database";
          "newValue"="Db001prod";
       },
      @{
          "name"="SQL Server";
          "newValue"="$($sqlserver)";
       }            
  )
}
$Params = $Parameters | ConvertTo-Json -Compress
 
# Update Dataset Parameters
Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$workspaceid/datasets/$datasetids/Default.UpdateParameters" -Method POST -Body $Params -ContentType $content -Verbose

The parameter names may be different for you, depending on what the PBIX uploaded had saved as the values.

I’ve made a generic script example, which may also be of use over on my GitHub repository https://github.com/riosengineer/PowerShell/blob/main/Power-BI-Automation.ps1

Issues & Limitations

(400) Bad Request

When receiving this error it was mostly down to two things 1) I was not a datasource owner or 2) the syntax in the JSON body was wrong in some capacity.

It’s always good to make sure you are a dataset owner, for example:

# Take over data sets
foreach ($datasetids in $datasetid){
    Invoke-PowerBIRestMethod -Url "https://api.powerbi.com/v1.0/myorg/groups/$workspaceid/datasets/$datasetids/Default.Takeover" -Method Post -Verbose
}

Unable to update dataset Credentials in PowerBI.com (Not embedded) for Azure SQL data source gateways

PowerBI.com and Azure SQL instances seemingly get a random GUID gateway ID when they are imported to the workspace, even though they aren’t really an actual (real) gateway like Power BI on premise.

Furthermore, Microsoft doesn’t seem to offer any examples on how this would work in PowerShell, but it appears to require you to encrypt the credentials with RSA-OAEP before the API will accept the request. Otherwise you will just get a bad request in return.

There is only a small note on the docs page about this, and it only seems to offer examples for .NET core or Java.

I did come across this article which offers a PS solution but it is only for on premise PowerBI and not PowerBI.com with an Azure SQL so I was unsuccessful in getting this to work. Let me know if you know a way on how to do this in PowerShell?

API Limits

The API only allows 8 refresh requests in a set time period, after this you’ll start to receive 400 bad requests responses which can be misleading – but this was my experience.

Conclusion

I’ve managed to reach most of my end goal, but frustratingly I’m still not able to find or compile a solution for the RSA-OAEP encryption in PowerShell. Without this I can’t update the data source credentials using the API and Power BI PS module as I had hoped.

Hopefully others who read this article may have a solution and could share – which would be great.

Thanks to all the authors who I referenced throguhout the article – their guides really proved useful where Microsoft seemed to lack from there documentation on this.

What are your thoughts? Anyone else having similar issues I faced?