Power BI Refresh failure Azure SQL DB and On Premise SSAS

[RahilBukhari]

Hi

I've got a simple Pbix which connects to Azure SQL DB and an On Premise SSAS Tabular (IaaS in Azure). We have an On Premise Gateway configured and this gateway works absolutely fine because we have Pbix with live connections to the SSAS model published.

When I publish the pbix to PowerBI.com with just the Azure SQL DB connection, the schedule refresh and refresh now completes succesfully. Equally when i just have a pbix with the SSAS Tabular connection which imports data (not Live), the schedule refresh and refresh now completes succesfully (using the On Premise Gateway). 

However if I have both in the same pbix with exactly the same configuration, the refresh fails, complaining  that the Personal Gateway (i have no idea why it's complaining about this as we don't have the Personal Gateway configured) is not available. The Azure SQL DB is configured to enable connections from other Azure services and as I mentioned above the Pbix will refresh succesfully when it just has the connection to the database. Appreciate some guidance on this. 

 

[Anonymous]

I have same issue. 

It fails sometime with no reason.

[v-haibl-msft]

@RahilBukhari

You need to add these two data sources to the same gateway, one for Azure SQL and one for on-premises AS as below.

Best Regards,
Herbert

[RahilBukhari]

Thanks @v-haibl-msft

I tried adding the Azure SQL DB to the same gateway however it couldn't establish a connection and when i read up on it, you shouldn't have to create a gateway since it's already an Azure service, which has enabled access to other Azure services. I assumed this is why I had this error but the fact that you can create the connection suggests something else might be going on.

[v-haibl-msft]

@RahilBukhari

In Azure portal, have you enabled 'Allow access to Azure services' and set server firewall to provide access to client IPs?

Please refer to this document.

Best Regards,
Herbert