Oauth2 flow with custom connector requiring username and password

[halfglassdarkly]

Hi all, long time lurker first time poster.

I usually try to solve things myself but this currently has me stumped. Apologies in advance for the long-winded post.

I'm trying to get PowerQuery/PowerBI to connect to the New Zealand Business Number (NZBN) API using a 2 legged OAuth 2 flow with client_credentials. In my test query I am using their test API call called MBIE Echo which takes an input string and returns it.

I have got this working successfully in Postman, and with the aid of Jussi Roine's useful tutorial I have managed to get this working in PowerQuery by writing a custom connector using the Power Query SDK for Visual Studio.

In PowerQuery I am able to connect successfully to the API including initiating a token refresh, and it is returning data as expected.

However, in order to connect, PowerQuery is prompting me to authenticate with my user name and password for the RealMe identification service that the NZ Government uses for a number of websites, and which is used by the Ministry of Business, Innovation & Employment (MBIE) who own the API, when accessing the API console via their website.

Using Fiddler for traffic inspection, I can see this is hitting the login.realme.govt.nz server which returns some cookies and then refers me to api.business.govt.nz and then on to oauth.powerbi.com for the OAuth redirect...

The query then runs successfully and returns the expected response. It will also refresh fine. The problem is, if I make any change to the query, it requires re-authorization with username and password. This includes when using parameters to change the content of the get request to MBIE Echo - if I create a custom function out of the query to pass parameters to the query from another table I am prompted to reenter username and password for every record for which the function is invoked, which obviously makes this unusable in a real-world situation.

I'm very new to working with OAuth 2 and only have a vague idea what I'm doing, but it seems to me like maybe this is an issue with PowerQuery not storing/reading the cookie returned during the RealMe authentication step, or maybe there is a way to bypass this authentication step entirely that I am missing when writing my connector. In Postman I am able to initiate the OAuth token refresh and MBIE Echo API calls using the client_id and client_secret without being prompted for the RealMe username and password as well, so I would have thought the same should be able to be implemented using the PowerQuery connector?

In case it made a difference I have set my PQ/PBI Security settings to not check for certificate revocation information, and I also  enabled the 'allow any extension to load without validation or warning' before using the custom connector.

Note: for some types of API requests MBIE does require a 3 legged OAuth flow, but for the type of calls I am working with 2 legged flow should be all that is required. See :

https://api.business.govt.nz/api/apis/info?name=NZBN&version=v4&provider=mbiecreator and

https://support.api.business.govt.nz/s/article/oauth2-authentication

Below is the text from the .pq file from my custom connector. The client_id and client_secret are stored in seperate text files.

