Multiple Authentication Issues with Custom Connector in Power BI

DavidGassmann — Thu, 11 Jul 2024 07:11:01 GMT

Hi Experts,

I am encountering an issue with a custom Power Query connector that uses OAuth2 authentication in Power BI. The connector is designed to retrieve data from an API, and it works correctly when fetching data from a single endpoint. However, I face a significant problem when iterating over multiple URLs to retrieve data from different projects.

Problem Description:

Every time the connector accesses a new URL, Power BI prompts for re-authentication. This results in multiple authentication requests, which is impractical and disrupts the data retrieval process. Specifically, the authentication token seems to become invalid whenever a different URL is accessed, causing the need for repeated logins.

Observed Behavior:

When accessing different URLs within the same session, Power BI repeatedly prompts for re-authentication. This issue seems to stem from Power BI treating each unique URL as a separate data source, invalidating the current token.

Desired Outcome:

Implement token caching or reuse the existing token across multiple URLs within the same session until it expires.
Enable seamless data retrieval without repeated authentication prompts for each URL.

Is it possible to use the same connector with the same credentials (same authentication) across different URLs in Power BI? If yes, how can this be achieved? Or is there a workaround?

Current Connector Code:

// This file contains your Data Connector logic section buildagil; // OuraCloud OAuth2 values client_id = "XXXXX"; client_secret = "XXXXX"; redirect_uri = "http://localhost"; token_uri = "XXXXX"; authorize_uri = "XXXXX"; logout_uri = "XXXXX"; // Login modal window dimensions windowWidth = 720; windowHeight = 1024; // OAuth2 scope scope_prefix = ""; scopes = { "openid", "offline_access", "bcf" }; [DataSource.Kind="buildagil", Publish="buildagil.Publish"] shared buildagil.Contents = Value.ReplaceType(UrlImpl, UrlType); shared buildagil.GetAccessToken = GetAccessToken; // Expose the access token function UrlType = type function( url as (type text meta [ Documentation.Description = "Specify api url", Documentation.SampleValues = {"https://api.buildagil.com/bcf/3.0/projects"} ])) as table meta [ Documentation.Name = "buildagil" ]; UrlImpl = (url as text) => let // Get the access token tokenResponse = GetAccessToken(), accessToken = tokenResponse[access_token], // Fetch data from the API with the access token source = Json.Document(Web.Contents(url, [Headers=[Authorization="Bearer " & accessToken]])) in source; // Function to get the access token GetAccessToken = () as record => let token = Extension.CurrentCredential()[access_token] in token; // Data Source Kind description buildagil = [ TestConnection = (dataSourcePath) => { "buildagil.Contents", dataSourcePath }, Authentication = [ OAuth = [ StartLogin=StartLogin, FinishLogin=FinishLogin, Refresh=Refresh, Logout=Logout ] ], Label = Extension.LoadString("DataSourceLabel") ]; // Data Source UI publishing description buildagil.Publish = [ Beta = false, Category = "Other", ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") }, LearnMoreUrl = "https://powerbi.microsoft.com/", SourceImage = buildagil.Icons, SourceTypeImage = buildagil.Icons ]; // Helper functions for OAuth2: StartLogin, FinishLogin, Refresh, Logout StartLogin = (resourceUrl, state, display) => let authorizeUrl = authorize_uri & "?" & Uri.BuildQueryString([ response_type = "code", client_id = client_id, redirect_uri = redirect_uri, state = state, scope = GetScopeString(scopes, scope_prefix) ]) in [ LoginUri = authorizeUrl, CallbackUri = redirect_uri, WindowHeight = windowHeight, WindowWidth = windowWidth, 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); Logout = (token) => logout_uri; TokenMethod = (grantType, tokenField, code) => let queryString = [ grant_type = grantType, redirect_uri = redirect_uri, client_id = client_id, client_secret = client_secret ], 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; Value.IfNull = (a, b) => if a <> null then a else b; GetScopeString = (scopes as list, optional scopePrefix as text) as text => let prefix = Value.IfNull(scopePrefix, ""), addPrefix = List.Transform(scopes, each prefix & _), asText = Text.Combine(addPrefix, " ") in asText; buildagil.Icons = [ Icon16 = { Extension.Contents("buildagilCloudConnector16.png"), Extension.Contents("buildagilCloudConnector20.png"), Extension.Contents("buildagilCloudConnector24.png"), Extension.Contents("buildagilCloudConnector32.png") }, Icon32 = { Extension.Contents("buildagilCloudConnector32.png"), Extension.Contents("buildagilCloudConnector40.png"), Extension.Contents("buildagilCloudConnector48.png"), Extension.Contents("buildagilCloudConnector64.png") } ];

