Connect to Power BI Api programmatically

By | March 25, 2020
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!