Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

Power BI API via PowerShell fails in Azure DevOps

Hi all,

 

I'm trying to automate the deployment of Power BI (and other modern DWH components) via Azure DevOps releases. I've created a PowerShell script that looks at the contents of a setup file in my repository and then starts creating workspaces (if they don't exist) and publish reports to these workspaces. 

 

Everything works fine when I run the PowerShell script on my local laptop. It hangs when I run it in DevOps. I've narrowed the issue down to the authentication part of the script. 

 

I use an app registration to authenticate against Power BI. This works. The first time I ran the script I had to assign rights to the app, and now it just works. 

My script below (simplified to demo the issue)

$clientId = "xyz" -- clientId of app here
function GetAuthToken
{
    if(-not (Get-Module AzureRm.Profile)) {
      Import-Module AzureRm.Profile
    }

    $redirectUri = "urn:ietf:wg:oauth:2.0:oob"

    $resourceAppIdURI = "https://analysis.windows.net/powerbi/api"

    $authority = "https://login.microsoftonline.com/common/oauth2/authorize";

    $authContext = New-Object "Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext" -ArgumentList $authority

    $authResult = $authContext.AcquireToken($resourceAppIdURI, $clientId, $redirectUri, "Auto")

    return $authResult
}

$token = GetAuthToken

# Building Rest API header with authorization token
$auth_header = @{
   'Content-Type'='application/json'
   'Authorization'=$token.CreateAuthorizationHeader()
} 

$target_group_name = "ABC Test Workspace"
$uri = "https://api.powerbi.com/v1.0/myorg/groups"
$body = "{`"name`":`"$target_group_name`"}"
$response = Invoke-RestMethod -Uri $uri –Headers $auth_header –Method POST -Body $body
Write-Host $response.id

What happens is that on the AcquireToken function, a popup window (the authentication window) opens up and then immediately closes (because the app is already authenticated). 

However, in DevOps, the script seems to hang on this popup. When I run this in Windows the script continues past the popup and creates the Power BI workspace called "ABC Test Workspace". In DevOps, nothing happens and I have to cancel the operation manually. 

 

Does anybody have an idea how to solve this? It would be great to have a working DevOps pipeline to do CI/CD stuff with Power BI from your repositories. 

 

Thanks in advance.

 

Cheers! 

Bas

6 REPLIES 6
Jayendran
Solution Sage
Solution Sage

Hi @Anonymous 

 

Not sure that you aware of the Azure DevOps Extension called PowerBI Action. Using this you can do some operation easily with out having worry about powershell scripts.

 

If you still want to do some additional things , you can see the source code of the extension, where the author is also using powershell to build this extension.

 

The author put all the powershell cleanly in a function, as per that

 

Function Get-AADToken {
    Param(
        [parameter(Mandatory = $true)][string]$Username,
        [parameter(Mandatory = $true)][SecureString]$Password,
        [parameter(Mandatory = $true)][guid]$ClientId,
        [parameter(Mandatory = $true)][string]$Resource
    )

    $authorityUrl = "https://login.microsoftonline.com/common/oauth2/authorize"

    ## load active directory client dll
    $typePath = $PSScriptRoot + "\Microsoft.IdentityModel.Clients.ActiveDirectory.dll"
    Add-Type -Path $typePath 

    Write-Verbose "Loaded the Microsoft.IdentityModel.Clients.ActiveDirectory.dll"

    Write-Verbose "Using authority: $authorityUrl"
    $authContext = New-Object -TypeName Microsoft.IdentityModel.Clients.ActiveDirectory.AuthenticationContext -ArgumentList ($authorityUrl)
    $credential = New-Object -TypeName Microsoft.IdentityModel.Clients.ActiveDirectory.UserCredential -ArgumentList ($UserName, $Password)
    
    Write-Verbose "Trying to aquire token for resource: $Resource"
    $authResult = $authContext.AcquireToken($Resource, $clientId, $credential)

    Write-Verbose "Authentication Result retrieved for: $($authResult.UserInfo.DisplayableId)"
    return $authResult.AccessToken
}

 I'm also writing a detailed article for CICD for PowerBI using Azure DevOps, 

 

https://social.technet.microsoft.com/wiki/contents/articles/53172.azuredevops-cicd-for-powerbi-reports.aspx

 

But it's still in progress, I stil need some time to need these too Smiley Wink

Anonymous
Not applicable

The reason I can't use Power BI Actions (I tried!) is that it needs a username and password. This is not acceptible for our clients. Best practice is to use a client id. 

Via PowerShell this is 100% possible. In fact the scripts work when I run them locally on my laptop. They seem to get stuck on the authentication part when I run them as part of a DevOps pipeline. 

 

I'm interested in your blog article! 

Hi @Anonymous 

Ok could you please try my script below

 

$applicationId = "xxxxxxxx";
$securePassword = "xxxxxx" | ConvertTo-SecureString -AsPlainText -Force
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $applicationId, $securePassword
Connect-PowerBIServiceAccount -ServicePrincipal -Credential $credential -TenantId "xxxx"

Which is using the Client ID autentication part.

 

I've also provided this answer in SO

 

By this way it will work in both your local and azure devops

Anonymous
Not applicable

This doesn't work because the Power BI cmdlets don't accept service principals for most operations. For example:

2019-08-19 08_23_01-Clipboard.png

 

Therefore I'm trying to login using a native app (instead of web app) using the code I posted in the OP. That works perfectly fine in Windows, just not in DevOps. 

Hi @Anonymous 

 

It should work. I think you are in luck today. I've just completed my Article 

https://social.technet.microsoft.com/wiki/contents/articles/53172.azuredevops-cicd-for-powerbi-reports.aspx

 

Download Full code

https://gallery.technet.microsoft.com/AzureDevOps-CICD-for-fefd58b8

 

I've explained all in details about in this article. It would help you. Best of luck !

Hi @Anonymous 

 

Do you have any update ? If my replay helps please accept it as answer/giving kudos which will help other community members

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.