Power Bi Server cannot refresh data from Azure Synapse SQL pool

[Anonymous]

Hi all,

I have a problem with Power Bi server and data refreshing. My setting is as follows: my primary data source is Azure Synapse SQL Pool where I have all the data in one database, different schemas and tables. The server allows for Azure services and resources to access the server. I have built dashboards in Power Bi Desktop where I use database credentials and custom SQL clauses to connect to Synapse. Additionally in some dashboards I also have one web based source and one Excel source. The data flows in with Import mode and everything works perfectly in my Power Bi Desktop.

Now when I publish my dashboards to Power Bi Server and try to refresh the data, the server UI prompts me to use a data gateway. I am really struggling to understand why would two cloud based servers (operated both by Microsoft) need an additional data gateway between these instances? If I need the gateway, why is that and what would be the best practise in setting it up? It does not seem right to install the gateway on my own laptop for sure and setting up a VM in Azure just for a gateway is too much of an investment in addition to Synapse server.

Thanks in advance for the advice!

[Srini1855]

Its the same problem for me . Azure synapse and azure sql data bases . Any suggestions ?

[gldaniel]

We are experiencing the same issue.  Did you get an answer for this?

[v-jayw-msft]

Hi @Anonymous ,

As you said you have an excel data source?

Hope these document can help you.

https://docs.microsoft.com/en-us/data-integration/gateway/service-gateway-app

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-data-sources

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-mashup-on-premises-cloud

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-enterprise-manage-scheduled-refresh#use-the-data-source-for-scheduled-refresh

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[Anonymous]

Hi @v-jayw-msft,

As I said in the original post, the Excel data source is not the problem here - even if I remove the Excel source, the Azure based Synapse SQL pool cannot seem to connect without gateway. I understand that I need a gateway for the Excel source, that is clear. But it is not clear to me, why would a cloude source (on Azure) need an on-premise gateway?