There are a lot of articles and blog posts out there on how to handle OAuth2 authentication when connecting to REST APIs from Power Query in Power BI. However there is also a lot of confusion and contradictory information too so in this post I want to give you the definitive, Microsoft-endorsed answer to this question, which is:
If want to connect from Power BI to a REST API that uses OAuth2 authentication then you need to build a custom connector. You can find documentation on how to implement an OAuth2 flow in a custom connector here.
The only exception is that you can connect to some APIs that use AAD authentication using the built-in web or OData connectors, as documented here.
A quick web search will turn up several examples of how to implement an OAuth2 credential flow in regular Power Query queries without needing a custom connector. This is not recommended: it’s not secure and it’s not reliable. In particular, hard-coding usernames/passwords or client ids/client secrets in your M code is a really bad idea. What’s more requesting a new token every time a query runs isn’t great either.
Unfortunately Excel Power Query doesn’t support custom connectors at the time of writing. Also, if you use a custom connector in the Power BI Service then you’ll need to use an on-premises gateway. Finally, there’s an article here explaining why it isn’t easy to connect Power BI to the Microsoft Graph API.
[Thanks to Curt Hagenlocher and Matt Masson for the information in this post]
Thank you for this… I can now stop banging my head against the desk trying to connect Excel Power Query to my own Spotify account.
Might be a dumb question, but why is requesting for a new token every time the query runs not a good idea?
The first issue that comes into mind is that it would be inefficient. For example, for situations where you have to perform some sort of pagination, you’d have to request a new token for every single page that you try to get. There’s no true “refresh token” mechanism to support it, which is where everything falls apart.
I’m sure Chris would’ve found some other crazy scenarios with a few of the accounts that he supports.
Hi Chris, thank you for the summary. Is it possible to hide an API without managed identity behind ‘API Management / APIM’ with managed identity via a policy? That would circumvent the need for a custom connector, at the expense of an added Azure component.
Hi Chris, thanks for your blob post.
Is there any specific reason, Microsoft isn’t providing a field “resource” or “scope” (e.g. in the OAuth connection parameters) that will allow to connect to every resource behind an AAD authentication ? As it looks it’s the only thing preventing us to be authenticated with the same Workflow without the need to develop a custom connector (no one want to do this…)
Regards,
This is actually available on the custom connectors SDK and its far easier when the authentication is AAD. Quite a few of the certified custom connectors use this.
Hi all, if anyone is interested in building custom connectors, check this curated list. Pay special attention to Miguel’s Power BI API custom connector in GitHub that has OAUTH implementation.
https://www.powp.co/2021/08/building-power-bi-custom-connectors/
“In particular, hard-coding usernames/passwords or client ids/client secrets in your M code is a really bad idea”
@Chris –
Do you know a way in which a Excel File containing a M query pulling data from a lets say an Access Database can be shared with the end user – without them having to enter a database password first time they open it on their system
I so wish it was possible to supply the credentials either via VBA or within the M Code.
Cheers
Sam
That is something we specifically prevent – it would be convenient in some cases, but it would also be a massive security hole.
I’m having an issue with a REST API for ServiceNow and using the Web connector and username and password for my account which has the correct permissions .
I can connect to the data through a browser or using another tool, but when i connect in Power BI using the Web connector i just get the “we couldn’t authorize with credentials provided” ….
Is this related to things you outline in this article?
Hi Chris, this post is only a couple months old and I’m hoping I can ask a follow-up question….
Taking your advice I created a custom connector for an internal REST API that uses OAuth2, and retrieves simple JSON data. It works fine in the PBI desktop, and works fine if I refresh a PBI dataset via an enterprise gateway.
But I cannot seem to use the custom connector from a PBI dataflow. My custom connector only seems to work from datasets.
Why is there an inconsistency in the dataflows? Shouldn’t those be able to connect to the same data sources that my datasets are connected to? I start my dataflow by adding a “blank query”. After copying the PQ code into the blank query, it refuses to allow me to connect to the data source (the one that was shared with). It also refuses to let me save my incomplete dataflow.
You haven’t blogged much about loading data via dataflows. I hope this question doesn’t take us too far into the weeds. It would be unfortunate if these custom connectors weren’t supported for dataflows.
Dataflows do not support custom connectors yet, sorry
That is unfortunate. Thanks for the reply. I scratch my head a lot when I’m working with dataflows. I don’t know why they built them to have so much little consistency with datasets.
I can understand the fact that it is complicated to build an entire power query U/X in a web browser.
But U/X aside, they should at least have an underlying foundation for dataflows that works the same as the Power Query for datasets. For example, you should be able to click a dataset in a workspace and convert it to a dataflow. There should be a reasonable migration path from one to the other (especially where data sources and Power Queries are concerned).
Instead of making dataflows work similar to datasets, and building on the knowledge and the skills that developers already possess, they decided to set us on a totally new path to some unexpected destination. There should be a high degree of overlap between these two techniques for data retrieval, but it seems like I’m constantly bumping into annoying and unexpected issues with my dataflows. I may need to take a break from them for a year or two.
Chris, you said:
“Dataflows do not support custom connectors yet, sorry”
These types of show-stoppers will sometimes steer us far out of our way (eg. hosting power query in ADF using wrangling dataflows and integration runtimes or whatever). These other types of approaches can be complex, costly and they can go *really* far off the beaten path.
Customers don’t want to invest days or weeks working on creating alternative infrastructure, just because of one missing dataflow feature that was omitted for reasons we don’t understand.
I looked at the power bi roadmap and can find other connectors, but not custom connectors :
https://community.powerbi.com/t5/Data-Stories-Gallery/Power-BI-Release-Plan/m-p/930557
Does that mean they won’t be available for the next year? Is there any way to get feedback from this team? Is there a github where they can be reached? I found a github where they host sample connectors, but that doesn’t seem to be a very active community.
Hi everyone, i have been reading all the stuff here and other places, I would really like to have a “fool Proof” step by step help to update from pbi service a Api that has a token that expires each 5 or 10 minutes.
Ibe tried a lot of stuff even to hardcode but as i was reading it is not the solution.
Also really i feel so dumb since not a tec guy nor englis native speaker and alsp cant understand what to do.
It seems so simple but i don’t understand what to change in Hello World example or were to Authorize the token so “A real Example” really would be great.
What in need Is to update this API once a day.
https://api.teamplace.maxi.com/api/reports/sales?PARAMWEBREPORT_FechaDesde=2021-01-01&PARAMWEBREPORT_FechaHasta=2031-01-01&ACCESS_TOKEN=mytoken
I can obtain mytoken from this page.
https://api.teamplace.maxi.com/api/oauth/token?grant_type=client_credentials&client_id=myuser&client_secret=mycode
Thanks a lot, really best regards
Hi Everyone, I was hoping that someone could confirm/deny whether it’s possible to an Oauth2 token directly from PowerBI desktop? I don’t have a gateway and I don’t want to publish the report. I’m happy to create a token and update PBI each time I want an update, but I don’t want to download the dataset and save it to the file system each time.