Issue with getting data via API with bearer token

[Anonymous]

Hi,

I am using an API that requires a bearer token embedded in the header that has a 30 min lifespan. I have constructed a Power Query ("M") that will pass the username and password to a Microsoft OWIN oauth token endpoint and this returns the required token correctly.

let
 url = #"Token URL",
 GetJson = Web.Contents(url,
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("username=XXXXXXX&password=XXXXXXXX&grant_type=password") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),

// Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken,
 GetJsonQuery = Web.Contents(#"Search URL",
     [
         Headers = [#"Authorization"=AccessTokenHeader]
     ]
 ),
FormatAsJsonQuery = Json.Document(GetJsonQuery)
in
    #"FormatAsJsonQuery"

I then try to pass that token through to a Web.Contents function to process the API query with the authorisation token to pull the required data into Power BI.

On running this I get a prompt saying "Access to the resource is forbidden. Edit Credentials". So I try setting the credentials to anonymous but it keeps returning "Access is forbidden". This is probably due to the requirement of the "Authorization" key to pass the token. My assumption is Power BI then tries to intercept this and provide the authorisation prompt but this will not work for my requirement.

I know the token that is returned is working for authentication as I can use it externally from Power BI to run a GET query effectively, I just can't make this function inside Power BI.

Is there something I'm missing? Researching this proves fairly light on details and contradictory answers for this use case so if someone could clarify any experience it would be appreciated.

Thanks.

[Anonymous]

Hi @Anonymous,

Hope you get solution for this issue,if you dont mind can share the code..im also facing the same issue

Thanks in advance.

[krenting]

The problem is that PowerBI.com first tries to load the json from the url given without the parameters. The return value will not be in the correct format because most API's will return an error. After failing this test it is impossible to refresh your data using your parameters.

let
 url = #"Token URL", <-- THIS URL NEEDS TO ALLWAYS RETURN THE SAME CORRECT DATA STRUCTURE EVEN WITHOUT THE HEADERS AND POST VALUES 
 GetJson = Web.Contents(url,
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("username=XXXXXXX&password=XXXXXXXX&grant_type=password") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),

I created a workaround by returning a json document containing fake data in the correct structure when the API was called without proper authentication. 

When you do this and set authentication to anonymous at PowerBI.com the first test will pass. PowerBI.com will now know that your API returns data in the correct format and will execute your Power Query during refresh returning the correct data.

Regards,

Kees Renting

[v-shex-msft]

Hi @Anonymous,

I think your formula based on below article, right?

Get Data from Twitter API with Power Query

Web.Contents method not support directly add username and password to content query.

Usually logic of request:

1.  Call login API to get the access token.

2.  Use access token call other operation api.

Sample:

    WebResponse = Web.Contents(WebServiceURI,
        [Content = Text.ToBinary(WebServiceContent),
         Headers = [Authorization="Bearer " & AccessToken,
                    #"Content-Type"="application/json",
                    Accept="application/json"],
         Timeout = WebTimeout])

In addition, API can allow "username", "password", "token" as the parameters to query string, but they must be defined in design.

For example:

RequestUri= BasUri?UserName:xxxxx&Passowrd:xxxxxx&Token:xxxxx

I'd recommend you take a look at the Official documentation and find out the correct way to call api.

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you Xiaoxin Sheng ( @v-shex-msft ),

The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.

To call a token I have to send a POST request with the username, password and grant_type embedded as content. The API does not accept a URI with parameters to return a token.

This works to return my token:

GetJson = Web.Contents(url,
     [
         Headers = [#"Accept"="application/json",
                    #"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
         Content = Text.ToBinary("username=uXXXXXX&password=pXXXXXX&grant_type=password") 
     ]
 ),
 FormatAsJson = Json.Document(GetJson),

// Gets token from the Json response
 AccessToken = FormatAsJson[access_token],
 AccessTokenHeader = "bearer " & AccessToken

 This will give me a variable (AccessTokenHeader) with the access token. If there is another way to do this please let me know.

[v-shex-msft]

Hi @Anonymous,

Can you share some detail content of the api which you want to invoke?

>>The logic you have provided is what I am trying to perform as the token has a 30 min lifespan and I can't get users of the report to manually enter a token each time.

You can write a custom function to get token which your account, then invoke this method before operation other api.

Regards,

Xiaoxin sheng

[Anonymous]

Hi @v-shex-msft,
 
Thanks for following this through. The instructions provided for the API are as follows:
 
1. Create an OAuth2 Session
Create a session and get a token (that you need to pass in your Web API request) using your user credentials by doing a “HTTP POST“ request on the URL. 

The base URL used for all operations is formatted as follows: https://{deployedAPIServer}/api/{resource}

 
The “https://{deployedAPIServer}/token/" Microsoft OWIN oauth token endpoint with parameters:
 
 "username", with value: the user login
 "password", with value: the user password
 "grant_type" : password (Resource Owner Password Credentials)
 
The content-type of the request should be: application/x-www-form-url encoded
 

Link to image of C# snippet provided in instructions

If the credentials are valid, the response would be a JSON object:
{"access_token":"..base64 encoded string","token_type":"bearer","expires_in":seconds}"
 
2. Call a Secure API
Call a secure rest API with token.
Set the returned token in your request header as a "Bearer" authentication and make the post, get, delete, .. HTTP call.

Link to image of C# snippet provided in instructions

Does this help?

[kailashkrishnan]

Hope this helps

let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",

GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),

FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,

GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))

