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

Hi Anonymous ,

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: Anonymous 

Please mark also this as answer if it helped.