section NZBN_Register;
// NZBN_Register OAuth2 values
client_id = Text.FromBinary(Extension.Contents("client_id.txt"));
client_secret = Text.FromBinary(Extension.Contents("client_secret.txt"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";
token_uri = "https://api.business.govt.nz/services/token?grant_type=client_credentials";
authorize_uri = "https://api.business.govt.nz/services/authorize";
logout_uri = "https://login.microsoftonline.com/logout.srf";
// Login modal window dimensions
windowWidth = 720;
windowHeight = 1024;
[DataSource.Kind = "NZBN_Register", Publish = "NZBN_Register.Publish"]
shared NZBN_Register.Contents = (url as text) =>
let
source = Json.Document(Web.Contents(url))
in
source;
// Data Source Kind description
NZBN_Register = [
TestConnection = (dataSourcePath) => {"NZBN_Register.Contents", dataSourcePath},
Authentication = [
OAuth = [
StartLogin = StartLogin,
FinishLogin = FinishLogin,
Refresh = Refresh,
Logout = Logout
]
],
Label = Extension.LoadString("DataSourceLabel")
];
// Data Source UI publishing description
NZBN_Register.Publish = [
Beta = true,
Category = "Other",
ButtonText = {Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp")},
LearnMoreUrl = "https://powerbi.microsoft.com/",
SourceImage = NZBN_Register.Icons,
SourceTypeImage = NZBN_Register.Icons
];
// Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout
StartLogin = (resourceUrl, state, display) =>
let
authorizeUrl = authorize_uri
& "?"
& Uri.BuildQueryString(
[
response_type = "code",
client_id = client_id,
redirect_uri = redirect_uri,
content_type = "application/x-www-form-urlencoded",
state = state,
scope = "am_application_scope default",
Accept = "application/json"
]
)
in
[
LoginUri = authorizeUrl,
CallbackUri = redirect_uri,
WindowHeight = 720,
WindowWidth = 1024,
Context = null
];
FinishLogin = (context, callbackUri, state) =>
let
// parse the full callbackUri, and extract the Query string
parts = Uri.Parts(callbackUri)[Query],
// if the query string contains an "error" field, raise an error
// otherwise call TokenMethod to exchange our code for an access_token
result =
if (Record.HasFields(parts, {"error", "error_description"})) then
error Error.Record(parts[error], parts[error_description], parts)
else
TokenMethod("authorization_code", "code", parts[code])
in
result;
Refresh = (resourceUrl, refresh_token) =>
TokenMethod("refresh_token", "refresh_token", refresh_token);
Logout = (token) => logout_uri;
// see "Exchange code for access token: POST /oauth/token" at https://cloud.ouraring.com/docs/authentication for details
TokenMethod = (grantType, tokenField, code) =>
let
queryString = [
grant_type = "client_credentials",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = Record.AddField(queryString, tokenField, code),
tokenResponse = Web.Contents(
token_uri,
[
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]
),
body = Json.Document(tokenResponse),
result =
if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;
Value.IfNull = (a, b) => if a <> null then a else b;
GetScopeString = (scopes as list, optional scopePrefix as text) as text =>
let
prefix = Value.IfNull(scopePrefix, ""),
addPrefix = List.Transform(scopes, each prefix & _),
asText = Text.Combine(addPrefix, " ")
in
asText;
NZBN_Register.Icons = [
Icon16 = {
Extension.Contents("NZBN_Register16.png"),
Extension.Contents("NZBN_Register20.png"),
Extension.Contents("NZBN_Register24.png"),
Extension.Contents("NZBN_Register32.png")
},
Icon32 = {
Extension.Contents("NZBN_Register32.png"),
Extension.Contents("NZBN_Register40.png"),
Extension.Contents("NZBN_Register48.png"),
Extension.Contents("NZBN_Register64.png")
}
];

And this is the test query I am running in PowerQuery using the MBIE Echo API:

let
Source = NZBN_Register.Contents("https://sandbox.api.business.govt.nz/services/v1/echo/echoString?in=hello world"),
echoStringResponse = Source[echoStringResponse],
#"Converted to Table" = Record.ToTable(echoStringResponse)
in
#"Converted to Table"

Which returns my hello world text back as a string before being converted to a table.

If I attempt to change the query, e.g. setting the echo string to "new string"

Source = NZBN_Register.Contents("https://sandbox.api.business.govt.nz/services/v1/echo/echoString?in=new string")

I will get the edit credentials error prompt:

On signing in the query runs as expected.

It's really frustrating getting this far with successful API calls but not knowing how to proceed in dealing with these credentials prompts. Any help would be most appreciated! - Daniel

[halfglassdarkly]

So I haven't had concentrated time this week to solve this yet, but @ImkeF's suggestion has definitely pointed me in the right direction.

I tried implementing a relative path in the query per the suggested blog post, and it returned an error (which I failed to save the text of but essentially: invalid number of arguments, expected 1 received x).

Since I'm using a custom connector, I've come to the conclusion I need to make changes to the connector rather than my query so queries using the connector expect multiple inputs (query string of URL based on parameter).

The section of this article on excluding required parameters from your data source path covering was helpful, and I was able to successfully replecate their example of excluding parameters from authentication in the HelloWorldWithDocs tutorial connector using 'DataSource.Path = false'. I knew I shouldn't have skipped the Hello World tutorial ><.

Now I need to work out how to implement that in my connector as that tutorial doesn't cover how to implement this for the query string in a URL in a connector. I'm sure I will get there eventually, but any suggestions appreciated.

As best I can tell, this is the section of my custom connector .pq file that I need to modify?

let
queryString = [
grant_type = "client_credentials",
redirect_uri = redirect_uri,
client_id = client_id,
client_secret = client_secret
],
queryWithCode = Record.AddField(queryString, tokenField, code),

tokenResponse = Web.Contents(token_uri, [
Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
Headers = [
#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"
],
ManualStatusHandling = {400}
]),
body = Json.Document(tokenResponse),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
body
in
result;

[ImkeF]

Hi @halfglassdarkly ,

try to split up the url into a main part (which should be the same for all calls and hopefully controls the auth) and eiter a relative path or query parameter.

You can see both methods desribed (and a lot of useful links) in this blogpost : http://blog.datainspirations.com/2018/02/17/dynamic  (Web view)

[halfglassdarkly]

That's great, thanks @ImkeF I will give that a go and report back.