Scheduled refresh on Dynamics data source - fails

[Anonymous]

Hello!

My Gateway cannot refresh my Dynamics data source.

This is funny, while it works fine on desktop (only way to update data).

My pbix reads a date table from sql server and rest of the tables are coming from dynamics, therefore the dynamics source also needs to be in the gateway.

When I set up the ODATA-source (with the same account/pw as in desktop), I get 'unauthorized' error.

From logs i read it is an external (non-gateway error):
.MashupCredentialException (0x80004005): The credentials provided for the OData source are invalid.

BUTWHAT? I tried some three licensed users with access to the data source and none of them work.
Data source is at Organizational privacy level, (likewise with sql source). It uses windows authentication (while oauth2 not available in OAUTH source).

Any ideas, now I've been stuck with manual updates and it really bugs me.. ;(

All fresh ideas are welcome, I've read the previous answers already..

[v-easonf-msft]

Hi , @Anonymous 

Please  check if  these related posts help.

https://community.powerbi.com/t5/Power-Query/Cannot-schedule-refresh-for-datasource-with-Dynamics-CRM-and-SQL/td-p/149164

https://ideas.powerbi.com/ideas/idea/?ideaid=1833782d-044a-43c4-b427-65722c50d789

https://powerbi.microsoft.com/zh-cn/blog/privacy-levels-for-cloud-data-sources/

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Is this this limitation still there:
"On premises and cloud data sources cannot currently be used in the same dataset."

I've read all the other posts already and they really do not answer to the problem.

I've tried the actual gateway service account for login, also two other account which have access to env, application and entities.. still getting the error, although they work when logging in to dynamics online.

So this happens when trying to refresh the data via gateway.

[v-easonf-msft]

Hi ， @Anonymous 

If  you want to refresh a report with queries that combine on-premises and cloud data sources,please check  whether  you have apply the option "Allow user's cloud data sources to refresh through this gateway cluster ..." under the Gateway Cluster Settings.

For more details,please erfer to this document.

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hello, tried that already, also played with the permission levels..

If I only have the dynamics connection in report, I do not need to use gateway and then the authentication and update works!

When I add data from on-prem sql, I need to enable the gateway and then the dynamics authentication starts to fail.
(AAD is synced with our local ad -> gateway connection with ad-account should work (works without gateway at least)

Could this some way relate to:
a) gateway service account (currently I use a local account)

b) Microsoft account that is used when logging in when configuring the gateway?

[v-easonf-msft]

Hi , @Anonymous 

Does the privacy level is consistent in both desk and service(needed to be set to NONE)?

And  it is suggest to use the "OAuth2"  in  service  rather than "windows" .

In fact ,it's necessary to add  Odata data sources under “Manage gateway”,you just need to check the option "Allow user's cloud data sources to refresh through this gateway cluster" under the configured data source of sql.

Have you tried to use personal gateway  if possible?

Best Regards,
Community Support Team _ Eason

[Anonymous]

Thanks for good hints, I'll need to run through the permission levels yet once.

Although I have the latest gateway version, Oauth2 was not available in the gateway for OAUTH data pipelines (funny).
Oauth2 seems to work only with SQL server data pipelines (primarily for Azure sql I guess..).

I keep trying.. 🙂

[v-easonf-msft]

Hi , @Anonymous 

Could you please tell me whether your problem has been solved?
If yes,you could mark the helpful reply  or  share your solution to close this thread  .So that other community members will easily find the solution when they get the same issue.

If not, please kindly raise it to the support team for better advice, thanks a lot!

https://powerbi.microsoft.com/en-us/support/ 

Best Regards,
Community Support Team _ Eason

[Greg_Deckler]

You could check the Issues forum here:

https://community.powerbi.com/t5/Issues/idb-p/Issues

And if it is not there, then you could post it.

If you have Pro account you could try to open a support ticket. If you have a Pro account it is free. Go to https://support.powerbi.com. Scroll down and click "CREATE SUPPORT TICKET".