Connecting to REST API with Secret Key

[DRoberts]

Goal

To pull data into PowerBI using Power Query Editor from a 3rd party REST API which authenticates with a key in the request header. I'd like this key to not be directly visible in the code for security reasons.

Background

The API handles authentication through a key which is passed in GET method's header ("Ocp-Apim-subscription-Key" parameter).

The following works (replacing the "abc_example_key_xyz" with a string that is my actual API key). I am propted to click "edit credentials" and when selecting the "Anonymous" connection option I successfully get the desired data back:

response = Web.Contents("https://api.example.com/V1/company/GetAll", [Headers=[#"Ocp-Apim-subscription-Key"="abc_example_key_xyz"]])

Now I'd like to obfuscate / hide / replace the key in the query with a secret (or some other method).

Instead of selecting an Anonymous connection, the Web API option allows me to enter a key. However, unless I include the field ApiKeyName in the request I get the error "A web API key can only be specified when a web API key name is provided":

But if I update the request to include the ApiKeyName field the authentication fails (obviously, because it's not what the API wants):

response = Web.Contents("https://api.example.com/V1/company/GetAll", [ApiKeyName = "abc_example_key_xyz"])

I tried some other funny tests like "tricking" it into pulling a secret key from the Web API key interface and using it in the head, e.g.:

response = Web.Contents("https://api.example.com/V1/company/GetAll", [ApiKeyName = "api_key", Headers=[#"Ocp-Apim-subscription-Key"=ApiKeyName]])

... but it didn't work, I interestingly get the error "Expression.Error: The 'Ocp-Apim-subscription-Key' header is only supported when connecting anonymously" - which would have been useful information upfront (I can't believe this is that much of a fringe case?)

Help

So, has anyone successfully used a GET request using a header / ocp-apim-subscription-key authentication which is kept secret?

Can anyone point me in the right direction? (I've been down a terrible Azure Key Vault Secrets rabbit hole and would rather avoid if possible!)

Thank you!

[Anonymous]

Hi @DRoberts ,

Please try this solution in this post:

Solved: Re: Get data from web error: A web API key can onl... - Microsoft Power BI Community

Best Regards,

Stephen Tao

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

[DRoberts]

Hi Stephen,

Thanks for the response, unfortunately I couldn't get it work.

The post you refer to has the following marked as a solution:

---

https://dev.applicationinsights.io/documentation/Using-the-API/Power-BI

"In the Access web content dialog, select Basic, enter your app's API key as the Username, and press Connect"

---

However that url doesn't appear to work anymore, instead it redirects to:

https://docs.microsoft.com/en-gb/azure/azure-monitor/logs/api/overview

and I don't see the bit quote about using the API key as the username. I'm unsure if I can apply any of the steps in the article (which admittedly may just be my lack of understanding).

In any case I attempted the suggestion of using the API key in the username field in a Basic connection and it still failed:

The interface doesn't let me proceed any further if the authentication fails, so I'm also unable to see if this would solve the issue of obfuscating the API key within the query code.