How to securely store and use token and secret for API request in Power Query M ?

[PunchBird]

Hello all,

I have successfully connected to an online service with an API request with token and secret, see example below. However, I believe storing the token and secret in the Power Query M code (or in a parameter) is not very secure, so I am looking for a more secure option. Does anyone know what is the best solution here? Can I use Azure Key Vault (if yes, how?). Or is there another way to do this securely? Many thanks!

let
  url = "https://url_of_service/",
  token = "<MY_TOKEN>",
  secret = "<MY_SECRET>",
  authString = token & ":" & secret,
  authBytes = Text.ToBinary(authString),
  encodedAuth = "Basic " & Binary.ToText(authBytes, BinaryEncoding.Base64),
  body = "{ ""profiles"": [ { ""id"": ""<PROFILE1>"", ""id2"": ""<PR1>"" }, { ""id"": ""<PROFILE2>"", ""id2"": ""<PR2>"" } ], ""date_start"": ""2023-01-01"", ""date_end"": ""2023-01-31"", ""metric"": ""metric1"", ""dimensions"": [ {""type"": ""date.day""}, {""type"": ""id""} ]}",
  options = [
        Headers = [
            #"Authorization" = encodedAuth,
            #"Content-Type" = "application/json; charset=utf-8"
        ],
        Content = Text.ToBinary(body)
    ],
  response = Web.Contents(url, options),
  data = Json.Document(response),
  #"Converted to table" = Record.ToTable(data)
in
data

[ams1]

Hi @PunchBird ,

Great question - indeed using hardcoded credentials in the query is NOT secure.

There is a quick-and-maybe-not-so-dirty workarround:

Context:

If you do...

let
    Source = Web.Contents("http://localhost:5000/test", [ApiKeyName="howdy"])
    //                                                  ^^^^^^^^^^^^^^^^^^^^ this is just the NAME of the key (NOT the key secret)
in
    Source

...and then store the credentials securely in the PowerQuery "credentials manager"...

...PowerQuery will send to the webserver the following GET web request:

                                 vvvvv - notice the key SECRET from credentials manager
http://localhost:5000/test?howdy=12345
                           ^^^^^ - notice the key name from ApiKeyName

So PowerQuery sends the API key name and secret as GET query parameters.

Problem:

Now MOST of us need that api key secret inside the Header (NOT as query parameters).

A solution:

Well, one way to get that secret is to have a SUPER-SIMPLE web server somewhere (local, intranet, lambda, azure function etc.) that just extracts the secret from the query parameter and just sends it back in the response - I can provide ex. the python code to run on localhost if you want.

With that "intermediary boomerang server" 😊, your code would look like:

let
    // below is the web request that uses the token from PowerQuery credentials manager
    // sends it as query parameter to the "boomerang" server which just sends it back in the response 
    token = Web.Contents("http://localhost:5000/boomerang", [ApiKeyName="howdy"]), // 1st request with apy key credentials
    actualWebRequstResponse = Web.Contents( // 2nd request with annonymous credentials
        "http://api_that_uses_header_authorization",
        [
            Headers = [
                // below i use the token from the intermediary
                Authorization = Text.FromBinary(token) // if the token request returns just the key as text
            ],
            // POST requests also work with apy key this way!
            Content = Text.ToBinary("how are you?")
        ]
    )
in
    actualWebRequstResponse

Why do we need to go to these extra lengths to use the PowerQuery credentials manager for Header authorization (majority of APIs)? I don't know - I'd actually be curious to get an answer.

The thing is there is a workarround which will be as secure as the boomerang server.

FYI: @troyjsullivan 

Please mark also this as answer if it helped.

[Dops0]

@ams1 Ha! I just did exactly this before seeing your post. This method works fine in PBI Desktop. Unfortunately, the Web API Credential method is not supported in PowerBi Service even though it is 2023 and the whole world is using REST APIs with Authentication! 

I really hope I am wrong here and someone will correct me 🙂

[PunchBird]

@ams1 thanks for your additional input! I indeed need that api key secret inside the Header. Your proposal sounds like it could work, but at this point I don't know if we can set up a web server for this. I am just thinking out loud... could I not extract the secret from the query parameter in the query itself and then add it to the Header?

[ams1]

