Published PBI showing both Cloud and OnPrem Sources after appending queries

[AdamB_]

Hi all,

So i've got two sources:

• Cloud
• On Prem

For the report i've published, I needed to append the data sources so I added the cloud source and then appended via PBI desktop. The gateways have already been configured (one uses Azure SQL, the other is a standard on prem) however when I publish the report, it shows:

Blue highlight = cloud source

Green highlight = on prem

This ends up breaking the scheduled refresh.

Anyone have any ideas how this could be fixed?

[PankajBoldara]

Hello All,

I am facing same issue, one source is on-prem SQL server and other is on SharePoint Online Excel files.

The report refreshes successfully on Power BI Desktop, but refresh fails. The error message is below.

 

[AdamB_]

Hi @PankajBoldara , I ended up solving this. The way I did it was using a data flow as my on prem source and then merging with cloud afterwards.

Hope that helps

[Anonymous]

Hi @AdamB_ ,

Based on your error message：

Please make sure that the username and password you’ve provided are correct. Remember, these fields are case-sensitive.

If you’re using a connection string to connect to your SQL source, ensure that it’s formatted correctly and includes all necessary information. and ensure that your SQL source is set up to accept connections from your current location. You might need to adjust your firewall settings or enable certain IP addresses.

Best regards.
Community Support Team_Caitlyn

[Anonymous]

Hi @AdamB_ ,

You can use the on-premises data gateway to merge or append on-premises and cloud data sources in the same query. This solution is helpful when you want to combine data from multiple sources without having to use separate queries. It applies only to datasets that have cloud and on-premises data sources merged or appended in a single query. For datasets that include separate queries, for instance, one that connects to an on-premises data source and the other to a cloud data source, the gateway doesn't execute the query for the cloud data source.

Please you have added one or more data sources to the gateway and have selected Allow user's cloud data sources to refresh through this gateway cluster options.

For details：

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

Best regards.
Community Support Team_Caitlyn

[AdamB_]

Hi @Anonymous ,

So I have already checked the allow user's cloud data sources to refresh through this gateway cluster for my on prem gateway. The source as shown above changes to "optional- add to gateway" however when I refresh it still throws up the error stating:

• The credentials provided for the SQL source are invalid. (Cloud source.). The exception was raised by the IDbCommand interface. Table: Cloud Table.