Showing results for 
Search instead for 
Did you mean: 

Dataset can't be refreshed if workbook contains conn to both SQL AND conn to WEB

I have a workbook, where some data comes from a SQL server running in VM in Azure (ex.

If i publish the workbook to and add the Data Gateway everything works fine - refresh can be scheduled.


If i then add a datasource more -> WEB -> pointing to Excel stored in Onedrive in O365 (have also tried saving to Sharepoint Online) and i publish the workbook, i'm no longer able to Schedule Refresh. It states that



You don't have any gateway installed or configured for the data sources in this dataset. Please install a new personal gateway or configure the data source for an existing data gateway.


Last refresh failed: Mon May 29 2017 20:35:46 GMT+0200 (W. Europe Daylight Time)
Your data gateway (Power BI – personal) is offline or couldn't be reached.
Hide details

Processing error:Your data gateway (Power BI – personal) is offline or could not be reached. Ensure the gateway computer is on and you are logged on to it during the scheduled refresh period.
Activity ID:151e78e6-176e-4d12-a281-f278e78ff06e
Request ID:cc6f6945-43bf-938a-283f-4ef20c798727
Time:2017-05-29 18:35:46Z


Your data source can't be refreshed because the credentials are invalid. Please update your credentials and try again.Your data source can't be queried because the credentials are not provided. Please provide your credentials and try again.



Under credentials i have two sources where i can type credentials


2: Web


For the web i can type credentials and everything seems fine, but for the SQL datasource i can retype the SQL credentials, but it just hangs - no error. The same SQL datasource, whice just earlier was working fine through the enterprise gateway

Status: Delivered
Helper II

A few observations.


If i just use publish workbook with the SQL source, in the Gateway Connection i can then choose "Use a Data Gateway" and select the SQL connection i setup in the Enterprise Gateway with Basic Authentication.


If I only use the WEB source and publish the workbook, the Gateway Connection is = Connect Directly and i enter credentials using OAUTH2


If i publish the workbook with both Sources in, Gateway Connection is grayed out and it's seems to try and default to Personal Gateway. Hope it helps




Please refer to this thread. Currently, on-premise data gateway doesn't support OAuth2 based authentication. We are not able to add the online data sources in data gateway as well as on-premise data sources, so that we can't configure this kind of combined dataset to use data gateway. The workaround is to use personal gateway.
PG have started working on the data gateway to fix this issue. See: Combine enterprise gateway and online data sources.


Best Regards,

Impactful Individual
Status changed to: Delivered
Helper II

2 steps backwards....