Forum Discussion
Oauth 2.0: Session API Token Request Fails with Special Chrs in Body?!? (Rest API)
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.
- Anonymous6 years ago
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
10 Replies
- artemusMicrosoft Employee
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.
- AnonymousNot applicable
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?
- artemusMicrosoft Employee
See the sample here: https://github.com/microsoft/DataConnectors/blob/dbd99078501897e04e7a6b9305ad7857ae1b7a63/samples/Github/github.pq
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.