Current Query Code:

let // Function to query project data GetProjects = () => let Source = buildagil.Contents("https://api.buildagil.com/bcf/3.0/projects"), ProjectsTable = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandedProjects = Table.ExpandRecordColumn(ProjectsTable, "Column1", {"project_id", "name"}, {"project_id", "name"}) in ExpandedProjects, // Function to query topics for a given project GetTopics = (project_id as text) => let url = Text.Format("https://api.buildagil.com/bcf/3.0/projects/#{0}/topics?$top=100000", {project_id}), TopicsResponse = buildagil.Contents(url), TopicsTable = Table.FromList(TopicsResponse, Splitter.SplitByNothing(), null, null, ExtraValues.Error), ExpandedTopics = Table.ExpandRecordColumn(TopicsTable, "Column1", {"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"}, {"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"}) in ExpandedTopics, // Load project data Projects = GetProjects(), // Dynamically load topics for each project Topics = Table.AddColumn(Projects, "Topics", each GetTopics([project_id])), // Expand topics into separate columns ExpandedTopics = Table.ExpandTableColumn(Topics, "Topics", {"guid", "topic_type", "topic_status", "title", "labels", "labels_id", "creation_date", "creation_author", "modified_date", "modified_author", "assigned_to", "description", "due_date", "document_references"}, {"Topic GUID", "Topic Type", "Topic Status", "Title", "Labels", "Labels ID", "Creation Date", "Creation Author", "Modified Date", "Modified Author", "Assigned To", "Description", "Due Date", "Document References"}), // Adding a custom column for the status AddCustomStatusColumn = Table.AddColumn(ExpandedTopics, "Status", each if [Topic Status] = null then null else if [Topic Status] = "Closed" then "Abgeschlossen" else if [Topic Status] = "Draft" then "Entwurf" else if [Topic Status] = "Open" and [Due Date] <> null and DateTime.From([Due Date]) < DateTime.LocalNow() then "Überfällig" else if [Topic Status] = "Open" then "Offen" else [Topic Status]), // Converting date fields to date types ConvertToDateType = Table.TransformColumnTypes(AddCustomStatusColumn, { {"Creation Date", type datetime}, {"Modified Date", type datetime}, {"Due Date", type datetime} }), // Renaming columns in the final table to German RenameColumns = Table.RenameColumns(ConvertToDateType, { {"project_id", "Projekt-ID"}, {"name", "Projekt-Name"}, {"Topic GUID", "Themen-GUID"}, {"Topic Type", "Themenart"}, {"Topic Status", "Themenstatus"}, {"Title", "Themen-Titel"}, {"Labels", "Kategorien"}, {"Labels ID", "Kategorien-IDs"}, {"Creation Date", "Erstellungsdatum"}, {"Creation Author", "Thema erstellt von"}, {"Modified Date", "Änderungsdatum"}, {"Modified Author", "Thema geändert von"}, {"Assigned To", "Thema zugewiesen an"}, {"Description", "Thema-Beschreibung"}, {"Due Date", "Fälligkeitsdatum"}, {"Document References", "Dokumentverweise"}, {"Status", "Status"} }) in RenameColumns

Thanks for the help!

Re: Multiple Authentication Issues with Custom Connector in Power BI

DavidGassmann — Tue, 30 Jul 2024 07:03:56 GMT

Hi Liu Yang,

Thank you for your response. Unfortunately, I couldn't find relevant information or a solution to my problem in the provided links.

Before posting my question in the community, I came across this post: Multiple Authentication Issue with Custom Connector. I suspect that it might be related to my issue, but I am not entirely sure, given my limited experience with Power BI.

In my scenario, I have a query that retrieves project information. I then use the IDs of these projects to fetch various topic information. The URL changes each time topics for a new project are loaded (due to the new project ID). I suspect that this is why I need to re-authenticate for each project. While this isn't a major issue for a small number of projects, it becomes impractical when trying to manage hundreds of projects, which is the intended use case for using Power BI in this case.

I believe that the frequent re-authentication is caused by Power BI treating each unique URL as a separate data source, invalidating the current token.

Do you have an idea how to solve this?

topic Re: Multiple Authentication Issues with Custom Connector in Power BI in Developer

Multiple Authentication Issues with Custom Connector in Power BI

Problem Description:

Observed Behavior:

Desired Outcome:

Re: Multiple Authentication Issues with Custom Connector in Power BI

Re: Multiple Authentication Issues with Custom Connector in Power BI