Error 10060: Configure Azure NSG for Azure DB and Azure DW Connectivity

Customer running Virtual Machines in the cloud require additional layer of security and as a cost-effective option is to implement Network Secure Gateways to control inbound and outbound traffic to and from their Azure hosted services.

In the light of one wanting to connect to the Azure SQL DB or Azure SQL DW using SQL Credentials, specific ports are required through the NSG. Both Azure SQL DB and Azure DW allow Secure VNET connections and to make use of this configuration Destination Service Tags are to be applied to the Firewall Rule.

If the NSG is not correctly configured Error 10060 will be returned as a connection to the database could not be established.

For a list of required ports which need to be opened in addition to this article for AAD or Hybrid AD Domain Scenarios for Windows based Authentication please refer to following article : https://docs.microsoft.com/en-us/azure/active-directory/hybrid/reference-connect-ports

Problem Scenario

Customer configured their NSG to Allow port 1433 traffic to their Azure DW as per our documentation Azure DW requires this for connectivity to complete.

A successful connection test was performed by the customer via telnet to Port 1433 of the Target Server name.

When connecting to the Target DW database we receive the following exception when connecting with SQL Credentials.

When using SSMS Error 10060 is being returned and clearly states a connection attempt failed because the connected party did not respond after a period of time.

Solution:

The NSG had port 80, 443 and 1433 allowed through the firewall which according to most peoples understanding is sufficient to allow access to Azure DW.

As per the Azure Connectivity architecture when the connection is within the Azure boundary we make use of Redirection which requires port Range 11000 to 11999 to be opened as well, when the host is outside of the Azure network it would be a Proxy connection making use of Port 1433 only.

https://docs.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture

The Firewall rules on the Logical Server allow you to change the connection policy for all inbound connections to Proxy, when doing so only Port 1433 will be required. (The Default is Proxy External to Azure, Redirect Internal to Azure)

When the following NSG Firewall rule was created we were able to connect successfully to the Target DW database using SQL Credentials.

The NSG will be associated to the VNET which the Source VM has been created in I will be limiting access to the VNET only on my DW and in order to do so will make use of the Service Tag for SQL as well in my NSG Rule.

Ensure that the Destination references : Service Tag

Ensure that the Destination Service Tag selected is : Sql

For additional security select the Source as your Source VM VNET and not Any as per the example below

In accordance with Azure DW Best practices we limit the connection to my Azure SQL Server which hosts the DW to the following VNET only thus completing the secure end to end connection

For additional information on Service Endpoints refer to following article https://docs.microsoft.com/en-us/azure/sql-database/sql-database-vnet-service-endpoint-rule-overview