Connecting To REST APIs With OAuth2 Authentication In Power Query/Power BI

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]

12 responses

  1. 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.

    • 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.

  2. 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.

  3. 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.

  4. Pingback: Dew Drop – August 30, 2021 (#3506) – Morning Dew by Alvin Ashcraft

  5. Pingback: Connecting to REST APIs via OAuth2 in Power BI – Curated SQL

  6. Pingback: ➧Dew Drop – August 30, 2021 (#3506) • Softbranchdevelopers

  7. “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

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: