Best Practice for Calling a REST API with Username/Password Authentication Without Storing Credentia

[Flo1]

Hi everyone,

I'm currently building a Power BI report for a customer that needs to retrieve data from a REST API.

The API provider has created a dedicated user account for me (username + password). My current approach is to use a Power Query function to authenticate against the API, retrieve a bearer token, and then use that token to request the actual data. Technically, this works without any issues.

The problem is that the username and password have to be hardcoded in the Power Query function, which obviously isn't a secure solution.

To make my question easier to understand, I recreated the entire process using the public DummyJSON API. The documentation can be found here:

 https://dummyjson.com/

First, I use a custom Power Query function (fnGetAccessToken) to obtain a bearer token. The authentication endpoint is documented here: https://dummyjson.com/docs/auth

As you can see, the username and password are embedded directly in the source code (these are only demo credentials provided by the sample API, not production credentials):

() =>
let
    // Get Bearer Token via username & password...
    TokenRequest = Web.Contents(
        "https://dummyjson.com/auth/login", 
        [
            Headers = [
                #"Content-Type" = "application/json"
            ],
            Content = Json.FromValue([
                username ="emilys",
                password = "emilyspass" // password exposed in source code 😞
            ])
        ]
    ),
    accessToken = Json.Document(TokenRequest)[accessToken]
in
    accessToken

I then use the generated bearer token to retrieve the actual data (in this example, the products endpoint, documented here: https://dummyjson.com/auth/product😞

let
    // Get access token via function...
    accessToken = fnGetAccessToken(),
    
    // Call REST API
    Source = Web.Contents(
        "https://dummyjson.com/auth/product?limit=0", 
        [
            Headers = [
                Authorization = "Bearer " & accessToken
            ] 
        ]
    ),
    Data = Json.Document(Source),
    products = Data[products],
    #"Converted to Table" = Table.FromList(products, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"id", "title", "description", "category", "price", "discountPercentage", "rating", "stock", "tags", "brand", "sku", "weight", "dimensions", "warrantyInformation", "shippingInformation", "availabilityStatus", "reviews", "returnPolicy", "minimumOrderQuantity", "meta", "images", "thumbnail"}, {"id", "title", "description", "category", "price", "discountPercentage", "rating", "stock", "tags", "brand", "sku", "weight", "dimensions", "warrantyInformation", "shippingInformation", "availabilityStatus", "reviews", "returnPolicy", "minimumOrderQuantity", "meta", "images", "thumbnail"})
in
    #"Expanded Column1"

For both endpoints (https://dummyjson.com/auth/login and https://dummyjson.com/auth/product), the authentication method configured in Data Source Settings is Anonymous.

My question is:

What is considered the recommended or best-practice approach for this scenario? I need to keep the authentication flow (username/password → bearer token → API request), but I don't want to store sensitive credentials directly in the Power Query source code.

I've been wondering whether the solution involves an Azure App Registration together with Azure Key Vault. However, I'm not sure whether that approach is even appropriate for a third-party API, or how such an API would fit into an Azure App Registration.

Is there a more common or simpler approach that I'm overlooking?

I'd appreciate any guidance or recommendations.

Thanks in advance!

[v-saisrao-msft]

Hi @Flo1,

Have you had a chance to review the solution shared by @Parchitect? If the issue persists, feel free to reply so we can help further.

Thank you.

[Parchitect · Accepted solution]

Hi @Flo1,

You've hit a structural limit, not a coding problem.

Power Query's credential store can't help your login call at all -- the Web.Contents documentation states it directly: POST requests may only be made anonymously. So any flow that starts with a credentials-in-body login POST will have those credentials living in the file, no matter how you arrange the M. The fix is moving the secret out of the report, and there are three clean ways to do it.

The one I'd recommend for a third-party API: put a thin proxy in front of it.

A small Azure Function holds the username and password (in app settings, or better, in Key Vault read through the Function's managed identity -- that's where Key Vault fits your picture), performs the login-and-token dance server-side, and returns the data. Your report then calls the Function like this:

Web.Contents(
    "https://yourfunc.azurewebsites.net",
    [ RelativePath = "api/products", ApiKeyName = "code" ]
)

and you select Web API as the authentication type, pasting the function key as the credential. Per the documentation, ApiKeyName takes the name of the key parameter while the actual value is provided in the credential, so the key sits encrypted in Power BI's credential store, never in your code, and refresh in the Service is cloud-to-cloud with no gateway.

The canonical Power Query answer is a custom connector.
It gives your API a real credential prompt, and the username/password land in the same encrypted store as any other data source. It's the right choice when you can't add Azure components, but budget for the development effort and for refreshing through a gateway with the connector deployed on it.

And if the customer has Fabric capacity:

Move the ingestion out of the report entirely — a pipeline or notebook makes the API call with the secret in Key Vault and lands the data in a Lakehouse; the report just reads a table.

On your App Registration idea: skip it. App registrations authenticate against Entra-protected APIs — your provider runs its own username/password scheme, so there's nothing for Entra to do here. And one non-solution worth naming since it comes up often: moving the credentials into Power Query parameters only relocates the plaintext inside the same file. It's tidiness, not security.

Reference: https://learn.microsoft.com/en-us/powerquery-m/web-contents

🔍Parchitect
Solutions Architect · Microsoft Fabric Specialist

💡Helpful? Kudos are appreciated.
✔️Solved? Mark as Solution so others can find it faster.