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.
Load data from SQL Server into Azure SQL Data Warehouse (SSIS)
On-premises data gateway in-depth
Data Factory Data Management Gateway
Securing your SQL Database
Overview of Azure SQL Database firewall rules
Use Azure Active Directory Authentication for authentication with SQL Database or SQL Data Warehouse
What is Azure Analysis Services?