How do we use Azure Analysis Service with the SQL databases that are behind the firewall?

Question

We are considering using Azure Analysis Services for the BI requirements. Currently, the databases are deployed behind a firewall in a VNET. Since AAS do not have either VNET support or has static IP address range, how do we connect to the databases behind the firewall? I have seen one solution where folks are talking about using the gateway (https://azure.microsoft.com/en-gb/blog/azure-analysis-services-integration-with-azure-virtual-networks-vnets/). But apart from using Gateway, what are the other options I have?

Answer 1

You need to

1. Install On-Premises Data Gateway on VM
2. Add VM Virtual Network to SQL firewall settings
3. Set AAS to use On-Premises Data Gateway
4. Force all AAS traffic through gatway. This is important.

As for other options I don't think there are any that are good in terms of security. AAS IP can change during restarts so you can't make it this way. Unless you write script which changes IP every time but its cumbersome.

Answer 2

At this time gateway is our current production solution for this scenario.

For dev/test you can always examine the connection failure error from AAS to SQL Database, extract the IP address and add a firewall exception for that IP address, or a small range containing it.