@PunchBird indeed, there is the extra hassle of the boomerang server - the code itself is SUPER simple and runs in milliseconds, you just need somewhere secure to serve it (like I've said - a lambda/azure function would be more than enough).

The ideea is that instead of running 1 x request and have PowerQuery handle the authentication, you first run a request that gets you everything* you need (token, credentials etc) to authorize the 2nd request.

*in this regard you could also refresh the token/get a new one, do ouath etc. - with additional code on the boomerang server side -> including getting things from Azure Key Vault.

Note that as far as I know PowerQuery does NOT "see" the "compiled" web request (and thus is not able to extract the web query parameters), only the webserver does - so that's why you need it.

P.S.: Please mark my previous reply as solution if it helped (and maybe kudo just to raise awareness - hopefully we'll get this feature) 😉

[PunchBird]

Thanks for this, I will have a look into this. If you have the Python code available to run in an Azure functions, could you please share it with me? 🙂

[ams1]

Hi @PunchBird 

I wanted to do something more elaborate like multi-hosting/multi-cloud (maybe I'll do it later), but for now, I think the below chatGPT "boomerang server" snippet should get you started:

give me the python code for an azure function with a web endpoint that returns all query parameters as json

import json
import logging

import azure.functions as func


def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    # Get all query parameters from the request
    query_params = req.params

    # Convert the query parameters to a JSON object
    json_params = json.dumps(query_params)

    # Return the JSON object as an HTTP response
    return func.HttpResponse(json_params, mimetype='application/json')

Haven't tested it, but that's should be the WHOLE code needed.

To use above inside the query you'd do something like:

...
            Headers = [
                // below i use the token from the intermediary
                Authorization = Json.Document(token)[howdy]
                //                                   ^^^^^ - name of API key
            ],
...

IMPORTANT NOTE: above code does not check for any access permissions (on the other hand it just returns what is sent to it) so you have to take care of securing it, ex.: https://learn.microsoft.com/en-us/azure/azure-functions/security-concepts?tabs=v4

There are many ways to use this - ex if you need to store more than 1 secret in the PowerQuery credentials manager you could concatenate them using a character of choice and store them in the single apiKey slot (and split them later in PowerQuery OR split them in the above function...).

Please also consider marking my initial answer as ANSWER if it helped so that other users find it more easily.

[PunchBird]

Many thanks! I will look into this further and when we decide to take this approach I will let you know our test results.

[artemus]

Your best bet it to add the option: ApiKeyName = "Authorization". However, you would need to encode the encoded authorization by hand. When you are prompted for credentials, select ApiKey for login type.

let
  url = "https://url_of_service/",
  body = "{ ""profiles"": [ { ""id"": ""<PROFILE1>"", ""id2"": ""<PR1>"" }, { ""id"": ""<PROFILE2>"", ""id2"": ""<PR2>"" } ], ""date_start"": ""2023-01-01"", ""date_end"": ""2023-01-31"", ""metric"": ""metric1"", ""dimensions"": [ {""type"": ""date.day""}, {""type"": ""id""} ]}",
  options = [
        Headers = [
            #"Content-Type" = "application/json; charset=utf-8"
        ],
        Content = Text.ToBinary(body),
        ApiKeyName = "Authorization"
    ],
  response = Web.Contents(url, options),
  data = Json.Document(response),
  #"Converted to table" = Record.ToTable(data)
in
data

[PunchBird]

Thanks! Would this work with a scheduled refresh?

[artemus]

It would last as until the token expires. When this happens you will get an email and need to provide a new updated token.

[PunchBird]

@artemus I tried your suggestion but I get this error message, any idea how to fix it?

[artemus · Accepted solution]

Oh, sorry about that... didn't realize that POST didn't support authentication in Web.Contents. I don't know any use of keyvault, and you would likely run into the same issues you face here using it.

I think the only option you would have is to write your own connector, which is given much more freedom on how it operates. Note, that if you write your own connector you cannot publish your report online unless you use a gateway (in which case you might as well just store the secret on the gateway machine and configure access to that speratly). Also, note that this does not work with importing data into Excel.

[PunchBird]

That's a bummer... but thanks for your useful info!

[PunchBird]

Thanks, I am going to test this and will let you know if it works!