• Ben Watt

Power BI REST API with Python and Microsoft Authentication Library (MSAL)

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!



Power BI loves Python!

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:

  1. Application Name: Whatever you like. Your Azure admin team will see this so make it meaningful

  2. Application Type: Native

  3. 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)


The Power BI App Registration Tool


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


Where to grant consent in the Azure Portal

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!


Relevant Links


Microsoft Docs: MSAL overview

https://docs.microsoft.com/en-us/azure/active-directory/develop/msal-overview


GitHub: MSAL for Python

https://github.com/AzureAD/microsoft-authentication-library-for-python


MSAL Syntax:

https://msal-python.readthedocs.io/en/latest/


Microsoft Docs: Power BI REST API Reference

https://docs.microsoft.com/en-us/rest/api/power-bi/


Ye old ADAL library

https://github.com/AzureAD/azure-activedirectory-library-for-python


Microsoft Docs: Power BI PowerShell Cmdlets

https://docs.microsoft.com/en-us/powershell/power-bi/overview?view=powerbi-ps



Copyright © 2019 Datalineo Ltd. All Rights Reserved