Sharepoint connection fails when adding SQL source

[IanCockcroft]

Hi guys,

I have a sharepoint list source which works fine when I publish and refresh the report on the service.

as soon as i add a SQL source the connection breaks.

in the desktop it works

The SQL on its own works.

But the 2 together dont

any idea why and how to fix it?

[IanCockcroft]

Hi guys, we worked out from the refresh error that both source need to have the same security level. i.e. PRIVATE, ORGANISATIONAL,  PUBLIC etc

thanks for your help

[Anonymous]

Hi,

Thanks for the solution freginier  offered, and i want to offer some more infotmation for user to refer to.

hello @IanCockcroft , you need to allow gateway to refresh your colud data source, you can set it in gateway settings in Service, you can refer to the following link.

Merge or append on-premises and cloud data sources - Power BI | Microsoft Learn

And after you add the sql data source in report, you need to upload the report to Service again, then set the data source settings.

Best Regards!

Yolo Zhu

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

[freginier]

Hey there!

If I understood correctly, your SharePoint list connection works fine when published alone, but breaks when you add a SQL data source. The issue does not occur in Power BI Desktop but happens in Power BI Service when refreshing.

Here's a possible solution for you, to convert SharePoint Connection to an On-Prem Gateway. SharePoint Online (cloud) works without a gateway, but when mixed with SQL Server (on-prem), Power BI requires a gateway for both. You can do this by: 

- Go to Power BI Service → Manage Gateways.
- Add SharePoint Online to the same on-prem SQL gateway.
- Set the SharePoint URL as a data source under the SQL gateway.

Also, you should enable Data Source Credentials in Power BI Service because Power BI Service needs authentication settings for each data source. Here's how: 

- In Power BI Service → Dataset Settings.
- Find both SharePoint List and SQL data sources.
- Ensure both have valid credentials.
- If the SQL data source is using a Windows Authenticated account, try switching it to Database Authentication.

Hope this helps!

😁😁