Publishing report with composite models with azure sql gives gateway error

[Tom_V]

We are trieing to use composite models in powerbi.

What we are testing is using a azure sql connection and an excel, in the desktop app this works without problems.

Yet we want to publish this to the web but we get an error after publishing: "Publishing succeeded, but the published report can't connect to the data source because we were unable to find a gateway. Please install and configure an enterprise gateway"

And when we visit the website we get the error: "This data source can't be accessed by a data gateway: Server: ********.database.windows.net; Database: *******"

It looks like the API allows us to change the connection string, yet it doesn't change the error in the web app, and the settings UI only allows us to set up a gateway connection and not a data source credentials.

Are we doing possibly something wrong or is this a known issue?

[Anonymous]

We have the exact same issue. We have a pbix model made of 2 sources: 1 Excel on-premises file (import mode), 1 Azure SQL db (direct query). We create the connection for the Excel file in the gateway, but there is still an error message for the Azure SQL db. It is asking for a connection definition for this one too, which makes no sense. So that's what we did, and define a connection for the Azure SQL db as if it was an on-premises SQL Server source, and it works.

It think there is a glitch in the logic to detect which data source requires a connection or not in the gateway with Composite model, if we mix on-premises and Azure data source in the same model.

To prove it, I change the location of my Excel file for a onedrive location. So both Excel and Azure SQL db are in the cloud. I disabled the gateway for that model, and it works fine. But as soon 1 of my source is on-premises, Power BI seems to be lost and is asking for a gateway connection for the Azure one.

[Anonymous · Accepted solution]

Gateways are an all or nothing deal. Either they handle all of your connections, or none of them.

Earlier this year a new feature was added which allows the Gateways to route to cloud sources without configuration of those sources.  But this needs to be turned on under manage gateways:

[Anonymous]

Hi Ross. We didn't know that gateway usage was "an all or nothing deal" !?!! We though only on-premises data sources would require a gateway connection, not the cloud ones, eventhough they are in the same model. Can you point me to this particular specification please, would like to document the issue internally. Many thanks !

[Anonymous]

I don't know a specific document that points it out.  I did a quick check and nothing i could find comes up.  It is how it works though.  Just get that check box turned on in the managed gateways and it will pass through all of the cloud sources in the manner you were expecting.

[Anonymous]

Many thanks a lot Ross, I think I found it here: https://docs.microsoft.com/en-us/power-bi/service-gateway-mashup-on-premises-cloud

[Anonymous]

Well done, yes looks like it.  Knew it had to be somewhere!

[v-danhe-msft]

Hi @Tom_V,

Based on my test, after you have published your report, you need to configure a gateway for your local excel file, then it could work on the service:

Regards,

Daniel He

[Anonymous]

Have you configured an On-Premise data gateway for the connection to the Excel file?