Rajendra Gupta
Diagram that shows how the TCP session is established via the Azure SQL Database gateway and all subsequent packets flow via the gateway.

Azure SQL Database Connectivity architecture

March 24, 2022 by

The article Configure IP firewall rules for Azure SQL Databases, discuss the configuring server and database IP firewall rules for Azure databases. In this article, we will explore setting applicable to Azure databases that control connectivity to the database.

Pre-requites

This article assumes you are familiar with Azure SQL Database, its deployments, and basic configurations. In case you don’t, refer to SQLShack articles from the URL https://www.sqlshack.com/category/azure/

Connectivity setting in Azure portal

In the Azure portal, navigate to Azure SQL Server and Firewalls and virtual networks below.

Firewalls and virtual networks

Deny public network access

By default, Azure SQL Database enables connections using public endpoints. Users can connect to SQL using the public endpoint after validating access using the VNET firewall rules, database-level firewall or the server-level firewall.

If you modify the configuration – Deny public network access as Yes, users must use the private link for connection to Azure SQL. The private link allows connection using the private endpoint.

Note: We are not exploring private links in this article. Therefore, you can refer to Azure Private Link for Azure SQL Database for understanding private endpoints.

The following flow diagram shows the high-level connection architecture.

  • Deny public network access = True: Azure checks for private endpoints and allows server scoped connection
  • Deny public network access = False:
    • If there is a match found in the VNET firewall rules, provide server scoped access
    • If no VNET firewall rules, check for database-level access. If IP is allowed for database-level firewall, the user gets database scoped access
    • If no VNET and database-level firewall access is configured, Azure checks for the server-level firewall. If it is configured, the user gets server scoped access. Else, the connection is denied

Diagram showing connectivity when Deny public network access is set to yes versus when Deny public network access is set to no.

Image Reference

Note: You cannot deny public network access unless private endpoints are configured. If you try to disable it, you get error 42102 – Unable to set Deny Public Network Access to Yes since there is no private endpoint enabled to access the server. Please set up private endpoints and retry the operation.

Note: You can refer to the article, Change public network access via Azure CLI, PowerShell.

Connectivity architecture

This section looks at internal components of how a user with the direct network traffic connects to the Azure SQL Database.

To establish a connection to Azure SQL Database, a client connects to the gateway (GW) with the public IP address on port 1433. The gateway redirects the connection to a database cluster with the appropriate connection policy – Redirect or Proxy. The database cluster forwards traffic to the desired database.

Diagram that shows a high-level overview of the connectivity architecture.

Image Reference

Azure SQL Server supports the following connection policies.

Redirect:

In the redirect connection policy, a client establishes a connection directly to the SQL database node. The Redirect policy does not require a gateway for subsequent connections, and therefore, it provides better throughput and reduced latency. By default, if the connection arises within Microsoft Azure, Azure uses the redirect connection mechanism.

To understand the redirect mechanism, we will use the Wireshark network monitoring tool inside the Azure VM. From the Azure VM, we initiate the connection to Azure SQL Database and capture the network traffic.

  • My Azure VM IP address: 10.0.0.4

Azure VM

  • Run nslookup command to capture Azure SQL DB public IP address. It shows the IP address 104.211.96.159

nslookup command

My Azure SQL Server is in Central India; therefore, it uses the Gateway IP addresses in the below range documented at Microsoft documentation – Gateway IP addresses

Gateway IP addresses

  • In the first step, once we initiate a new connection request to Azure SQL DB on port 1433 from the Azure VM, the Wireshark captures the TCP connection request as highlighted below.

Note: You can download and install network protocol analyzer Wireshark from the URL https://www.wireshark.org/

protocol analyzer Wireshark

  • It assigned a dynamically identified port (in this example – 11012) and sent it to the client application.

dynamically identified port

  • The application uses this port 11012 and connects to the target Azure SQL Database.

Connection to Target DB

  • For the subsequent connections, it does not require a gateway. The application can directly connect to the Azure SQL Database using the port received.

