Custom Data Connectors can support multiple kinds of authentication. For most modern web apps, OAuth is the obvious choice and the Power Query SDK, coupled with the authentication examples (here and here), make it easy to setup.
Unfortunately, two apps that I have recently created Custom Data Connectors for were partway through an OAuth implementation. While the circumstances were unique to each application, I had to authenticate the user with the OAuth Identity Provider (IdP) and then exchange the access_token for one that the application could use. This isn’t an unusual OAuth “flow”, particularly when the application uses a custom payload in the access_token JWT for application functions.
Whether you agree with an exchange flow or not, I had to figure out how to make the Custom Data Connector work within these constraints.
To accomplish this, I had to interrupt the normal OAuth response flow and exchange that IdP provided access_token for one that the application would accept as through Bearer authorization. I also needed to ensure that the refresh_token flow would continue to work when Power BI encountered a HTTP 401 (unauthorized) response from and API, causing it to perform a refresh grant to retrieve a new access_token and keep data flowing without the user being interrupted.
It just wasn’t clear to me exactly where to do all of this work.
In other OAuth frameworks or SDK’s, it’s usually pretty easy to see where you’d drop into the flow and make the exchange before moving ahead. Given the Github authentication example, my mind wasn’t thinking functionally at the time (it’s still a challenge for me!). It was unclear that the response from the FinishLogin function was being consumed and stored by Power BI directly. Once I made that connection it was easy to accomplish what I needed.
The Basic OAuth Setup
I started with the examples from the articles I’ve linked to above, enabling OAuth and setting up each of the functions.
...
Authentication = [
OAuth = [
StartLogin=StartLogin,
FinishLogin=FinishLogin,
Refresh=Refresh,
Logout=Logout
]
...
Inside of the FinishLogin function there is a call to the TokenMethod which takes the code grant and exchanges it with the IdP for an access_token.
TokenMethod = (code) =>
let
Response = Web.Contents("https://auth.example.com/oauth2/token", [
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
code = code,
redirect_uri = redirect_uri])),
Headers=[#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"]]),
LoginResult = Json.Document(Response)
in
LoginResult ;
It took me a while to realize that the Json.Document(Response) was simply parsing the OAuth payload (id_token, access_token, and refresh_token) into a Power Query record that the connector/runtime was storing and using later. Therefore, I had to do my exchange somewhere in this function before returning the Record that Power BI needed.
Exchanging the Access Token
In order to exchange the trusted access_token, another function was needed to call the actual Exchange endpoint of each application. YMMV, but here’s what a basic function might look like.
TokenExchange = (id_token as text) =>
let
NewToken = Web.Contents("https://auth.example.com/api/exchangeToken",[
Content = Text.ToBinary("{id_token: '" & id_token & "'}"),
Headers = [#"Content-Type"="application/json"]]),
LoginResult = Json.Document(NewToken),
AccessToken = LoginResult[token]
in
AccessToken
With that in place, I could modify the TokenMethod to do the exchange mid-stream for me like so.
TokenMethod = (code) =>
let
Response = Web.Contents("https://Github.com/login/oauth/access_token", [
Content = Text.ToBinary(Uri.BuildQueryString([
client_id = client_id,
client_secret = client_secret,
code = code,
redirect_uri = redirect_uri])),
Headers=[#"Content-type" = "application/x-www-form-urlencoded",
#"Accept" = "application/json"]]),
body = Json.Document(Response),
result = if (Record.HasFields(body, {"error", "error_description"})) then
error Error.Record(body[error], body[error_description], body)
else
// This is a code exchange, we expect a refresh_token
if (Record.HasFields(body, {"refresh_token"})) then
[
refresh_token=body[refresh_token],
access_token = ExchangeToken(body[id_token])
]
// This token was obtained by a Refresh request. No refresh_token
else
[
access_token = ExchangeToken(body[id_token])
]
in
result
The key was understanding that the result of the TokenMethod, which is what the FinishLogin method will ultimately respond with, needs to provide a Record with all of the fields necessary to continue the OAuth flow. In this case, simply replacing the access_token and refresh_token allowed Power BI to store this for later use.
Whenever the token expires, producing a HTTP 401 (Unauthorized) response, the RefreshToken logic kicks in, goes back through this same logic, and data keeps flowing.
Conclusion
Learning how to use Power Query to get data from REST APIs has been a fun (if not frustrating at times) learning experience. The documentation continues to improve and the Power Query team and community are very helpful. Each new challenge helps me better understand the building blocks they have provided and how to get to the next milestone.
Now, if I could just have something like a “finally” query that gets executed after an import to create the relationships I need and additional linking tables if needed. Wow, I’d be in heaven! π