Forum Discussion

jnickell's avatar
jnickell
Helper V
1 year ago
Solved

Power Query custom connector with combined authentication types

I'm just getting started with creating a custom connector. My intent is to replicate a power query connection that's declared in the dataflow with a custom connector and more properly protect the cre...
Need Help
  • v-hashadapu's avatar
    v-hashadapu
    11 months ago

    Hi jnickell , Thank you for reaching out to the Microsoft Community Forum.

     

    Extension.CurrentCredential() can only return one credential record at a time, even though VS Code shows both UsernamePassword and Key stored. The connector runtime won’t merge them, so you can’t directly retrieve both Password and Key from the same call. That’s why your current approach fails. Greg_Deckler was right that you could hardcode the API key, but that weakens security. vojtechsima correctly said, you should either stick with Dataflows (where credentials are already protected in the service and hidden from end users) or move to Fabric Notebooks/Pipelines with Key Vault for stronger enterprise security.

     

    If you do want to continue with a connector, the usual pattern is to define a single authentication kind and pass the API key as a required parameter that you inject into the request header. That way you avoid mixing credential types, which isn’t supported. You weren’t wrong in thinking about connectors, but you’re not as exposed as you feared with Dataflows, they already give you a decent security boundary.

jnickell's avatar
jnickell
Helper V
11 months ago

Ok, this is interesting, and possibly exposing ignorance on my part. I've heard/read/watched what I feel are multiple resources about the 'no-no's' of putting creds in PowerQuery. In our use case all of these have been in the dataflows and only giving end users access to models derived from the dataflows. 
I was interpreting what I have been doing as risk and was pursuing the custom connector in part to pull the creds further "out of the service" and store them only in the custom connector accessed via the gateway.  

Your response gives me some hope that we might not have been as bad off as I thought.

vojtechsima's avatar
vojtechsima
Super User
11 months ago

jnickell 

Ideally, you’d run REST APIs in Fabric Notebooks or Azure Data Factory. Better security tooling, Key Vault, usually faster. If you can’t, here’s my checklist:

  1. If the API allows it, don’t use Anonymous. Use the built-in ApiKey auth in a Source Credential window.

  2. Don’t send credentials in the query string. Send them in headers only.

  3. Don’t hardcode keys or store them in M parameters. Those are accessible even through PBI Service without downloading the file (even tho it doesn't matter that much at that point).

  4. Use Dataflows. Store the credentials in the service and then call the Dataflow as a function from Power Query.

  5. If you have it on-prem, you can technically store it there as well.