Summary

A user may encounter a problem when using a data reporting tool like Tableau or Python installed on an Azure hosted server and can't establish a successful connection to the Enterprise Insights(EI) reporting database.

Firewall permissions for the server have been granted for connections to Enterprise Insights Azure SQL database on port 1433

Also, this problem is specific to connection attempts from an Azure hosted server and not Azure Cloud services like Power BI. 

Environment

Jira Align

Diagnosis

• Connection attempts from the Azure Hosted server fail with network related errors like 

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Login timeout expired.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x102.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online..

• Firewall permission to the EI Azure SQL database gateway IPs has been permitted on port 1433
• Network capture of the connection attempts shows successful connections on port 1433 and multiple "TCP Retransmission" messages.

Cause

Azure SQL Database has three different connection policies as documented here: Azure SQL Database and Azure Synapse Analytics connectivity architecture - Connection Policy

Connections to the Azure SQL Database from within Azure (i.e. the Azure Hosted server) use the Redirect connection policy by default to establish connections to the EI Azure SQL Database.

With the Redirect policy, firewall permission to only the EI Azure SQL database gateway IPs on port 1433 is insufficient for a successful connection. Therefore, connection attempts fail until the additional firewall permissions are configured.

Solution

Ensure that the Azure Hosted server has all the requirements below to utilise the Redirect connection policy;

• Allow outbound communication from the client to all Azure SQL IP addresses in the region on ports in the range of 11000 to 11999
• Allow outbound communication from the client to Azure SQL Database gateway IP addresses on port 1433.

Reference: Azure SQL Database and Azure Synapse Analytics connectivity architecture - Connectivity from within Azure