Posted by This blog post gives an overview of a typical hybrid Azure/on-premises BI data platform network architecture. The most important settings, configurations, shortcomings and other things you should take into account are described. For more details and in-depth documentation links to Microsoft pages are provided at the bottom of this post.
The following common scenarios are covered:
· Write data from on-premises data sources to an Azure SQL Database or Azure SQL Data Warehouse using SQL Server Integration Services.
· Use the On-Premises Data Gateway (or ADF gateway) to make on-premises sources available for various Azure services like PowerBI.com and Azure Data Factory, or to be able to process an Azure Analysis Services cube with on-premises data.
· Connect to an Azure SQL Database using client tools or PowerBI.com.
· Connect to an Azure Analysis Services tabular model using client tools or PowerBI.com.
The architecture diagram below (click to open) shows how the different on-premises resources connect to Azure services. For every connection the ports that should be open in your firewall, network protocols, encryption methods and authentication types are shown.
1. For the SSIS ADO.NET driver, use the following connection string parameters to use an encrypted connection: Encrypt=True and TrustServerCertificate=False.
2. All connections to Azure SQL Database require encryption (SSL/TLS) at all times while data is “in transit” to and from the database.
3. Set “Allow access to Azure services” property for your Azure SQL Database server to ON. Be aware that this means not only the Azure services in your Azure Subscription can reach your Azure SQL Database server, but all Azure services worldwide, also from other customers.
4. Only SQL Server Authentication is currently supported when connecting to an Azure SQL Database from SSIS, Power BI Desktop or Excel. Power BI Desktop models deployed to PowerBI.com will therefore also connect to an Azure SQL Database using SQL Server Authentication. The latest version of SQL Server Management Studio does support Azure Active Directory Integrated Authentication.
5. To access Azure SQL Database from your local computer, ensure the firewall on your network and local computer allows outgoing communication on TCP port 1433. Outgoing traffic can be filtered to allow only traffic to Azure datacenter IP addresses for your region. This is sometimes a requirement before organizations want to allow outgoing traffic through port 1433. Inbound connections for port 1433 can be blocked.
6. By default in Power BI Desktop, the “Encrypt connections” option is checked for your data source. If the data source doesn’t support encryption, Power BI Desktop will prompt to ask if an unencrypted connection should be used.
7. Port 443 is used for default communication to PowerBI.com. Ports 5671 and 5672 can be used for Advanced Message Queuing Protocol (AMQP). Ports 9350 thru 9354 can be used for listeners on Service Bus Relay over TCP. If you use the ADF gateway to write data to Azure SQL Database or Azure SQL DW outgoing traffic through port 1433 needs to be allowed.
Links:
Jorg Klein's Blog 
I am trying to design Azure BI architecture for a client and wanted to ask a question in reference to your article ‘Hybrid-BI data platform network architecture’.
You stated there being data gateway server to communicate with on prem network. In my design i have VM which has azure SQL db within the tenant along with Power Bi and there is ExpressRoute in place. Does my client still need Data gateway for power bi desktop or any other ad hoc on prem database?
LikeLike
Hi Mohammed,
You also need data gateways for data sources that reside on Azure VMs. Consider using Azure SQL Database instead so no gateway will be required.
Good luck!
LikeLike
Many thanks for the interesting post, I think it will come in handy to me.
LikeLiked by 1 person
Hello! How about the acost of connect our onprem SSIS to the cloud? Is some table to calculate how much or what is the susbscription do I have to buy to work with? Thanks!
LikeLike