Unable to connect to the data source undefined

[ybarsabal]

I have a data source with multiple connections. It starts with a SQL database connection, then I append 4 web connections (Excel files on OneDrive). I have a gateway that allows the SQL database connection to refresh successfully by itself. In Power BI Desktop, the combined data source refreshes successfully, but it fails to refresh on Power BI Service. I assume it has something to do with the gateway, screenshot below, due to the red Xs. But under But I have all the credentials stored under Data source credentials, so I'm not sure why this is failing. 

[ybarsabal]

Update: After troubleshooting further, I narrowed it down to an issue with the data set refreshing from our SQL database through the Gateway. The report no longer has multiple connections and is just a basic query of 1 small table. All the settings in the Gateway and data source in the gateway appear to be correct, and the connection tests successfully, but the datasets fail to refresh. We updated the gateway to the latest version, but no luck. We added a new data source in the gateway, and that doesn't work either. 

[ybarsabal · Accepted solution]

My tech guy worked with Microsoft to resolve the issue.

His response:

"I followed their instructions, set the server certificate to blank, then back to the original value; saved the settings, then restarted the SQL Server windows service. That somehow seems to have fixed it."

[v-xicai]

Hi @ybarsabal ,

1.Please make sure these points: the gateway is online , data source setting is correct, and the schedule refresh configuration is correct. Reference On-premises data gateway ,Manage your data source - SQL Server .

2.You may check the different privacy level settings in Power BI Desktop and Power BI Service. Make sure you have the same privacy level security:

In Power BI Desktop:

Options and Settings --> Data Source Settings

For every Datasource: -->  Edit Permissions

Set Privacy Level: e.g. „Organizational“

Power BI Service:

Datasets --> (Choose you Datasets which does not update properly) --> settings --> Gateway Connection --> Actions --> (expand you cluster to show all Data Sources) --> (Choose relevant Datasource) --> Advanced Settings --> Privacy setting --> set same level (e.g. „Organizational) as in Power BI Desktop

3.You may export the data gateway log by selecting Diagnostics and then selecting the Export logs link near the bottom of the page. Reference: https://docs.microsoft.com/en-us/power-bi/service-gateway-onprem-tshoot#tools-for-troubleshooting.

4.Data refresh can also fail due to expired cached credentials. Clear your internet browser cache by going signing into Power BI and going to https://app.powerbi.com?alwaysPromptForContentProviderCreds=true. This forces an update of your credentials.

You may resolve the issue via the troubleshooting link :https://docs.microsoft.com/en-us/power-bi/refresh-troubleshooting-refresh-scenarios .

If you still have this issue for Power BI, you'd better create a support ticket in Power BI Support to get further help.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-xicai]

Hi @ybarsabal ,

To connect to the excel files located in OneDrive for Business , you need to use Web connector, pay attention to steps detail: Use OneDrive for Business links in Power BI Desktop.

You need to add the web data sources to gateway, see more:https://docs.microsoft.com/en-us/power-bi/service-gateway-data-sources#add-a-data-source.

Note that you also need to select Allow user's cloud data sources to refresh through this gateway cluster option Under Gateway Cluster Settings. See: Merge or append on-premises and cloud data sources.

Best Regards,

Amy

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[nardtmo]

Having same issue were you able to get it resolve