Refreshing an oData source dataset and an on premise SQL dataset on the same gateway

[Anonymous]

Hello,

I made a report with some dataset connected to our on premise SQL server from PowerBI Desktop and I added a connection to Dynamic365 cloud with the OData connection with the link provided in the setting page, I used our Datagateway account to access to the Dynamic365 data. We created an admin licence in Dynamic365 to query the data. So in PowerBI Desktop everything works my data load and everything, when I upload the report to PowerBI Service it works but I cannot schedule the refresh and add to our DataGateway the OData source. I know how to configure the SQL server datasource with our gateway, it always works but the OData there's an error message. 

In the Data Source setting page, I have the data source name completed, the data source type is set to OData, the url is there but when I enter the authentification method I get an error. I tried Anonymous didn't work. Then I tried Basic and Windows with the datagateway account name and password (for Basic and Windows in advanced setting we use Organizational) and I always get an error:

"Unable to connect: We encountered an error while trying to connect to . Details: "We could not register this data source for any gateway instances within this cluster. Please find more details below about specific errors for each gateway instance."

here is the detail:

Activity ID:
Request ID:
Cluster URI:	https://wabi-canada-central-redirect.analysis.windows.net/
Status code:	400
Error Code:	DMTS_PublishDatasourceToClusterErrorCode
Time:	Fri Oct 25 2019 09:35:46 GMT-0400 (Eastern Daylight Time)
Service version:	13.0.11109.192
Client version:	1910.2.021

CHG Data Gateway:	Invalid connection credentials.
Underlying error code:	-2147467259
Underlying error message:	The credentials provided for the OData source are invalid. (Source at 'xxxxxxxxxxx our address xxxxxxxxxxxxx')
DM_ErrorDetailNameCode_UnderlyingHResult:	-2147467259
Microsoft.Data.Mashup.CredentialError.DataSourceKind:	OData
Microsoft.Data.Mashup.CredentialError.DataSourcePath:	'xxxxxxxxxxx our address xxxxxxxxxxxxx'
	AccessUnauthorized
Microsoft.Data.Mashup.MashupSecurityException.DataSources:	[{"kind":"OData","path":"'xxxxxxxxxxx our address xxxxxxxxxxxxx'"}]
Microsoft.Data.Mashup.MashupSecurityException.Reason:	AccessUnauthorized
CHG Data Gateway on CHG-SERVICES:	The gateway is either offline or could not be reached.

Anyone has an idea about what's wrong?

[v-shex-msft]

HI @Anonymous ,

According to your error message, it seems like your credentials are invalid so that power bi can't use them to finish the verify of authorization and refresh data.
Have you tested with the same credentials on other connection tools? If this is a case, it means this issue more related to power bi data connector.

Regards,

Xiaoxin Sheng

[Anonymous]

Hello Xiaoxin, the credentials work when I load the data and use this access in powerbi desktop but doesn't work when I use it in the gateway on powerbi service.

[v-shex-msft]

Hi @Anonymous ,

What type of credentials are you used on this data source? Some authorization modes do not fully work on service side, I'd like to suggest you switch to 'organization account' mode and try again.
In addition, have you tried to update datasoruce credentials on power bi service side?

Regards,

Xiaoxin Sheng

[Anonymous]

Hello @v-shex-msft, yes, we use Organization Account. I tried all my access to Dynamic 365 and the connection doesn't work through the API in PowerBI Service, it works in PowerBI Desktop though.

[v-shex-msft]

Hi @Anonymous ,

I'd like to suggest you check and update your data source credentials on gateway side to confirm if they correct verified on power bi service.

If they all work, you can check your database rule and policy settings to confirm if they block power bi requests. BTW, you can also update your gateway to last version if this issue is related to gateway compatibility.

Regards,

Xiaoxin Sheng

[Anonymous]

We updated our datagateway and it still doesn't work.

It seems this was an issue for other people too:

https://community.powerbi.com/t5/Power-Query/Using-the-on-premise-gateway-with-Dynamics-CRM-and-on-p...

[Anonymous]

Is our data gateway setting wrong?

[Anonymous]

Disregard the version of the data gateway, I just updated the version to 242 and it still doesn't work.

[Anonymous · Accepted solution]

Ok, my boss figured it out. You need to leave as optional the cloud source and not add it to the gateway in order for the refresh to work.

here are source where we got the information:

https://ideas.powerbi.com/forums/265200-power-bi-ideas/suggestions/13405644-combine-enterprise-gatew...

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