ODBC connector and OAuth flow

[Ferna]

Hi all,

I'm trying to customize a ODBC connector that require some parameters before starting an authentication flow. The parameters are specifically 3 as IdP endpoint, user, user-secret. These are inputted from a GUI like this: 

 

That is given from that code: 

shared Project.Contents = Value.ReplaceType(ProjectImpl, ProjectType);

// Wrapper function to provide additional UI customization.
ProjectType = type function (
    Server as (
        type text meta [
            Documentation.FieldCaption = "Server",
            Documentation.FieldDescription = "The hostname of the server.",
            Documentation.SampleValues = {"localhost"}
        ]
    ),
    Port as (
        type number meta [
            Documentation.FieldCaption = "Port",
            Documentation.FieldDescription = "Port server listens on.",
            Documentation.SampleValues = {9200}
        ]
    ),
    UseSSL as (
        type logical meta [
            Documentation.FieldCaption = "Use SSL",
            Documentation.FieldDescription = "Use SSL",
            Documentation.AllowedValues = {true, false}
        ]
    ),
    HostnameVerification as (
        type logical meta [
            Documentation.FieldCaption = "Certificate validation",
            Documentation.FieldDescription = "Certificate validation",
            Documentation.AllowedValues = {true, false}
        ]
    ),
    IdPUrl as (
        type text meta [
            Documentation.FieldCaption = "Endopint",
            Documentation.FieldDescription = "Endpoint",
            Documentation.SampleValues = {"http://"}
        ]
    ),
    User as (
        type text meta [
            Documentation.FieldCaption = "Username",
            Documentation.FieldDescription = "Username",
            Documentation.SampleValues = {"username"}
        ]
    ),
    Secret as (
        type text meta [
            Documentation.FieldCaption = "Secret",
            Documentation.FieldDescription = "Secret",
            Documentation.SampleValues = {"secret"}
        ]
    )
) as table meta [
    Documentation.Name = "Project Mod"
];


ProjectImpl = (Server as text, Port as number, UseSSL as logical, HostnameVerification as logical, IdPUrl as text, User as text, Secret as text) as table =>
// The value are available here!!
    let ..............
    in .............

These information will be hopefully passed in this phase activated by a second GUI that should take these three parameters for initialize the Oauth2 flow as such:

How can I access to these three available from here:

Project = [ ...............

or in a global context? Is not allowed to write a global variable inside "ProjectImpl".

[Anonymous]

Hi  @Ferna ,

If you are connecting to data from the API, you can consider using your own editing Web.Content function to write the parameters in the request header, and then do PQ customized data processing after you successfully get the data.

Web.Contents - PowerQuery M | Microsoft Learn

For example:

let     url = ...,     headers = [#"Content-Type" = "application/json"],     postData = Json.FromValue([x = 235.7, y = 41.53]),     response = Web.Contents(         url,         [             Headers = headers,             Content = postData         ]     ),     jsonResponse = Json.Document(response) in     jsonResponse

This is the related document, you can view this content:

Pass Parameters to Identity Providers (auth0.com)

Microsoft identity platform and OAuth 2.0 authorization code flow - Microsoft identity platform | Mi...

Best Regards,

Liu Yang

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

[Ferna]

Hi Liu,

I'm querying an OpenSearch database,  and the uses case is many users that has its credentials and IdP endpoint. They should insert the parameter manually if they authenticate with Oauth2, otherwise is required a compiling and build phase of ".mez" file for each of these users.  The IdP mostly is "static" but user and secret can vary significantly.