Oauth 2.0: Session API Token Request Fails with Special Chrs in Body?!? (Rest API)

[Anonymous]

Hi there,

First... Thank you all for the earlier help!  What a great community!  I'm so close here...  Nevertheless, I'm continuing to struggle with one last bit in obtaining a "session" token from an API within Power Query.  I had a post earlier about it, (thanks to @artemus for a start), but to save others the hassle, where we landed I wanted to summarize it in a new thread (I'll link back to this one). 

I highly "suspect" the problem might do with the special values embedded in the string.  In the code below, VALUE1 contains "!" (i.e. abc123!) and VALUE3 contains "+" and "=" (i.e. abc+123=).  I can't get ride of either of these.  If it isn't the special characters, it is something like a missing character or flag.

Any thoughts would be much appreicated!  I just got dumped this in my lap and am trying to learn and address it at the same time.

RefreshAPIToken = () as text =>
        let token_url = "https://DOMAIN/api/Token",
    body="Username=VALUE1&Password=VALUE2&grant_type=password&Integrated=N&database=VALUE3&Client_Id=VALUE4=&client_secret=VALUE5", 

params =
        [ 
            Headers = [#"Content-Type"="application/x-www-form-urlencoded", #"Accept"="*/*", #"Accept-Encoding"="gzip, deflate"],
            Content = Text.ToBinary(body)
        ],
    WebSource = Json.Document(Web.Contents(token_url,params)),
    apikey = WebSource[access_token]
   
    in
        apikey

PLEASE NOTE: Tried Text.AsciiEncoding and BuildQueryString using same string of text.  Also added a few parameters to ToBinary (such as Base64 and Compress).

The code runs fine in PostMan, and if I retrieve the session token from PostMan, I can plug it in and pull my data in Power BI.  I'm getting a 400 reply error, but I am not sure how to debug what is being sent by Power BI, nor the specific error message details being received.  If I don't add "Text.ToBinary", Power BI fails saying it can't convert to binary.

According to PostMan I need to do Oauth 2.0 and "Request Headers" (as opposed to URL") in authorization data.  I'm not sure where or if I need to setup either of these within Power BI.  Power BI is setup for an anonymous connection.  

[artemus]

Is writing an extension an option, as custom data connectors have direct support for OAuth?

This is an option if:

1. You plan to only use Power Bi Desktop or if you publish online, you will not use their scheduled refresh.

or

2. You can deploy an enterprise gateway.

[Anonymous]

I have Power BI Desktop, but I would need some handholding walking through that a bit.  I'm currently on the phone arguing this API is not working.  We are certain the way it is setup is correct, no?

[artemus]

See the sample here: https://github.com/microsoft/DataConnectors/blob/dbd99078501897e04e7a6b9305ad7857ae1b7a63/samples/Gi...

To develop/use:

1. Optional: Install Visual studio and get the power bi extension.

2. Write code

3. In visual studio: build code, find the .mez file in your project's bin folder

or

3. Create a zip file of your .pq file and rename it to .mez

4. Copy file to :<home directory>\Power BI Desktop\Custom Connectors\

5. In Power bi go to options -> security and enable loading of custom connectors.

6. Connector should appear when you  choose GetData

Note if you modify the .mez file you will need to do a refresh preview to see the changes.

[Anonymous]

So... I'm guessing, but would that connector would mean the report has to reside on the desktop at all times?!?   While I have Power BI Desktop, we need the reports to run in the cloud on Teams and such.

The code looks nearly identical to what we have already written...

Are we able to rule out it isn't the special characters, compression, or encoding causing the trouble?

[artemus]

I think the main difference is the headers passed in. I.e. it says use Json

As far as I mentioned, if you use a custom connector and you want it to refresh itself, you would need to set up an enterprise gateway which would let you install the custom connector on it.

[Anonymous]

Where do you see it saying "use Json"?  In the code you linked or elsewhere?  I could give that a try...

[artemus]

It is here (line 154): https://github.com/microsoft/DataConnectors/blob/dbd99078501897e04e7a6b9305ad7857ae1b7a63/samples/Gi...

The request headers are different in this example

[Anonymous]

That's a great call out.  I tried it, didn't help or hurt.  Reviewing the API, I think it has to do with URL encoding, but I don't know the syntax.  Right now it reads:

body = Uri.BuildQueryString([Username="VALUE1", Password="VALUE2!", grant_type="password", Integrated="N", database="VALUE3", Client_Id="VAL+UE4=", client_secret="VALUE5"]),

I'm concerned about the blue highlighted values.

The API provides an encoding chart and also mentions some values might need to be URL-escaped (looking that up now).  But doesn't provide syntax or context on either one.

Special CharacterEncoded Value

space	[ ]
.	[.]
*	[_ASTERISK_]
/	[_$2F_]
\	[_$5C_]
&	[_$26_]
?	[_$3F_]
+	[_$2B_]
=	[_$3D_]
#	[_$23_]
%	[_$25_]
[	[_$5B_]

[artemus]

Use the Uri.EscapeDataString function to do the escaping

[Anonymous · Accepted solution]

@artemus , thank you for all of your help troubleshooting this issue!!!  It's insanely valuable and been absolutely appreciated.  I wanted to let you (as well as others that see this thread) know that we were able to resolve the issue.  I contacted the vendor after our multiple attempts and it was determined the API had a bug.  Your code was 100% correct and works flawlessly!

Thank you again!

-Brett