Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
DavidGassmann
Regular Visitor

Multiple Authentication Issues with Custom Connector in Power BI

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!

2 REPLIES 2
v-yangliu-msft
Community Support
Community Support

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 sovle this?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.