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.
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
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.
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
- Run nslookup command to capture Azure SQL DB public IP address. It shows the IP address 104.211.96.159
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
- 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/
- It assigned a dynamically identified port (in this example – 11012) and sent it to the client application.
- The application uses this port 11012 and connects to the target Azure SQL Database.
- 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
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.
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.
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.
|
1 2 3 4 5 6 7 8 9 10 |
# 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 |
The following script updates the connection policy to Redirect.
|
1 2 3 |
az resource update --ids $ids --set properties.connectionType=Redirect |
As shown below, the az resource show returns Redirect for my existing Azure database.
The following script updates the connection policy to Proxy.
|
1 2 3 |
az resource update --ids $ids --set properties.connectionType=Proxy |
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.
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.
You can run the following CLI script to check the minimal TLS version for the SQL database.
|
1 2 3 |
az sql server show -n azuredemosqldemo -g myazurelabsql --query "minimalTlsVersion" |
The az sql server update script updates the minimum TLS version. For example, the below script updates the TLS version to 1.2
|
1 2 3 |
az sql server update -n azuredemosqldemo -g myazurelabsql --set minimalTlsVersion="1.2" |
If we rerun the az sql server show, it returns the updated minimum TLS version.
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.
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.
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.
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.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023