Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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.  

  • Anonymous's avatar
    Anonymous
    6 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

  • artemus's avatar
    artemus
    Microsoft 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.

    • Anonymous's avatar
      Anonymous
      Not 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?

      • artemus's avatar
        artemus
        Microsoft 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.