Skip to content

MSAL Authentication via Service Principal (Python) to Power BI #374

Closed

Description

@marossi10

Describe the bug
I am trying to connect to a Power BI Workspace and Report. In order to do so, I need to get an access token. I'm following the steps of this article https://www.datalineo.com/post/power-bi-rest-api-with-python-and-microsoft-authentication-library-msal. However differently from this article I would like to authenticate via Service Principal. I don't understand why when I authenticate via User and Pwd I get the correct access token while when I authenticate via SP I get the error 'Get report failed 403'.

It follows the code for both types of authentication.

To Reproduce

--------------------------------------------------

Set Authentication variables for SP authentication

--------------------------------------------------

client_id = 'Service Principal Client ID'
client_secret = 'Service Principal Secret'
authority_url = 'https://login.microsoftonline.com/[my tenant]'
scope = ["https://analysis.windows.net/powerbi/api/.default"]
url_groups = 'https://api.powerbi.com/v1.0/myorg/groups'

--------------------------------------------------

Get the access token via SP authentication

--------------------------------------------------

app = msal.ConfidentialClientApplication(
client_id,
authority=authority_url,
client_credential=client_secret)

result = app.acquire_token_for_client(scopes=scope)

accessToken = result['access_token']

--------------------------------------------------

Set local variables for User and Pwd Authentication

--------------------------------------------------

client_id = 'Registered App Client ID'
username = 'my username'
password = 'my pwd'
authority_url = 'https://login.microsoftonline.com/[tenant]'
scope = ["https://analysis.windows.net/powerbi/api/.default"]
url_groups = 'https://api.powerbi.com/v1.0/myorg/groups'

--------------------------------------------------

Get the access token via User and Pwd Authentication

--------------------------------------------------

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"))

accessToken = result['access_token']

print(accessToken)

--------------------------------------------------

Get the workspaces list

--------------------------------------------------

header = {'Content-Type': 'application/json', 'Authorization': f'Bearer {accessToken}'}
api_out_w = requests.get(url=url_groups, headers=header)
workspacesJson = api_out_w.json()

workspacesId = []
workspacesName = []
workspaces = {}

for w in workspacesJson['value']:
workspacesId.append(w['id'])
workspacesName.append(w['name'])

workspaces['id'] = workspacesId
workspaces['name'] = workspacesName

groupName = workspaces['name'][0] # forcing to choose the first workspace
groupId = workspaces['id'][0] # forcing to choose the first workspace

print('Got the workspace info, workspaceName:', groupName, ',workspaceId: ', groupId)

-------------------------------------------------

Get the reports list

--------------------------------------------------

header = {'Content-Type': 'application/json', 'Authorization': f'Bearer {accessToken}'}
api_out_r = requests.get(url=f'https://api.powerbi.com/v1.0/myorg/groups/{groupId}/reports', headers=header)
reportsJson = api_out_r.json()

reportsId = []
reportsName = []
reportsEmbedUrl = []
reports = {}

for r in reportsJson['value']:
reportsId.append(r['id'])
reportsName.append(r['name'])
reportsEmbedUrl.append(r['embedUrl'])

reports['id'] = reportsId
reports['name'] = reportsName
reports['embedUrl'] = reportsEmbedUrl

reportName = reports['name'][0]
reportEmbedUrl = reports['embedUrl'][0] # forcing to choose the first workspace

print('Got the report info, reportName:', reportName, ',reportEmbedUrl:', reportEmbedUrl)

Expected behavior
I would like to get the correct access token (as it happens when I authenticate via user and pwd).

What you see instead
'Get report failed 403'
image

**The MSAL Python version **
version 1.12.0

Activity

  1. rayluo commented on Jun 22, 2021

    @rayluo
    Contributor

    Sounds like you can successfully obtain an access token when you authenticated as a service principal, then MSAL already does its job. Whether that service principal can access a certain resource depends on its permission. You probably need to look into Power BI documents for that.

    BTW, next time when you paste your source code, you can wrap them inside "```python" and "```" (both in their own line), then they will be rendered readably.

    print("hello world")
  2. marossi10 commented on Jun 22, 2021

    @marossi10
    Author

    Thank you Ray Luo (@rayluo) for your reply and support.

    I am not saying that I'm not able to retrieve the token. What I am saying is that the token that is returned is not functioning.
    For instance, the one retrieved with User and Pwd authentication is functional. I have already followed all the steps also mentioned by you in the doc and I have given all the the permissions to the SP in both Admin portal > Tenant settings and then also in the Azure Portal > App Registration > API Permissions. But I keep getting the 403 error.

    Sounds like you can successfully obtain an access token when you authenticated as a service principal, then MSAL already does its job. Whether that service principal can access a certain resource depends on its permission. You probably need to look into Power BI documents for that.

    BTW, next time when you paste your source code, you can wrap them inside "python" and "" (both in their own line), then they will be rendered readably.

    print("hello world")
  3. rayluo commented on Jun 22, 2021

    @rayluo
    Contributor

    Your code snippet seems right from the MSAL perspective. Perhaps the direction would be more on the "How to access Power BI api while authenticate via Service Principal". That is a domain knowledge that more likely be obtained from the Power BI community. In fact, a close look into the blog post "Power BI REST API with Python and Microsoft Authentication Library (MSAL)" you shared in your message, it even contains this bullet point (emphasis mine):

    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.

    By the way, since MSAL 1.11, the confidential client also supports get a token via username/password.

    Looping in our PM Jean-Marc Prieur (@jmprieur) to see whether he has any more input on this.

  4. AccidentalCitizen commented on Jun 22, 2021

    @AccidentalCitizen

    Dear Ray Luo (@rayluo), I too have tried the official code documented in C# for the same purpose, and yet again a Token is received that is non-functional with respect to PowerBI Service-API access:
    https://docs.microsoft.com/en-us/power-bi/developer/automation/walkthrough-push-data-get-token

    In addition, the MSAL Library for .NET Microsoft.Identity.Client -Version 4.33.0 was made use of.

    Many thanks
    Rajah

  5. rayluo commented on Jun 22, 2021

    @rayluo
    Contributor

    Dear Ray Luo (@rayluo), I too have tried the official code documented in C# for the same purpose, and yet again a Token is received that is non-functional with respect to PowerBI Service-API access:
    https://docs.microsoft.com/en-us/power-bi/developer/automation/walkthrough-push-data-get-token

    In addition, the MSAL Library for .NET Microsoft.Identity.Client -Version 4.33.0 was made use of.

    Many thanks
    Rajah

    Jean-Marc Prieur (@jmprieur) , now we have stronger reason to pull you into this conversation. :-)

    Is there any intrinsic characteristic about Power BI and Service Principal that we would need to know?

  6. rayluo commented on Jul 6, 2021

    @rayluo
    Contributor

    Per offline investigation, marossi10 's case can be resolved by configuring Power BI embed report's tokenType to models.TokenType.Embed. See Understand the different embedding solutions for more information.

    AccidentalCitizen , you may also want to give it a shot.

  7. marossi10 commented on Jul 7, 2021

    @marossi10
    Author

    Ray Luo (@rayluo) AccidentalCitizen another important step is to generate the embedToken. The access token won't be enough.
    The config parameter used to call powerbi.embed(embedDiv, config) will have therefore the following shape:

    image

  8. locked and limited conversation to collaborators on Jul 16, 2021
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Metadata

Metadata

Assignees

No one assigned

    Type

    No type

    Projects

    No projects

      Milestone

      No milestone

      Relationships

      None yet

      Development

      No branches or pull requests

      Issue actions