Dataset connection frustration - 2 sql server data sources

[joglidden2]

I created a report that connects to two (2) data sources: sql server on-prem, and sql server in Azure ("cloud"). The report managed to connect to both using direct query, and I published it to the service.

The on-prem sql sever has a configured data gateway, which works.

After publishing the report, PBI in its infinite wisdom, attempts to add both data sources to the gateway. Why? Of course the Azure sql server won't work, and I don't have access to the on-prem data gateway to configure it, but this should not be necessary!

If I publish two separate reports, using the two different data sources, the gateway works fine for the on-prem sql server, and the "data source in the cloud" connection for the Azure sql server works fine. 

So, then the easy thing to do would be to create a new report and "Create a live connection to datasets in the Power BI Service", which is misleading and should be renamed "Create one live connection to one dataset in the Power BI Service". Because obviously, it's not possible to connect to two datasets in one report. 

Any suggestions would be most appreciated. I'll start a service ticket with Microsoft shortly if I don't get a solution posted here.  

[v-zhenbw-msft]

Hi @joglidden2 ,

Do you solve this issue?

If you've fixed the issue, please kindly share your solution. 

It will be helpful for more people.

Best regards,

Community Support Team _ zhenbw

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

[joglidden2]

I started a support ticket with MS PBI. I'll post the solution to the problem here for the public.