In this article, we will explore the connectivity architecture of Azure SQL Database and discuss various concepts related to configuring server and database IP firewall rules for Azure databases.

Prerequisites

This article assumes that you are familiar with Azure SQL Database, its deployments, and basic configurations. If you need a refresher, you can refer to SQLShack articles on Azure SQL Database.

Connectivity Settings in Azure Portal

In the Azure portal, you can navigate to Azure SQL Server and configure the Firewalls and virtual networks settings. By default, Azure SQL Database enables connections using public endpoints. However, you can modify the configuration to deny public network access and require the use of private endpoints for connection to Azure SQL.

When denying public network access, Azure checks for private endpoints and allows server-scoped connection. If no private endpoints are configured, Azure checks for VNET firewall rules, database-level firewall, and server-level firewall in that order to determine the appropriate access level.

Connectivity Architecture

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

The Redirect connection policy allows a client to establish a direct connection to the SQL database node without requiring a gateway for subsequent connections. This provides better throughput and reduced latency. On the other hand, the Proxy connection policy uses Azure SQL Database gateways for communication.

Modifying Connection Policy

You can view and modify the existing connection policy in the Azure portal or by using Azure CLI. By default, Azure assigns the Redirect connection policy for all client connections coming from inside the Azure network, and the Proxy connection policy for all client connections coming from outside the Azure network.

To change the connection policy using Azure CLI, you can use the following script:

# Get SQL Server ID
sqlserverid = $(az sql server show -n azuredemosqldemo -g myazurelabsql --query 'id' -o tsv)

# Set URI
ids = "$sqlserverid/connectionPolicies/Default"

# Get current connection policy
az resource show --ids $ids

Once you have retrieved the current connection policy, you can update it to either Redirect or Proxy using the following script:

az resource update --ids $ids --set properties.connectionType=Redirect

Configuring Minimum TLS Version

You can configure the minimum Transport Layer Security (TLS) version for Azure SQL Database. 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 using Azure CLI.

To check the minimal TLS version for the SQL database using Azure CLI, you can run the following script:

az sql server show -n azuredemosqldemo -g myazurelabsql --query "minimalTlsVersion"

To update the minimum TLS version using Azure CLI, you can use the following script:

az sql server update -n azuredemosqldemo -g myazurelabsql --set minimalTlsVersion="1.2"

Azure SQL Connectivity Checker

You can use the PowerShell script provided by Microsoft to test the connectivity from your local machine to Azure SQL Database. The script supports Azure SQL DB, Managed Instance, and Azure Synapse.

To perform the connectivity test, you need to modify the script with the Azure server name, database name, and user credentials. The script will check the connection to the gateway, perform a redirect policy check on different ports, and provide recommendations in case of any connection failures.

Conclusion

In this article, we explored the Azure SQL Database connectivity architecture and discussed various concepts related to configuring IP firewall rules for Azure databases. We learned about connection policies, minimum TLS version, and access to public network configurations. It is recommended to use the Redirect connection policy for better performance.