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
orWorkspace.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.ReadOnce 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!