Cannot get Power BI API POST call for "Datasets - Execute Queries In Group" to authorize

[CasperRademaker]

Hi all,

I'm trying to call one of these two endpoints using Python, but running into 401 Unauthorized without any details from the API response.

https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries-in-group
https://learn.microsoft.com/en-us/rest/api/power-bi/datasets/execute-queries

Below is my code in Python, for full transparency versions used: Python 3.12.9, requests 2.32.2, msal 1.31.1

# internal imports
import configuration_details

# external imports
import json
import msal  # microsoft authentication library
import requests

(
    authority_url,
    azure_ad_client_id,
    azure_ad_client_secret,
) = configuration_details.retrieve_pbi_details()

authentication_context = msal.ConfidentialClientApplication(
    client_id=azure_ad_client_id,
    client_credential=azure_ad_client_secret,
    authority=authority_url,
    validate_authority=True,
)

token = authentication_context.acquire_token_for_client(
    scopes=["https://analysis.windows.net/powerbi/api/.default"]
)

access_token = token.get("access_token")

data = json.dumps({"queries": [{"query": "EVALUATE VALUES(Worklogs)"}]})

response = requests.post(
    data=data,
    url="https://api.powerbi.com/v1.0/myorg/groups/a365faf8-a136-48b7-96f0-2368934a9328/datasets/7284eead-fb8b-4fbf-9532-0c7df5dcd409/executeQueries",
    headers={
        "Authorization": f"Bearer {access_token}",
        "Content-Type": "application/json",
    },
    timeout=60
)

The response is 401 with content `{'code': 'PowerBINotAuthorizedException', 'parameters': {}, 'details': [], 'exceptionCulprit': 1}`

Checked:

- Workspace ID and dataset ID are correct
- Service principal related to client ID had admin rights on the workspace

- Client secret is correct

- Other similar API calls work with the same code, for example GET 

https://api.powerbi.com/v1.0/myorg/groups/a365faf8-a136-48b7-96f0-2368934a9328/datasets/7284eead-fb8... gives 200 response with dataset details.

- DAX query is correct, works in Power BI Desktop DAX query view and gives results
- In admin portal, "Semantic Model Execute Queries REST API" is "Enabled for the entire organization"

- In admin portal, "Service principals can use Fabric APIs" is "Enabled for the entire organization"

Would appreciate any pointers on this. Thanks in advance!

[CasperRademaker · Accepted solution]

I resolved the issue myself. For anyone else running into this: the issue was the semantic model having row-level security enabled. It seems you cannot use service principals in this case. 

It is sparsely mentioned in the Power BI API endpoint limitations section, but would appreciate a clear API response from the API itself:

To use Service Principals, make sure the admin tenant setting Allow service principals to use Power BI APIs under Developer settings is enabled. However, regardless of the admin tenant setting, Service Principals aren't supported for datasets with RLS per RLS limitations or datasets with SSO enabled.