Publishing and refreshing report with service principal

[SanderTK]

I have a PowerShell script that creates a workspace, publishes a report to that workspace, updates the query parameters of the dataset, and refreshes that dataset.

This works perfectly fine if I sign in with a user account using the "Connect-PowerBIServiceAccount" command but because the user account has MFA, I can't automate the script in an Azure DevOps pipeline.

Can I perform the same tasks with a Service Principal?

The commands are:

- New-PowerBIWorkspace (for creating the workspace)
- New-PowerBIReport (for publishing the report)
- Invoke-PowerBIRestMethod (for updating the parameters and refreshing the dataset)

[NickSandel]

Has someone got an example of how to use this with the cmdlet New-PowerBIReport?

I have been trying and keep hitting a 401 Unauthorized error. By chance one time I published some different pbix reports which worked fine but I think they had a connection to a local dataset. When I change the connection to a dataset in the workspace with syntax like: 

"server=powerbi://api.powerbi.com/v1.0/myorg/WORKSPACE;database=Model"
Then it fails. The cmdlet works fine with my credentials. I tried adding ";User ID=app:appid@tenantid;Password=password" but that hits a 500 error instead

[pbimhkc]

@SanderTK did you have the script also update the dataset credentials after updating parameters? I'm basically trying to implement the same thing but am currently stuck on that

[SanderTK]

It certainly does.
The PowerShell script for it:

    #Retrieve scoped access token for service principal
    $spnBody = @{
        'client_id'     = "$clientId"
        'scope'         = 'https://api.loganalytics.io/.default'
        'client_secret' = "$spnSecret"
        'grant_type'    = 'client_credentials'
    }

    $contentType = 'application/x-www-form-urlencoded'

    $spnToken = Invoke-RestMethod `
        -Uri "https://login.microsoftonline.com/$tenantId/oauth2/v2.0/token" `
        -Body $spnBody -ContentType $contentType -Method Post

    if ([string]::IsNullOrEmpty($spnToken.access_token)) {
        Throw "Error in acquiring access token"
    }

    #Set Oauth2 credentials for PowerBI dataset
    $credentials = @{
        credentialData = @(@{
                name  = "accessToken"
                value = $spnToken.access_token
            })
    }

    $credentialsString = $credentials | ConvertTo-Json -Compress

    $token = @{
        credentialDetails = @{
            credentialType      = "OAuth2"
            credentials         = $credentialsString
            encryptedConnection = "NotEncrypted"
            encryptionAlgorithm = "None"
            privacyLevel        = "None"
        }
    }

    $tokenString = $token | ConvertTo-Json

    try {
        Invoke-PowerBIRestMethod `
            -url "https://api.powerbi.com/v1.0/myorg/gateways/$gatewayId/datasources/$datasourceId" `
            -Body $tokenString -Method Patch -Verbose
    }
    catch {
        $_
        Throw "Error in updating datasource credentials"
    }

 
I realize that in the last Invoke-PowerBIRestMethod cmndlet I have the entire url listed which isn't neccesary. You can of course always finetune the code (I'm certainly no PowerShell pro as you can see from the formatting).

reference: https://learn.microsoft.com/en-us/rest/api/power-bi/gateways/update-datasource

[riyajshaikh09 · Accepted solution]

Yes you can do that using service priciple:

    #Create secure string & credential for application id and client secret
    $pbiSecurePassword = ConvertTo-SecureString $ClientSecret -Force -AsPlainText
    $pbiCredential = New-Object Management.Automation.PSCredential($AppId, $pbiSecurePassword)

    #Connect to the Power BI service
    try {
       Connect-PowerBIServiceAccount -ServicePrincipal -TenantId $Tenantid -Credential $pbiCredential | Out-Null
        Write-Host "Power BI connection is succeded using Service Principle"  
    }
    catch {
        Write-Host "Failed to connect to Power BI ."
        Write-Host $_.ScriptStackTrace
    }
   

[Anonymous]

Hi @SanderTK ,

There are some limitations when use service principal , so I'm afraid that the above commands can't work if used the service principal...

Power BI Workspace Management with PowerShell (mssqltips.com)

Best Regards