Power Bi Query to API with Basic Authentication -

[jeffg]

Working on a query to pull data into Power BI via FireEye Api.   I have confirmed authentication and connectivity in Python, but having troubles getting it to work in a Power Query.  

I am getting an "Expression.Error:  The 'Authorization' header is only supported when connecting anonymously".  Have done the research, tried may variaitions, still stuck.   Script is below.... what am i missing?   

jeff

************************************************************************************

let

// Concatenates key and converts to base64

//authKey = "Basic" & Binary.ToText(Text.ToBinary(#"username " & ":" & #"password "),0),

authKey = "Basic """ & "amVmZi5nOm51djJ0SGVzDQo=""" ,

url = ("https://10.35.90.4:3000/hx/api/v1/token"),

// Uses the HX POST token method to obtain a bearer token
header = [#"Authorization"=authKey,
#"Content-Type"="application/json"],

response = Web.Contents(url, [Headers=header]),
FormatAsJson = Json.Document(response),

//FormatAsJson = Json.Document(GetJson),

// Gets token from the Json response

AccessToken = FormatAsJson[access_token],
AccessTokenHeader = "bearer" & AccessToken,

// Uses the GET data using the token from the previous POST token method

GetJsonQuery = Web.Contents("https://10.35.90.4:3000/hx/api/v1/hosts)",

[
Headers = [#"Authorization"=AccessTokenHeader]
]

),

FormatAsJsonQuery = Json.Document(GetJsonQuery)

in

#"FormatAsJsonQuery"

*********************************************************

[v-ljerr-msft]

Hi @jeffg,

This is not possible due to constraints with the way Web.Contents stores credentials. They are stored based upon the URL value passed, and there can only be one URL per Dataset. In this case, you're trying to short-circuit a typical oAuth2 flow (one URL) and call the API (another URL). I made an attempt at using RelativePath to 'trick' the service, but stills require that the root of those two endpoints returned a valid HTTP 200 response to indicate connection success. I

Have you tried the solution using RelativePath along with your root url as mentioned in this similar thread above? 

let
 authKey = "{""Username"":""USERNAME"",""Password"":""PASSWORDGOESHERE"",""LocationID"":""LOCATIONIFNEEDED""}",
 url = "https://10.35.90.4:3000",
 // Uses the authentication/token method to obtain a token
 GetJson = Web.Contents(url,
     [
         Headers = [#"Content-Type"="application/json"],
         Content = Text.ToBinary(authKey),
         RelativePath="/hx/api/v1/token"
     ]
 )
in
    GetJson

Regards

[jeffg]

Guess this falls under the good news/bad news category.  I am happy to hear why it isn't working and that I didn't miss anything.  

I did try using RelativePath and have confirmed that it doesn't 'trick' the service.  

Time to try the full oAuth2 workflow and/or push the data into a SQL table and pull it from there.  

thx, 

jeff

[jeffg]

Tried several alternative scripts to get Power BI to authenticate and not doing so well.  

Does someone have a Power BI script/token method sample that may help? 

thx, 

j

[didijaba]

Hi, did you find solution for this? I am working on same thing, and can not figure it out, same problem as yours.