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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
karekaasamoen
Frequent Visitor

How to refresh using REST API with OAuth2

I created a model in the designer using API data with OAuth2 authentication. To make it a bit user-friendly a created some functions named GetAccessToken(), GetPage(), GetSize() and GetPages(). These functions made it quite userfriendly to make a OAuth2 connection, and of course all the functions was parameterized for re-use in the future

 

Uploaded the model to powerbi.com and could not refresh the dataset, with and error message telling me that I had references to an unsupported datasource. 

 

I did some searches in the Forum and saw that there was some issues with parameterixed functions, so i rewrote the powerquery scripts with all URL's, clientID's etc. hardcoded - I was actually making a script with real bad coding with no possibility for future re-use of common functions

 

Uploaded the model again, the refresh process started so it was accepted as a datasource, but ended with a error message again telling me that the credentials stored in the cloud was missing. I guess the script was not refreshed and the stored access token was no longer valid. 

 

So, my question is - how do I refresh (even refresh now will help), using API datasource with OAuth2 authentication?

 

If this isn't possible in powerBI, I think it should be quite soon. This will make it difficult for me to use PowerBi for analyzing my data. And I'm probably not alone

1 ACCEPTED SOLUTION
Youssef
Employee
Employee

Hi,

 

As you guessed, refreshing data with OAuth2 authentication is not yet supported in Power BI. Editing credentials in the PowerBI.com service will require the service to know how to direct you to the OAuth login page, capture and store the access token credentials from the redirect URL and refresh the token when necessary, none of which the service know how to do today with the tooling available to you.

 

It seems like you have been trying to work around some of those things by writing your custom code, but it might be really hard to have a working end to end solution that way.

 

The good news is that this is something on the team's radar and is in the pipeline. No solid dates or ETAs at the moment, but expect to hear more announcements in this area in the future.

Youssef Shoukry
Product Manager, Power BI

View solution in original post

15 REPLIES 15
tackytechtom
Super User
Super User

Hi everyone!

 

I know this is quite an old thread, but I still would like to share two ways of refreshing an oauth2 token for an azure sql database. This should work the same way for other data source types.

 

blog post a via a Powershell script as part of an Azure Devops Pipeline (script line 43 downwards)

blog post b via data factory. 

 

In either of the two alternatives, rest apis are used.

 

I hope this one is helping!

 

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/



Did I answer your question➡️ Please, mark my post as a solution ✔️

Also happily accepting Kudos 🙂

Feel free to connect with me on LinkedIn! linkedIn

#proudtobeasuperuser 

kschaefers
Resolver I
Resolver I

Hi karekaasamoen, would you mind sharing your functions to interact with OAuth2? I'm trying to solve a similar use case, though I do not intend to use PowerBI.com

 

THanks a lot!

kaushal
Frequent Visitor

Is oauth2 now implemented in power BI or not. Any update in future? 

Youssef
Employee
Employee

Hi,

 

As you guessed, refreshing data with OAuth2 authentication is not yet supported in Power BI. Editing credentials in the PowerBI.com service will require the service to know how to direct you to the OAuth login page, capture and store the access token credentials from the redirect URL and refresh the token when necessary, none of which the service know how to do today with the tooling available to you.

 

It seems like you have been trying to work around some of those things by writing your custom code, but it might be really hard to have a working end to end solution that way.

 

The good news is that this is something on the team's radar and is in the pipeline. No solid dates or ETAs at the moment, but expect to hear more announcements in this area in the future.

Youssef Shoukry
Product Manager, Power BI

Over 7 years later and nothing has changed re Oauth2 support. What happened to 

"The good news is that this is something on the team's radar and is in the pipeline. No solid dates or ETAs at the moment, but expect to hear more announcements in this area in the future."

 

Can we expect it this decade at least? 

I'm hoping this may be of use - I had ocassion to get data for a customer from the graph API on the Azure B2C service and I needed to use Oauth 2.0 to authenticate to get this data.

I achieved this by using a simple azure function the take a clientID and secret and perform the OAuth get token and submit token in the azure function - so a single http call to the function gets the graph api data protected by Oauth 2.0.

 

The code is in Node.js which is natively supported in azure functions and I guess you could host it elsewhere if you required.

See https://github.com/djburnham/graph-api-azure-fn-proxy for the code.

 

I hope this helps.

 

Kind regards,

                          David.

 

BTW I included my voice in the requests to the dev team to include this functionality in the PowerBi product.

Any news to make it easier to work with a broader variety of REST APIs? Everyone seems to be deprecating simple API key authentication and moving to more complicated Oauth schemes. We need something that works out of the box in PBI.

------------------------------------------------
1. How to get your question answered quickly - good questions get good answers!
2. Learning how to fish > being spoon-fed without active thinking.
3. Please accept as a solution posts that resolve your questions.
------------------------------------------------
BI Blog: Datamarts | RLS/OLS | Dev Tools | Languages | Aggregations | XMLA/APIs | Field Parameters | Custom Visuals

Hello,

 

It is now 8th of January 2019. The last post was 12-11-2017.

iIs it already possible to do this?

Can you refresh using a REST API with OAuth2 in Power BI? 

Anonymous
Not applicable

Hi Youseff,

 

Do we have any news about the ouath2 support?

In case not, do we have any workaround at the moment? Could we update via Gateway or something?

 

Many thanks

Regards

 

Could someone speak to two issues here:
Refreshing published data that use parameterized functions in their queries.  Is this a bug or a known limitation?

And custom solutions for the current implementations of oauth.  Using power query we can implement it quite easily ourselves (with heavy scripting) and I'm kind of curious about the same issue the OP was having.  We don't want to get the oauth token every time and we need to keep track of when it expires so that we can get it again.  Does anyone have any ideas if this is possible and how to do that?

I'm not sure I understand your first question regarding parameterized functions. Can you elaborate a bit more or share an example?

 

As for auto refreshing the token for OAuth, there is no easy way today to achieve that. We are working on some extensibility capabilities for Power BI Desktop that should enable this scenario, but there is no ETA at the moment. However, we are shooting for the first half of 2017.

Youssef Shoukry
Product Manager, Power BI

Is there a hard way to refresh the oauth token?  I figure I need a way to update a variable of some sort that exists in some global capacity.

 

The OP mentioned that he had read in the forums that having functions which take parameters means that published reports that use those functions will cause it not to work.  I think I have seen something similar with my own reports but did not realize that that could be the problem.

For example, I have a "GetAPI" function that you pass the url of the api that I want to access to (such as, GetAPI("/10001/devices/search")).  When I publish that report which uses that function the refresh will not work from the website.

Hi,

Does Power BI support a web api data source that is secured by Azure AD authentication(oauth2)? I've tried many ways but it doesnt seem to work.

It only allows you to authenticate a fixed set of first party Microsoft services. You can't use your own AAD OAuth flow out of the box.

Youssef Shoukry
Product Manager, Power BI

Did some very simple tests yesterday only to see if it was able to refresh the token from within the powerbi service, but it wasn't, so i guess it's impossible to use powerquery code to come around the problem.

 

I'm eagerly awaits Oaut2 support Smiley Happy

 

.....At least I guess I could use the Power BI API to push the data if necessary

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors
Top Kudoed Authors