Forum Discussion
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
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.
16 Replies
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.
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.
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 :smileyhappy:
.....At least I guess I could use the Power BI API to push the data if necessary
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.
Is oauth2 now implemented in power BI or not. Any update in future?
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!
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/Hi karekaasamoen or anyone who finds this old thread,
I was having the same issue of refreshing and authentication working in Power BI Desktop and not in the Power BI Service.
A solution to this which worked for me is to set the authentication method to "anonymous" and privacy level setting to "Public" while also skipping the test connection in the settings of the published semantic model. As the autentication is handled in the query and not through the service, the semantic model refreshes correctly, even when set to refresh on a schedule.
Hope this helps,Eoin
Project Controls Engineer, Jones Engineering
