Power Query custom connector with combined authentication types

[jnickell]

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 credentials.

My problem is that the authentication process for this API (UKG) requires both username, password and an API Key.  In my custom connector, the Extension.CurrentCredential() function only appears to be able to return the most recently entered credential. Am I missing something?

This is the relevant snippet from the working Power Query authentication pattern.

LoginAuthURL = "https://secure.saashr.com/ta/rest/v1/login",
           
        PostBody = [
            credentials = [
                company = "companyString",
                username = "Username",
                password = "UserPassword"
            ]
        ],
        Header = [#"Content-type"="application/json",
                    #"Api-Key" = "TenantAPIKey"                
                  ],
        out = Json.Document( Web.Contents(LoginAuthURL, [Content=Json.FromValue(PostBody),Headers=Header])),

Appreciate any pointers

In VS Code, List Credentials shows 

[Info]	ListCredentials result [
  {
    "DataSource": {
      "Kind": "UKGConnector",
      "NormalizedPath": "UKGConnector",
      "Path": "UKGConnector"
    },
    "AuthenticationKind": "Key",
    "PrivacySetting": "None",
    "Properties": {
      "Key": "TenantAPIKey"
    }
  },
  {
    "DataSource": {
      "Kind": "UKGConnector",
      "NormalizedPath": "UKGConnector",
      "Path": "UKGConnector"
    },
    "AuthenticationKind": "UsernamePassword",
    "PrivacySetting": "None",
    "Properties": {
      "Username": "username",
      "Password": "******"
    }
  }
]

 But calling this in the custom connector 

creds = Extension.CurrentCredential()[Key]

Returns this error message

"The field 'Key' of the record wasn't found."

[jnickell]

@Greg_Deckler @vojtechsima @v-hashadapu  - thanks to each of you for your insight and for straying off the original topic a little.  Much appreciated. 
Can only accept one as solution, so accepted @v-hashadapu since it answered the original question about accessing credentials within the PQ Connector

[v-hashadapu]

Hi @jnickell , hope you are doing great. May we know if your issue is solved or if you are still experiencing difficulties. Please share the details as it will help the community, especially others with similar issues.

[vojtechsima]

hey, @jnickell 

You can't really call an external extension, especially when you want to then use it PBI Service.

If you want a bit more secure stuff, you can build the authentication header and the API token inside Dataflow. And then inside Power Query in PBI, call the Dataflow and use the response as the parameters for your API call.

If someone without credentials to Dataflow accesses the .pbix (assume Power BI) file, they won't see the credentials there, as it requires authorisation to Dataflow.

If you want an extra "fake" security layer, store the dataflow call as a function and never return it inside a variable and then it won't be materialised in any step.

[jnickell]

@vojtechsima could you comment on my previous reply to tell me if I'm off base in my conclusion?

[v-hashadapu · Accepted solution]

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]

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]

@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.

[Greg_Deckler]

@jnickell Well, this will depend on how the API works but what I am thinking is that you need to skip the AuthenticationKind Key and essentially hard-code your API Key in the custom connector and then just include it as necessary in the request header. But, without more information about what API you are trying to connect to it's a complete guess. Note that I have found success using ChatGPT to point it to an API's documentation and generate the custom connector code for me. Generally only requires a bit of tweaking to get it working. Something you might try.

[jnickell]

Hi @Greg_Deckler ,

  The docs for the API are here: https://secure6.saashr.com/ta/docs/rest/public/, but they don't make the 'v1' immediately obvious, but I think this takes you there: https://secure6.saashr.com/ta/docs/rest/public/?r=__v1__login#post

I think the larger part of my question is how or if you can store multiple types of credentials in a custom connector and retrieve them.  I'm not seeing the documentation spell this out, but again I'm new to this part of PowerQuery.

[Greg_Deckler]

@jnickell Well, if it is an API key, you just store it as text in a file that is part of the connector. I mean, you can. Not the safest obviously. 

[jnickell]

I guess this is more secure than storing the entire credential set in PQ service. Was hoping I could say I had fully banished this one security hole by implementing a connector.