Run an Azure Automation runbook with PowerShell to get data from the Power BI REST API

Unfortunately, it is not possible to make a login to Power BI with the Cmdlet’s (Login-PowerBIServiceAccount) by Azure Automation. So, you cannot use the Power BI PowerShell commands to get very easy the requested information. But if you want to automate the running of your script, like to export some data in your Blob Storage automatically, you can use Azure Automation, but you need to make another authentication and another usage of the commands to get the data. So, you must create an authentication token, like you do that by Power BI Embedded, and then you can invoke a REST call. Sounds easy? Here is the script:

Developing Azure Automation with the PowerShell ISE
$connectionName = "AzureRunAsConnection"

try {
    # Get the connection "AzureRunAsConnection "
    $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName

     "Logging in to Azure..."
    Add-AzureRmAccount `
    -ServicePrincipal `
    -TenantId $servicePrincipalConnection.TenantId `
    -ApplicationId $servicePrincipalConnection.ApplicationId `
    -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint
catch {
    if (!$servicePrincipalConnection)
        $ErrorMessage = "Connection $connectionName not found."
        throw $ErrorMessage
    } else{
        Write-Error -Message $_.Exception
        throw $_.Exception

# Application id from
$clientId = "<Id>"

# Credetials of the user
$myPassword = '<Password>'
$myUsername = '<Username>'

# function to create an app token with user credentials
 function GetAuthToken {
     $redirectUri = "urn:ietf:wg:oauth:2.0:oob"
     $resourceAppIdURI = ""
     $authority = "";
     $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority
     $PWord = ConvertTo-SecureString -String $myPassword -AsPlainText -Force
     $credentials = New-Object System.Management.Automation.PSCredential $myUsername,$PWord
     $AADcredential = New-Object “Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential” -ArgumentList $credentials.UserName,$credentials.Password
     $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $AADcredential)
     return $authResult

# Save the API token
$token = GetAuthToken

# Create an autentification header
$headers = New-Object "System.Collections.Generic.Dictionary[[String],[String]]"
$headers.Add("Content-Type", "application/json")
$headers.Add("Authorization", $token.CreateAuthorizationHeader())

# Get all workspaces ...
$a = Invoke-RestMethod -Uri "" -Method Get -Headers $headers
foreach ($b in $a.value ) {

Categorized: Allgemein

Comments are closed.