Update Sep 2021. Great to see you here! I suggest after you read read this blog, don't miss the other two additions to the series, where I will give you an easier python module option, called azure-identity in the third & final one. Links are at the bottom.
I recently decided to develop some Power BI automation scripts for a customer using the Power BI REST APIs and Python. I had used the APIs before, but mainly for ad-hoc activities. For that, I would use the Power BI PowerShell Cmdlets, which offer nice syntax sugaring for the APIs and allow you to sign-in interactively. If you haven't used the PowerShell Power BI Cmdlets, here's a blog by my good friend Nicky van Vroenhoven on getting it installed.
Fully automating requires the ability to authenticate in the script, without the username/password screen popping up. There is a Python library called ADAL (Active Directory Authentication Library) which has been the de facto standard and what most current Python/Power BI REST API blogs mention. In late 2019 a new library went GA, called MSAL (Microsoft Authentication Library), and this blog is here to show you how to use it!
Before you run any scripts, there's some set up to be done. I'll run through that, then we'll get to the Python/MSAL part. These setup activities haven't changed, so if you have already configured the App Registration and just want to know the authentication stuff, then skip ahead!
What you need
An Azure Active Directory account, with a Power BI Pro licence, and MFA not enabled
Grant the AAD account access to whatever Power BI components (workspaces for e.g.) you require to automate, depending on the type of stuff you're doing. It may need as much as Power BI Admin access if you're doing tenant-level things
Python 3.x installed, with the following modules installed: msal, requests, json. This is done via a command prompt and running pip install msal requests. json is usually installed by default
App Registration setup
There's a few steps to create an App Registration in Azure, which gives the authority to run API calls against Microsoft services.
Step 1 of 2: Create an App Registration via the Power BI Developer Registration Tool
Go to https://dev.powerbi.com/apps
Sign-in with your Power BI username/password
To register the application, select:
Application Name: Whatever you like. Your Azure admin team will see this so make it meaningful
Application Type: Native
API Access: whatever you need. Large organisations may be selective here but I find most of the time "Select All" is chosen
Select Register and keep a copy of the Application ID (also referred to as Client ID)
Step 2 of 2. In the Azure Portal, the App registration needs final consent by your Azure admin
Browse to App Registrations or use this direct link
Choose the Application you just registered and select API permissions
Select Grant admin consent for yourdomain.com
Authentication via MSAL
It's very straight forward code when push comes to shove, but knowing the intricacies of the authentication options and API scope are hidden in it's simplicity.
There can be variations to the below depending on your organisation structure (multi-tenant for e.g.) and security requirements.
import msal
import requests
import json
# --------------------------------------------------
# Set local variables
# --------------------------------------------------
client_id='1234abcd-1234-56787-ab12-abcdefg123456'
username = 'youraccount@domain.com'
password = 'YourMegaSafePassword738!*'
authority_url = 'https://login.microsoftonline.com/yourdomain.com'
scope = ["https://analysis.windows.net/powerbi/api/.default"]
url_groups = 'https://api.powerbi.com/v1.0/myorg/groups'
# --------------------------------------------------
# Use MSAL to grab a token
# --------------------------------------------------
app = msal.PublicClientApplication(client_id, authority=authority_url)
result = app.acquire_token_by_username_password(username=username,password=password,scopes=scope)
# --------------------------------------------------
# Check if a token was obtained, grab it and call the
# Power BI REST API, otherwise throw up the error message
# --------------------------------------------------
if 'access_token' in result:
access_token = result['access_token']
header = {'Content-Type':'application/json','Authorization': f'Bearer {access_token}'}
api_out = requests.get(url=url_groups, headers=header)
print(api_out.json())
else:
print(result.get("error"))
print(result.get("error_description"))
Here are some additional details on the script and setup.
The AAD account can't have multi-factor authentication. This requires a pop-up which doesn't work with automation. It's best to use a dedicated service account for this type of activity
MSAL has two variants of authentication, public and confidential client. The former has a function to get a token via username/password, which is necessary to authenticate for Power BI REST APIs. If you authenticate via Client ID and Client Secret, you'll get a 403 error when calling the Power BI API.
The scopes parameter is a new concept in MSAL compared to ADAL. This allows you to further restrict the session's capabilities at runtime. This relates to the API access options you selected in Step 1 above. The parameter in the script above shows the endpoint and .default, meaning all assigned API access options to the registered app.
My script example has hardcoded the account details for ease of reading. DON'T YOU DO THAT OK!?! I suggest looking at Azure Key Vault to store & retrieve these details.
For simplicity, I'm calling the Get Groups API, just to you can see a result set if you try this yourself.
For interest, you can decode your token by pasting it into https://jwt.io/ This will decode the string to display the header and payload. Good for troubleshooting.
Microsoft Graph API
If you want to try the Microsoft Graph API, then switch your token acquisition to the below. You will need to create a Client Secret in the Azure Portal under your App Registration:
app = msal.ConfidentialClientApplication(
client_id,
authority=authority_url,
client_credential=client_secret)
result = app.acquire_token_for_client(scopes=scope)That's it! Hopefully if you're looking to migrate, then this will help you get started. ADAL will not be receiving any new feature improvements, but I doubt it will be retired soon, so migrating isn't urgent. However, for any new projects go straight to MSAL!
Update Sep 2021
Read the Part II blog here: Power BI REST API with Python and MSAL. Part II
Read the Part III blog here: Power BI REST API with Python Part III, azure-identity
Relevant Links
Microsoft Docs: MSAL overview
GitHub: MSAL for Python
MSAL Syntax:
Microsoft Docs: Power BI REST API Reference
Ye old ADAL library
Microsoft Docs: Power BI PowerShell Cmdlets
Tech Community, advise to switch to MSAL & Graph
Comments