Create a Power Query custom connector using your own identity to access resources

by Jan de Vries in  , , , , ,

If you’ve read my previous post on how to create a Power Query custom connector with authentication, you might be wondering if the same can be achieved by using your own identity instead of a service principal being used.
The answer is: YES!

There are a couple of resources that I found helpful, but didn’t provide me with a complete answer, but did help me get to a solution. These are the ones I used as a reference:

Set up the authentication

In the previous post, the Anonymous authentication was used.
If you want to use your own account, and use Microsoft Entra ID, this should be changed to OAuth making the block look like this.

MyConnector = [
    TestConnection = (dataSourcePath) => {"MyConnector.Contents"},
    Authentication = [
        // Anonymous = [], // Can be used when a Service Principal is used to authorize against the API
        OAuth = [
            StartLogin = StartLogin,
            FinishLogin = FinishLogin,
            Refresh = Refresh
        ]
    ]
];

The StartLogin, FinishLogin and Refresh are being invoked by the runtime and you need to define your own implementation on what needs to happen over there.

Implement the necessary methods

Disclaimer: I didn’t come up with most of the code in this post. It’s all copied from the earlier mentioned references.

The first thing you need to do is gather some of the details for your environment.

  • Tenant Id
    This will be the tenant identifier where the resource you want to connect to is located
  • Client Id The Client Id of the application you want to connect to
  • Client Secret A secret created for the application you want to connect to
  • Scope The scope you want access for.

You might think to pass these values to the connector, when you add it to Power BI. Too bad, this isn’t possible.
The values are being used when adding the connector to your report and have to log in immediate. Therefore, the values need to be hardcoded into the connector. In my opinion, this isn’t a very good experience for us engineers. I found two questions on Stack Overflow for this topic( https://stackoverflow.com/q/45881474/352640 and https://stackoverflow.com/q/77182342/352640), but both don’t have a (good) answer.
To make this somewhat maintailable, I opted to read the values from a file like ClientId = Text.FromBinary(Extension.Contents("clientid.txt"));. It’s a poor man’s solution to environment variables.

If you read the documentation, you also know https://oauth.powerbi.com/views/oauthredirect.html is the callback URL for Power Query custom connectors. You need to add this URL as a valid callback URL for the OAuth2 flow in the App Registration.

The implementation for StartLogin is quite straightforward. It comes down to invoking the authorize-endpoint with the correct values. The FinishLogin and Refresh are invoking the token-endpoint.
Below is the code I ended up with.

//
// OAuth2 flow definition
//
OAuth2BaseUrl = "https://login.microsoftonline.com/" & TenantId & "/oauth2/v2.0/";
AuthorizeUrl = Uri.Combine(OAuth2BaseUrl, "authorize");
TokenUrl = Uri.Combine(OAuth2BaseUrl, "token");

ClientId = Text.FromBinary(Extension.Contents("clientid.txt"));
ClientSecret = Text.FromBinary(Extension.Contents("clientsecret.txt"));
Scope = "api://" & ClientId & "/MyAdmin";

TenantId = Text.FromBinary(Extension.Contents("tenant.txt"));
redirect_uri = "https://oauth.powerbi.com/views/oauthredirect.html";

StartLogin = (resourceUrl, state, display) =>
    let
        authorize_uri = OAuth2BaseUrl & "authorize",
        authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([
            client_id = ClientId,
            client_secret = ClientSecret,
            redirect_uri = redirect_uri,
            state = state,
            scope = Scope,
            response_type = "code",
            response_mode = "query",
            login = "login",
            prompt="select_account"
        ])
    in
        [
            LoginUri = authorizeUrl,
            CallbackUri = redirect_uri,
            WindowHeight = 720,
            WindowWidth = 1024,
            Context = null
        ];

FinishLogin = (context, callbackUri, state) =>
    let
        // parse the full callbackUri, and extract the Query string
        parts = Uri.Parts(callbackUri)[Query],
        // if the query string contains an "error" field, raise an error
        // otherwise call TokenMethod to exchange our code for an access_token
        result = if (Record.HasFields(parts, {"error", "error_description"})) then 
                    error Error.Record(parts[error], parts[error_description], parts)
                 else
                    TokenMethod("authorization_code", "code", parts[code])
    in
        result;

Refresh = (resourceUrl, refresh_token) => TokenMethod("refresh_token", "refresh_token", refresh_token);

TokenMethod = (grantType, tokenField, code) =>
    let
        token_uri = OAuth2BaseUrl & "token",

        queryString = [
            client_id = ClientId,
            client_secret = ClientSecret,
            grant_type = grantType,
            redirect_uri = redirect_uri            
        ],
        queryWithCode = Record.AddField(queryString, tokenField, code),

        tokenResponse = Web.Contents(token_uri, [
            Content = Text.ToBinary(Uri.BuildQueryString(queryWithCode)),
            Headers = [
                #"Content-type" = "application/x-www-form-urlencoded",
                #"Accept" = "application/json"
            ],
            ManualStatusHandling = {400} 
        ]),
        body = Json.Document(tokenResponse),
        result = if (Record.HasFields(body, {"error", "error_description"})) then 
                    error Error.Record(body[error], body[error_description], body)
                 else
                    body
    in
        result;

Base64Url.Encode = (s) => Text.Replace(Text.Replace(Text.BeforeDelimiter(Binary.ToText(s,BinaryEncoding.Base64),"="),"+","-"),"/","_");

I added the prompt="select_account" in the StartLogin code. This makes sure you are always prompted to select the account you want to use. If this property isn’t sent, the code might log you in automatically (SSO) without notifying what account to use. This can be quite annoying, especially when you’re a consultant and have dozens of accounts in multiple tenants.

Use the logged in user-identity

For ease of use, I use the GetToken-method to get the access token of the current logged in identity.

QueryImpl = (optional query as text, optional tenantId as text, optional clientId as text, optional clientSecret as text, optional resource as text) =>
    let
        token = GetToken(),

        headers = [
            #"Content-Type" = "application/json",
            #"Authorization" = "Bearer " & token
        ],

        json = Json.Document(
            Web.Contents(
            BaseUrl,
            [
                RelativePath = "/my/endpoint",
                Headers = headers,
                Content = Text.ToBinary(query)
            ]
            )
        ),
        table = Table.FromRecords(json)
    in
        table;

GetToken = () =>
    let
        access_token = Extension.CurrentCredential()[access_token]
    in
        access_token;

At first, I did get an error:

ResourceAccessAuthorizationException’ was thrown. at Microsoft.Mashup.Engine1.Library.Http.Request.GetResponse

The Power Query SDK doesn’t provide a lot of useful information on what was wrong.
I used Fiddler to check the HTTPS traffic and discovered I was sending some wrong values towards the authorize-endpoint. A great reminder, for me, how awesome this tool is for web development.

Share