in

GetJson1

[jlorenzo]

Hi aexley,

Have you tried with Bearer (in capital letters)? I spent hours with that and finally that was the stupid problem.
Now, my big problem is that this solution does not work me on the Power BI service.

Good luck!

[Anonymous]

@jlorenzo @kailashkrishnan @v-shex-msft thanks for your input.

Just realised there had been further commentary on this so I thought I'd better close out with my resolution.

@jlorenzoI had the same issue. Managed to get the functionality to work from Desktop, but when you push to service, it has authorization issues.

My workaround that I settled on was to make Azure Functions for the API calls which I have scripted in C# then I call the Azure Function as an API.

[kailashkrishnan]

Hi 

i had the same issue but with some tweaking i was able to get the post and get token together and work.

my issue was i needed token registeration and use the token as bearer token to get the api call

--------------------

hope this helps for some one.

------------------

let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",

GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),

FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,

GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))

in

GetJson1

[v-shex-msft]

Hi @Anonymous,

Sorry for slow response.

I haven't found a way to use power query invoke the sign in credentials, perhaps you can refer to below steps:

1. Package the sample code to a webservice.

   Sample : (just for reference)

 Public string GetToke(string UserName, string Password,string deployedAPIServer)
{
	string token="";
using (HttpClient client= new HttpClient())
{
	Client.DefaultRequestHeaders.Add("Accept","application/json");
	FormUrlEncodedConten credentialsContent =new FormUrlEncodedConten(new Dictionary<string,string> {{ "username",UserName},{"password",Password},{"scope","read"},{"grant_type","password"}};
	Task<HttpResponseMesage> requestTask= client.PostAsync("https://{"+deployedAPIServer+"}/api/token",credentialsContent);
	string jwt= requestTask.Result.Conten.ReadAsStringAsync().Result;
	token= (string)jObject["access_token"];
};
return token;
}

2. At query editor side, use web.content to invoke above service and store the access token.

Sample:

let
    username="xxxx",
    passowrd="xxxxx",
    apiurl="xxx.xxx.xxx",
    AccessToken= Web.Contents(WebServiceURL& "?GetToke("+username+&","&+passowrd+&","&+apiurl+&")")
in
    AccessToken

3. Use token to operation other functions.

Some useful links:

Understanding the Username-Password OAuth Authentication Flow

Regards,

Xiaoxin sheng

[kailashkrishnan]

Hi

i had the same issue which i was able to figure it with some tweak.

Like a post  request with a get with bearer token to get the data

hope this helps

let
url = "http://###########/Portal/api/v1/Token",
url1 = "http://#########/Portal/api/v1/myapi?FromDate=2017/09/29 01:00:00&ToDate=2017/09/29 23:00:00&",

GetJson = Web.Contents(url,
[
Headers = [#"Accept"="application/json",
#"Content-Type"="application/x-www-form-urlencoded;charset=UTF-8"],
Content = Text.ToBinary("grant_type=password&username=###########&password=###########&client_id=###########&client_Secret=###########")
]
),

FormatAsJson = Json.Document(GetJson),
AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer " & AccessToken,

GetJson1 = Json.Document(Web.Contents(url1 , [Headers=[Authorization= AccessTokenHeader ]]))

in

GetJson1