In summary, in the redirect approach, the client requires the following.

  • The client should allow outbound communication from a client to Azure SQL DB region gateway IP addresses on port 1433
  • The client should allow outbound communication from the client to Azure SQL IP addresses( as per region) in the range of 11000 to 11999

architecture overview

Image Reference

Proxy

The proxy mode is the default mode for traffic coming out from the Azure network (Internet). It uses Azure SQL Database gateways for communication. Therefore, the client requires outbound communication on port 1433 to the Azure SQL gateway.

The following diagram shows the proxy connection policy.

Diagram that shows how the TCP session is established via the Azure SQL Database gateway and all subsequent packets flow via the gateway.

Image Reference

Default

Azure automatically assigns the default connection policy on all servers during deployments.

  • Redirect: For all client connections coming from inside Azure network such as Azure VM
  • Proxy: For all client connections coming from outside Azure network such as Internet

Note: As we noticed that the Redirect connection policy provides more throughput and less latency, it is recommended.

View and modify the existing connection policy

We can choose the required connection policy in the Azure portal firewall and network connection for Azure SQL DB.

existing connection policy

You can use the following Azure CLI script as well to list the connection policy and change it. You can use Azure Cloud Shell to run the CLI scripts.

Note: Refer to article An overview of Azure Cloud Shell for cloud shell configuration for Bash or PowerShell scripts.

AZURE CLI command output

The following script updates the connection policy to Redirect.

Update Azure resources

As shown below, the az resource show returns Redirect for my existing Azure database.

Redirect connection Policy

The following script updates the connection policy to Proxy.

Proxy  connection Policy

Minimal TLS version

We can configure the minimum Transport Layer Security (TLS) version for Azure SQL Database. As shown below, it supports TLS 1.0, 1.1 and 1.2 versions.

Minimal TLS version

Suppose you configure the minimum TLS 1.1 for Azure SQL DB. It shows that users with TLS 1.1 and 1.2, whereas TLS 1.0 connection requests are rejected. Therefore, you should test your application and configure the minimum TLS accordingly. However, it is recommended to use the latest TLS version for security purposes.

You can configure the minimum TLS version either in the Azure portal or Azure CLI. Modify the TLS version and save the firewall request.

Modify TLS version to 1.1

You can run the following CLI script to check the minimal TLS version for the SQL database. minimal TLS version

The az sql server update script updates the minimum TLS version. For example, the below script updates the TLS version to 1.2

Change TLS version to 1.2

If we rerun the az sql server show, it returns the updated minimum TLS version.

az sql server show command

Note: Users using the TLS version less than the configured minimum TLS version for Azure SQL Database get the Error 47072 – Login failed with the invalid TLS version.

Azure SQL Connectivity Checker

We can use the PowerShell script to test the connectivity from the local machine and Azure SQL Database. The script is stored in the GitHub repository https://github.com/Azure/SQL-Connectivity-Checker. It supports Azure SQL DB, Managed Instance, and Azure synapses.

Open the URL and copy the script in PowerShell ISE. Modify the parameter – Azure server name, database name, user credentials. Execute the script, and it checks the connection to the gateway, redirects policy check on different ports. If there is any issue in reaching out to Azure SQL DB, the test fails, and you can troubleshoot further on specific components.

Azure SQL Connectivity Checker

TCP test connection

Connection to master and Azure database

To perform the demo, I removed the client IP from IP firewalls. Therefore, the Azure firewall will not allow connection to the client to Azure SQL Database. Now, I re-executed the PowerShell script, and it gave the error in testing the connection to the master database. As per the message, the client IP address is not allowed to access the server.

Error in connection to client IP from IP firewalls

If there is any connection failure, the script also gives recommended actions. For example, my demo says to create a firewall rule using the Azure management portal or sp_set_firewall_rule stored procedure in the master database. If you need help, you can refer to the URL given in the recommended action section.

Recommended Actions

Conclusion

The article explored the Azure SQL Database connectivity architecture for a client connecting from inside the Azure or public network (internet). Further, we discussed the connection policies, minimum TLS version and access to public network configurations. The Redirect connection policy is a recommended way because it offers minimum latency and better throughput.

Rajendra Gupta
3,091 Views