Analysis Services Tabular Model - Processing Data - Error On-Premise Gateway is required to access the data source

Question

Analysis Services Tabular Model - Processing Data - Error On-Premise Gateway is required to access the data source

P Johns 16

Hi,

We were working with Analysis Services and a Private Endpoint SQL MI. An On premise Gateway was required for this set up but this was not a full PaaS so removed.

In Azure we have removed an On Premise Gateway and deleted the Gateway VM, also switched the SQL Managed Instance to Public. A new Analysis Services has since been created in Azure. Tabular Models can be created but on processing data we get:

"Failed to save modifications to the server. Error returned: 'On-Premise Gateway is required to access the data source. Please install a unified gateway for the server sqlasprod or disable AlwaysUseGateway setting if it's enabled"

The Gateway reference has been removed from the Power Apps Platform and also this script below has been run as the poster said that the Gateway was still in existence unless this script was run:

Remove-AzureRmResource -ResourceId "/subscriptions/xxxx/resourceGroups/xxxx/providers/Microsoft.Web/connectionGateways/xxxxGateway" -Force

There is still a reference to the deleted Gateway somewhere in Azure but we need to know how to remove this please? case raised with Microsoft but no resolution found as yet.

Paul

CarrinWu-MSFT 6,891 Reputation points

2022-02-17T02:22:36.917+00:00

Hi @P Johns , welcome to Microsoft Q&A! I just working with on premise SSAS. Based on my research, you could refer to this similar theard, see below:

I opened a case with Azure tech support and found a resolution. Thanks to Haidong Huang who talked to the dev team the response was:

"The dev team gave me an answer
Not all Azure/cloud data sources have been enabled for direct connectivity without gateway. Just a matter of resources and priorities.
I will file a design change request so the dev team knows there is such a need from the customer

At this time, a gateway is needed for this type of data source."

So anyone trying to access Azure Tables from Azure Analysis Services will need to spin up a VM, install a gateway, and connect it to your AS instance. How to do that can be found here:

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway-install
P Johns 16 Reputation points

2022-02-17T15:07:54.793+00:00

Hi CarrinWu,

Thanks for your reply, but unless this information in the Microsoft link below is incorrect, we should not need a vm gateway as the SQL MI is set to Public and we are all Paas.

Based on: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource, they say:

Azure SQL Managed Instance is supported. Because SQL Managed Instance runs within Azure VNet with a private IP address, public endpoint must be enabled on the instance. If not enabled, an On-premises data gateway is required.

2 answers

Your answer

CarrinWu-MSFT 6,891 Reputation points

2022-02-17T02:22:36.917+00:00

Hi @P Johns , welcome to Microsoft Q&A! I just working with on premise SSAS. Based on my research, you could refer to this similar theard, see below:

I opened a case with Azure tech support and found a resolution. Thanks to Haidong Huang who talked to the dev team the response was:

"The dev team gave me an answer
Not all Azure/cloud data sources have been enabled for direct connectivity without gateway. Just a matter of resources and priorities.
I will file a design change request so the dev team knows there is such a need from the customer

At this time, a gateway is needed for this type of data source."

So anyone trying to access Azure Tables from Azure Analysis Services will need to spin up a VM, install a gateway, and connect it to your AS instance. How to do that can be found here:

https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-gateway-install
P Johns 16 Reputation points

2022-02-17T15:07:54.793+00:00

Hi CarrinWu,

Thanks for your reply, but unless this information in the Microsoft link below is incorrect, we should not need a vm gateway as the SQL MI is set to Public and we are all Paas.

Based on: https://learn.microsoft.com/en-us/azure/analysis-services/analysis-services-datasource, they say:

Azure SQL Managed Instance is supported. Because SQL Managed Instance runs within Azure VNet with a private IP address, public endpoint must be enabled on the instance. If not enabled, an On-premises data gateway is required.

Answer 1

P Johns 16

The Data Processing error VM Gateway required to access datasource turned out to be not related to the context of the message at all, it was just an issue with the connection string. A single space before the port number was the issue and once removed the data processing could take place. The connection string was copied from elsewhere where a space was working fine, e.g. SQL MI server properties. Resolved through Microsoft support and they will raise the incorrect message with the production team. Thanks.

Peter Rush 61 Reputation points

2022-07-28T10:13:33.77+00:00

I had a similar issue with Azure Analysis Services unable to access an Azure SQL database. The error message referred to "'On-Premise Gateway is required to access the data source. Please install a unified gateway for the server" but actually the issue was an invalid server name in the connection details. Thanks for the hint!

Answer 2

Darren Gosbell 2,376

Have you checked the AlwaysUseGateway setting on your AzureAS instance has not been set to true? This article has a screenshot half way down showing where you can change this setting.
https://faun.pub/secure-network-connectivity-between-azure-analysis-services-and-azure-sql-4f5444c58e76

Share via

Analysis Services Tabular Model - Processing Data - Error On-Premise Gateway is required to access the data source

2 answers

Your answer