Power BI custom connector Token exchange in header

[joseph_singh_fr]

Hello All,

I have built a Power BI cutom connector for a REST API SPIDER CMDB service. However, I am having issues with the authentication. My issue is made worst, as I need to be able to publish the report. The API work as follow:

1. To get token there are two method

        1. http(s)://{UserName}:{Password}@{ServerName}/{SpiderApiInstance}/token. This canbe called as such:

                   Response = Web.Contents(http(s)://Username:Password@{ServerName}/{SpiderApiInstance}/token)

        2.  You first must create a token bx converting to Base64 the Username:Password and pass it in a request header with Basic in front. Example of the code below:

                 FirstToken = "Basic " & Binary.ToText(Text.ToBinary("Username:Password"))

                 Response = Web.Contents(http(s)://{ServerName}/{SpiderApiInstance}/token, [Headers = [

                                                    #"Content-Type" =      "application/json",

                                                     #"Authorization" = FirstToken,

                                                     #"Accept" = "application/json",

                                                     #"Cache-Control" = "no-cache" ]]

           3. You would extract the final  token in the response header                

                    FinalToken =  Value.Metadata(Response)[Headers][Token]

The token is needed in the query request header and it is valid for 15min.  I got it to work with my connector by hardcoding the Username and Password and using Anonymous as an Authentication Method, but this will not work on Power BI reporting server. What is the best method to use for this type of authentication?

[joseph_singh_fr]

@v-yiruan-msft  Thank you for your help, your function worked flawlessly. Now that I have everything working, I need to make it compatile with Power BI online. I want the TestConnection method to check if I was able to receive a token or not, if the token it receive than success if the token is not there than failure.  I tried 

TestConnection = (dataSourcePath) => { "MyAPI.Contents" }

But it always succeed even when the username or password is false. Any idea?

[joseph_singh_fr]

Thank you for your reponse. I will give it your funcations a go. However, i was reviewing the documentation, and one can obtain a token via  http(s)://{UserName}:{Password}@{ServerName}/{SpiderApiInstance}/token is this easier to implement

[v-yiruan-msft · Accepted solution]

Hi @joseph_singh_fr ,

Instead of “hardcoding” user name and password and then using Anonymous authentication, you should have your connector obtain the credentials at run‐time (from the data source settings) and then perform the token exchange in M. First create the function below to obtain the credentials that the user has supplied and then calls the token endpoint by sending a proper header:

GetFinalToken = (Server as text, SpiderApiInstance as text) as text =>
  let
    // Get the credentials from the data source settings.
    // When using “Basic” authentication, Extension.CurrentCredential() returns a record with Username and Password.
    Credentials = Extension.CurrentCredential(),

    // Extract username and password
    Username = Credentials[Username],
    Password = Credentials[Password],

    // Create base64-encoded Basic header (make sure to choose Base64 encoding)
    BasicToken = "Basic " & Binary.ToText(Text.ToBinary(Username & ":" & Password), BinaryEncoding.Base64),

    // build the URL to the token endpoint; note: do not embed credentials in the URL
    TokenUrl = "https://" & Server & "/" & SpiderApiInstance & "/token",

    // Make the call to the token endpoint.
    // Notice that we include a set of headers (including our Auth header) for our token request.
    TokenResponse = Web.Contents(TokenUrl, [
         Headers = [
            #"Content-Type" = "application/json",
            #"Authorization" = BasicToken,
            #"Accept" = "application/json",
            #"Cache-Control" = "no-cache"
         ]
    ]),

    // Extract the metadata headers which include the final token.
    ResponseHeaders = Value.Metadata(TokenResponse)[Headers],
    FinalToken = ResponseHeaders[Token]
  in
    FinalToken;

Then in the main query function call GetFinalToken to obtain the token and use it for subsequent calls:

MyAPIQuery = (Server as text, SpiderApiInstance as text, resourcePath as text) as any =>
  let
    // Get the final token using the helper function.
    FinalToken = GetFinalToken(Server, SpiderApiInstance),
    
    // Now build the header that will be passed when calling the secured resource.
    AuthTokenHeader = "Bearer " & FinalToken,
    
    ResourceUrl = "https://" & Server & "/" & SpiderApiInstance & "/" & resourcePath,
    
    // Make the secured call with the token in the header.
    Response = Web.Contents(ResourceUrl, [
         Headers = [
            #"Content-Type" = "application/json",
            #"Accept" = "application/json",
            #"Authorization" = AuthTokenHeader
         ]
    ]),
    Result = Json.Document(Response)
  in
    Result;

Best Regards