Service Principal authentication (for Graph API) without hardcoded credentials

[paddy_135]

Hello! I am successfully getting data from Graph API (in a dataflow) using a Service Principal to authenticate. However, I've had to hardcode the credentials into the query - this is an issue as the Service Principal has sensitive accesses.

I am trying to figure out a way to use the "data source settings" / "manage connections" UI to specify the Service Principal credentials, but I haven't had any luck.

Here is the current query I'm using to generate the token (with hardcoded credentials) which I then use in the Authorization header for various graph api endpoints.

let
  resource = "https://graph.microsoft.com",
  tenantId = "************************",
  clientId = "******************",
  clientSecret = "******************************",
  tokenResponse = Json.Document(Web.Contents("https://login.microsoftonline.com/" & tenantId & "/oauth2/token",
    [
        Content = Text.ToBinary(Uri.BuildQueryString(
            [
                client_id = clientId,
                resource = resource,
                grant_type = "client_credentials",
                client_secret = clientSecret
            ]
        )),
        Headers = [Accept = "application/json"]
    ])),
  access_token = tokenResponse[access_token]
in
  access_token 

I have tried many variations to update the connection in the UI to use the service principal, but it always fails to login. See below example. I've tried different URL variations but I can never get it to successfully update.

Does anyone know how to avoid hardcoding the Service Principal credentials?

Semi-related comments:

• I have successfully used the UI to authenticate with a Service Principal for two different APIs (https://api.loganalytics.io/v1 and https://api.securitycenter.microsoft.com/api/advancedqueries). I'm only encountering this issue with Graph (and may have same issue with management.azure later).
• Fabric Pipelines can query graph api without hardcoding credentials using "copy data" functionality - it handles all token generation behind the scenes. See below screenshot. For various reasons, unfortunately I'm not allowed to utilise Fabric Pipelines, hence using Dataflows.

[paddy_135 · Accepted solution]

Ok, for anyone in the future wanting a (sort-of) solution...
It turns out that service principal authentication via the UI in dataflows will work for beta graph API endpoints, but will not work for v1.0. For example, use https://graph.microsoft.com/beta/users instead of https://graph.microsoft.com/v1.0/users.

We are changing all queries our queries use the beta endpoints. So far no particular issues, though of course there are some minor risks of things changing when using the beta endpoints. That being said, Microsoft themselves use beta endpoints for production purposes, so they should be quite stable.

When setting it up, make sure that you are specifying the connection for either the full endpoint or for https://graph.microsoft.com/beta, not https://graph.microsoft.com as this won't work. Refer screenshot below.

[Anonymous]

Hi @paddy_135 

You can consider to use Python to get the token, then set it as a paramater to Power query, you can refer to the following link about it.

oauth - Using Python to retrieve access token for Power BI's Rest API? - Stack Overflow

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

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[paddy_135]

Hi Yolo

Thanks for taking the time to reply. However, I don't think this solves the issue - the hardcoded credentials would just be moved to wherever the python code is defined. Unless I misunderstood your suggestion?

Thanks