Custom Connector - Unreliable authentication

pgarman — Fri, 08 Mar 2024 17:06:20 GMT

Hello,

I have a custom connector for Power BI that connects to a REST API to pull data. The authentication path is OAuth, so when setting up the connecter you must enter the base URL for the datasource, a Client ID, and a Client Secret (there are optional variables to set query parameters).

The connector works perfectly when testing in Visual Studio Code, but when setting up in Power BI desktop the authentication only works sporadically. Sometimes it works perfectly, sometimes it throws and authentication error when first setting up, sometimes it works fine when setting up but then later throws an authentication error when you try to refresh data:

I've seen this same error posted in other threads, but can't find an actual answer to why it is happening.

Here is my connector code:

// This file contains your Data Connector logic
[Version = "1.0.0"]
section Connect;

[DataSource.Kind = "Connect", Publish = "Connect.Publish"]
shared Connect.AbstractItems =
(
ConnectURL as text,
client_id as text, //OAuth Client ID
client_secret as text, //OAuth Client Secret
optional projectId as text,
optional itemType as text
) =>
let
//Get the access token and build header from OAuth clientID / clientSecret
token = FetchToken(ConnectURL, client_id, client_secret),
header = [#"Authorization" = "Bearer " & token],

//Build query string and endpoint URL based on parameters
query = if (projectId <> null and itemType <> null) then "?project=" & projectId & "&itemType=" & itemType
else if (projectId <> null) then "?project=" & projectId
else if (itemType <> null) then "?itemType=" & itemType
else "",
abstractItemsUrl = ConnectURL & "/rest/latest/abstractitems",

//Method to get a single page of data from given url and start index position
GetPage = (abstractItemsUrl, start) =>
let
newQuery = if (query <> "" and query <> null) then query & "&startAt=" else "?startAt=",
newUrl = abstractItemsUrl & newQuery & start,
jsonResponse = Web.Contents(
newUrl,
[Headers = header]
),
doc = Json.Document(jsonResponse),
newData = doc[data],
newMeta = doc[#"meta"],
newTable = Table.FromRecords(
newData,
type table[
id = Number.Type,
itemType = Number.Type,
project = Number.Type,
createdDate = Text.Type,
modifiedDate = Text.Type,
lastActivityDate = Text.Type,
createdBy = Number.Type,
modifiedBy = Number.Type,
fields = Text.Type
]
),
startIndex = doc[Meta][pageInfo][startIndex],
totalResults = doc[Meta][pageInfo][totalResults],
response = [Meta = newMeta, Data = newTable]
in
response,

//Recursive call to GetPage method for paginated table of results
GeneratedList =
List.Generate(
() =>[i = 0, res = GetPage(abstractItemsUrl, Text.From(i))],
each [i] < [res][Meta][pageInfo][totalResults] and [res][Data] <> null,
each [i = [i] + 20, res = GetPage(abstractItemsUrl, Text.From(i))],
each [res][Data]),
ConvertedData = Table.FromList(GeneratedList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
ExpandedList = Table.ExpandListColumn(ConvertedData, "Column1"),
ExpandedRecords = try Table.ExpandRecordColumn(ExpandedList, "Column1",
{"id", "itemType", "project", "createdDate", "modifiedDate", "lastActivityDate", "createdBy", "modifiedBy", "fields"}) otherwise null
in
ExpandedRecords;

//Method to fetch access token from Connect
FetchToken = (ConnectURL as text, clientId as text, clientSecret as text) =>
let
url = Uri.Combine(ConnectURL, "/rest/oauth/token?grant_type=client_credentials"),
headers = [
#"Authorization" = "Basic " & Binary.ToText(Text.ToBinary(clientId & ":" & clientSecret), BinaryEncoding.Base64)
],
response = Web.Contents(
url,
[
Headers = headers,
Content = Text.ToBinary("")
]
),
jsonResponse = Json.Document(response),
accessToken = jsonResponse[access_token]
in
accessToken;

// Data Source Kind description
Connect = [
TestConnection = (dataSourcePath) => {"Connect.AbstractItems", dataSourcePath},
Authentication = [
Anonymous = []
],
Label = "Authentication"
];

// Data Source UI publishing description
Connect.Publish = [
Beta = true,
Category = "Online Services",
ButtonText = { Extension.LoadString("ButtonTitle"), Extension.LoadString("ButtonHelp") },
SourceImage = Connect.Icons,
SourceTypeImage = Connect.Icons
];

Connect.Icons = [
Icon16 = { Extension.Contents("Connect16.png"), Extension.Contents("Connect20.png"), Extension.Contents("Connect24.png"), Extension.Contents("Connect32.png") },
Icon32 = { Extension.Contents("Connect32.png"), Extension.Contents("Connect40.png"), Extension.Contents("Connect48.png"), Extension.Contents("Connect64.png") }
];

The connector code has the TestConnection, which was pointed out as missing in other threads, and it works when I test it in Visual Studio Code.

I can sometimes get around this error by generating a new client ID and client Secret for the same user account and retrying.

A few other things:

The user credentials have full admin access to the data source, so this is not a permission issue.
The connector uses the Client ID and Client Secret to request a Token from the data source and uses that token in the actual API query
The data source API limits the number of results to a maximum of 20 with each call, so the connector is set up to paginate results through looped calls until all data has been returned.
The API Token generated from the data source is 'good' for one hour, so I don't think this is an issue of an expiring token.
I use this connector multiple times within the same Power BI project to create different tables, and would like to be able to use the same Client ID and Client Secret each time I set up a new data source with this connector.

Can anyone help me figure out what the issue might be?

Re: Custom Connector - Unreliable authentication

lbendlin — Fri, 08 Mar 2024 23:07:30 GMT

The API Token generated from the data source is 'good' for one hour, so I don't think this is an issue of an expiring token.

not so sure about that. Could be the culprit.

Re: Custom Connector - Unreliable authentication

pgarman — Mon, 11 Mar 2024 15:07:18 GMT

@lbendlin I know that the API token doesn't expire for an hour, and anytime the connector is 'refreshed' in Power BI a new token is generated.

Is there a way to get error logs out of Power BI desktop for something like this so I can see the exact error message returned by the data source?

Re: Custom Connector - Unreliable authentication

lbendlin — Mon, 11 Mar 2024 15:46:13 GMT

You should be able to see the issue when running network traces on that machine. Wireshark or the F12 tools etc.

topic Re: Custom Connector - Unreliable authentication in Developer

Custom Connector - Unreliable authentication

Re: Custom Connector - Unreliable authentication

Re: Custom Connector - Unreliable authentication

Re: Custom Connector - Unreliable authentication