Connect to Power BI Api programmatically

Rate this post

Hello Everyone,

Today I am talking about PowerBI REST API Integration. This will help you understand in taking correct steps to authenticate with Power BI service. You can take approach and apply any programming language of your choice.

Prerequisites

Postman
oauth2 Concepts
Azure Portal

Walkthrough

I have listed some technical requirements and then methods to achieve this integration.

Create an application in Azure and add permissions

Register your application in Azure Portal. You can follow this [article] (https://docs.microsoft.com/en-us/azure/active-directory/develop/quickstart-register-app)

API Permissions
Depending on the type of query you are running, you will need specific set of permissions.

In my example, I am querying workspaces, so the permission for that is either Workspace.ReadWrite.All or Workspace.Read.All

You can refer this article to see the complete list of permissions for any query.

[!Note]
Most of these permissions work with delegated access.
Using Postman (Password Based authentication)

Getting Access Token

You can get access token using Authorization Code or Client Credentials. Since we are using Postman, we can leverage bulk-edit feature and do the following
```
 POST: https://login.microsoftonline.com/yourtenantid/oauth2/token
 Body:
    grant_type: password
    username: yourusername
    password: yourpassword
    client_id: yourapplicationid
    client_secret: yourclientsecret
    resource: https://analysis.windows.net/powerbi/api
    scope: Workspace.Read.All User.Read
```
Once you hit send you will see an access token.

Note: If you are going to use ADAL libraries, you cannot use grant_type = password. You will need to use User Credential class.

Use this access token to make calls to PowerBI Api

   GET: https://api.powerbi.com/v1.0/myorg/admin/groups?$top=1
   Headers:
     Authorization: Bearer youraccesstoken

You should see a response shown below:

{
        "@odata.context": "http://wabi-us-central-a-primary-redirect.analysis.windows.net/v1.0/myorg/admin/$metadata#groups",
         "@odata.count": 2,
        "value": [
            {
              "id": "F769F2BB-9825-4B11-8705-998FAF1F8B22",
              "isReadOnly": false,
              "isOnDedicatedCapacity": false,
              "capacityMigrationStatus": "",
              "type": "Workspace",
              "state": "Active",
              "name": "Sample Workspace"
            }
                ]
}

Using Powershell (Passwordless / Azure Service Principal)

This section will talk about how you can authenticate with power bi server-to-server. To achieve this, there are few prerequisites to follow:

Create Client Secret for your Azure application

Follow these instructions.

Allow your application to be authorized (Requires Tenant admin)
- Login into to your power bi admin portal.
- Go to Tenant Settings
- You will see “Allow service principal to use Power BI APIs”
- You must enable this and provide the security group name.
- Your app should be part of this SG.
Read more: Use Power BI API with service principal (Preview) | Microsoft Power BI Blog | Microsoft Power BI

Authorize your app to your workspace

Power BI has a powershell module which can be used to achieve this requirement. See the sample below

    #Install Power BI Module
    Install-Module MicrosoftPowerBIMgmt.Workspaces
    #Login
    Login-PowerBI
    # Service Principal Object ID of your Service Principal (App Registrations)
    $SPObjectId = '36......'
    $pbiWorkspace = Get-PowerBIWorkspace -Name ""
    #confirm if it is correct
    Write-Host $pbiWorkspace 
    #if So, Add As Admin or Member
    Add-PowerBIWorkspaceUser -Id $pbiWorkspace.Id -AccessRight Admin -PrincipalType App -Identifier $SPObjectId

Once you have completed these steps, its time to test them. You can use this script to try out your changes

    #Login to Azure CLI
    az login
    # Get Data from Key Vault
    $secretData = az keyvault secret show --vault-name 'your KV name' --name 'your secret Name' | ConvertFrom-Json | Select value

    $ClientId = ""
    $ClientSecret = $secretData.value
    $loginURL = "https://login.microsoftonline.com" 
    $tenantdomain = "microsoft.onmicrosoft.com"
    $scope1 = "https://analysis.windows.net/powerbi/api/.default"
    $clientSecret = (ConvertTo-SecureString $ClientSecretRaw -AsPlainText -Force)
    $msal = Get-MsalToken -clientID $clientID -clientSecret $clientSecret -tenantID $tenantdomain -Scopes $scope1
    $accessToken = $msal.AccessToken
    #Prepare header
    $AuthHeader = @{
    'Content-Type'  = 'application/json'
    'Authorization' = "Bearer $($accessToken)"
    }
    #Set Power BI URL
    $URI = "https://api.powerbi.com/v1.0/myorg/groups//datasets//refreshes"
    #Make HTTP Request
    Invoke-RestMethod -Uri $URI -Headers $AuthHeader -Method POST

You should be able to make authenticated calls successfully.

I hope you enjoyed reading this!

Prerequisites

Walkthrough

Create an application in Azure and add permissions

Using Postman (Password Based authentication)

Using Powershell (Passwordless / Azure Service Principal)