• Ben Watt

Power BI REST API with Python and MSAL. Part II.


Back in November 2020 I blogged about using the Python MSAL library to authenticate to Power BI and call the REST APIs. In that blog I only talked about authenticating via username and password. At the time of that blog the ability to authenticate via the service principal was relatively new, either in public preview or only recently GA (Generally Availability, a.k.a fully released).


I've been wanting to update this for yonks, so this is not anything new, just an extension to an old blog.


Power BI loves Python. Part II


I wanted to extend that article with the other ways I use to authenticate using the MSAL library. Here's a summary of the three options:


User Name and Password

This is in the previous blog. It can be run without the need for interaction, so can be used in something like Azure Functions. You'd need to safely store and maintain your credentials in a secure way, like Key Vault.


Username and Password Interactive

This is the same authentication protocol as #1, however no username and password is included in the script. The well-known authentication challenge pop-up is shown at run time. This is useful if you want to run a script from your desktop and not store the credentials anywhere. Also good to share with others, so they can authenticate using their own credentials. Not possible for automation though, as you can't do the interactive thing.


Client (service principal)

This authentication only passes the App Registration Client ID and Client Secret. This is the ideal authentication method for automated scripts as you are not tied to an Azure AD account, so there's no maintenance of password recycling, or non-best practice of running automation under a person's account.


I created a module I use for all of my Python scripts which need to authenticate to Azure. Here it is shared with you, with some simplifications made for readability. I made two main changes to the script

  1. I use Azure Key Vault to manage credentials, not hard-coded in the super unsecure way they are below!

  2. Error handling is simplified. In Azure functions, I write message to the log using the logging.info function, as you have no console to print to.


The Python MSAL authentication module

import msal

authority_url = 'https://login.microsoftonline.com/your-azure-tenant-id'
# authentication details
client_id= '1234abcd-1234-56787-ab12-abcdefg123456' 
client_secret = 'your-secret' # needed if authenticating with acquire_token_for_client function
username = 'youraccount@domain.com' # needed if authenticating with acquire_token_by_username_password function
password = 'YourMegaSafePassword738!*' # needed if authenticating with acquire_token_by_username_password function
scope =['https://analysis.windows.net/powerbi/api/.default']

# ---------------------------------------
# Option 1 for getting the token. If you don't want any credentials saved or the user has multi-factor configured on their account, the user can interactively authenticate
# ---------------------------------------

def get_token_interactive(scope):
 app = msal.PublicClientApplication(client_id, authority=authority_url)
 result = app.acquire_token_interactive(scope)
 if 'access_token' in result:
 return(result['access_token'])
 else:
 print('Error in get_token_interactive:',result.get("error"), result.get("error_description"))

# ---------------------------------------
# Option 2 for getting the token. You can save the username and password and get a token without interacting
# ---------------------------------------

def get_token_username_password(scope):
 app = msal.PublicClientApplication(client_id, authority=authority_url)
 result = app.acquire_token_by_username_password(username=username,password=password,scopes=scope)
 if 'access_token' in result:
 return(result['access_token'])
 else:
 print('Error in get_token_username_password:',result.get("error"), result.get("error_description"))

# ---------------------------------------
# Option 3 for getting the token. If authenticating via client/client_secret
# ---------------------------------------

def get_token_for_client(scope):
 app = msal.ConfidentialClientApplication(client_id,authority=authority_url,client_credential=client_secret)
 result = app.acquire_token_for_client(scopes=scope)
 if 'access_token' in result:
 return(result['access_token'])
 else:
 print('Error in get_token_username_password:',result.get("error"), result.get("error_description"))


Setting up the Service Principal option

In short, the below items need to be done. The link at the bottom gives the full step-by-step method

  1. Create the App Registration to get the Client ID and Secret

  2. Create a Microsoft 365 Security Group and add the App Registration to it

  3. In the Power BI Admin settings: enable the Admin API Settings and add the Security Group*

  4. Add the Security Group to any workspaces you need to

* If you are planning to call the admin APIs, then you need to enable TWO settings in your Power BI Tenant. The instructions in the step-by-step method below don't call this out. See the below image for the two settings I'm referring to.


Considerations

  • It only works on the new type of Workspaces.

  • The new workspaces aren't exactly new anymore, they have been around for ages so are actually old. The "old" old ones are so old, they are like antiques, so might be worth money to a collector!

  • More here on workspace types, but hopefully you're not using old ones any more!

  • There is no licence required for the service principal.

  • It does not work for all Power BI APIs. it only works for some APIs. See here for the supported list


Relevant Links


Setting up the service principal

https://docs.microsoft.com/en-us/power-bi/developer/embedded/embed-service-principal#step-1---create-an-azure-ad-app


My previous blog on this topic

https://www.datalineo.com/post/power-bi-rest-api-with-python-and-microsoft-authentication-library-msal