Custom Authentication in Custom Data Connector

[nzimmerman]

Trying to build a custom data connector for a website that uses an authentication flow that doesn't neatly fit in with the default options.

The authentication flow is like this:

1. send a GET request to https://www.example.com/api/authenticate?username="joesmith"&password="abcd1234 "

This returns a json encoded authentication token, which I can base 64 encode and pass in the headers of a Web.Contents call

2. call any other api endpoint with Basic authentication using the supplied username and authentication token

Source = Json.Document(Web.Contents(https://www.example.com/api/products?skip=0, [Headers=[Accept="application/json", Authorization="Basic " & AuthorizationToken]]))

This works fine in a normal query.

We just set authentication to Anonylous and hard code in the username and password.

But that won't work for a custom data connector.

In the custom data connector if I set the authetnication to UsernamePassword it fails because the website doesn't use basic authetnication to generate the authentication token.

If I set authentication to Anonymous I don't have the username and password I need to generate the authentication token.

I've spent a fair amount of time searching and have yet to come up with a way of generating a custom credential prompt that will let me get at the username/password fields of Extension.CurrentCredential().

I've also not seen a way to trap the authentication error, which would then let me handle the authenication on my own.

For this last one I've tried adding ManualStatusHandling = {401} to the Web.Contents call.

However, it doesn't seem to get trapped. (checking the result for an error field in the json doesn't appear to happen).

Maybe I'm doing that incorrectly.

I also tried setting authentication to UsernamePassword and passing ManualCredentials = true to Web.Contents, hoping that I could then grab the username and password and do the authentical manually.

However, it appears that the extension is attempting to validate the credentials on the website via Basic Auth before executing the Web.Contents call.

If I'm mistaken about this suggestions on doing the call properly would be appreciated.

Any ideas?

Thanks,

Ned

[tmvander]

We have a similar problem with one of our data sources.  In our case it only supports "password" OAuth grant type, which isn't supported by the OAuth Authentication type.  Our solution was to use UsernamePassword like this:

//  BEGIN SAMPLE

[DataSource.Kind="SamplePowerConnector", Publish="SamplePowerConnector.Publish"]

shared SamplePowerConnector.Contents = Value.ReplaceType(SampleConnector.Contents, type function (ApiQueryUrl as Uri.Type) as any);

SampleConnector.Contents = (ApiQueryUrl as text) =>

    let

        creds = Extension.CurrentCredential(),

        apiPart = "/api/",

        BaseUrL = Text.BeforeDelimiter(ApiQueryUrl, apiPart)&apiPart,

        relativeUrl = Text.AfterDelimiter(ApiQueryUrl, apiPart),

        uname = creds[Username],

        pw = creds[Password],

        token = SampleApiToken(BaseUrL, uname, pw),

        responseViaApi = SampleApiQuery(BaseUrL, relativeUrl, token),

        value = Table.ExpandListColumn(responseViaApi, "value")

    in

        value;

SampleApiToken = (baseurl as text, username as text, password as text) =>

    let

        RequestBody = "grant_type=password&username="&username&"&password="&password,

        ContentType = "application/x-www-form-urlencoded",

        Response = Json.Document(Web.Contents(baseurl, [RelativePath = "Token", Headers=[#"Content-Type"=ContentType], Content=Text.ToBinary(RequestBody)])),

        AccessToken = Response[access_token]

    in

        AccessToken;

SampleApiQuery = (baseurl as text, relativeUrl as text, token as text) =>

        let

            GetComponentsThruApi1 = Json.Document(Web.Contents(baseurl,

                [Headers = [#"Authorization" = "Bearer " &  token],

                RelativePath = relativeUrl])),

            #"Converted to Table" = Record.ToTable(GetComponentsThruApi1)

       in

            #"Converted to Table"

SamplePowerConnector = [

    TestConnection = (dataSourcePath) => {"SamplePowerConnector.Contents", dataSourcePath},

    Authentication = [

        UsernamePassword = []

    ],

    Label = Extension.LoadString("AuthenticationLabel")

];

// END SAMPLE

Now a call to SamplePowerConnector.Contents("https://www.example.com/api/querystring") will result first in a call to https://www.example.com/api/Token to get the access_token, which is then passed to the actual query and added to the Headers as a Bearer token.  You can do something similar in your version of SampleApiToken, but with a RelativePath of "authenticate?username="&username&"&password="&password.

The secret sauce here is that the connector handles all of the communication with the external data source, not just the authentication.  This means you won't use Web.Contents to get your data in your queries, you'll use SamplePowerConnector.Contents.

[tmvander]

I should point out the obvious here - this solution is still a bit of a hack in that every query still needs to re-authenticate with the external datasource and get a new token since there isn't a way to save the token or handle expiration or refresh of the token.

[pierregeraud]

Hi, same problem for me, did you find any solution or way to make it works ?

[nzimmerman]

No, unfortunately.

I have a gut sense that something may be possible using WebAction.Request.

But from what I can tell this will only work when implemented from within a connector in VS.

I don't believe you can test it by doing a direct query from Power BI. 

I just have not had time to go back and hack at it again.

Best of luck to you.

[v-lionel-msft]

Hi @nzimmerman ,

Maybe you can refer to this.

Authentication with a data source 

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nzimmerman]

Hi Lionel,

unfortunately this doesn't do anything for me.

I'm trying to build the custom data connector in Visual Studio, not access the data via an existing data connector.

As I mentioned in my original post I can get access to the data already in Power BI/Excel, but to do so I have to use Anonymous authentication and hard code in the username and password. This is neither portable, nor safe when extending to other users.

What I need to be able to do is capture the username and password entered for the Basic authentication credentials and then pass them through a custom authentication procedure in the connector I am building.

While I have seen a number of ways of doing this via OAuth authentication, that is not an option in my case.