Secure Connection to Azure SQL Database

As a database administrator, I started my journey in deploying managed SQL databases on the cloud, and I highly recommend deploying Azure SQL.

Azure SQL belongs to the family of managed resources on the Azure cloud platform that runs the very familiar SQL Server engine. Azure SQL Data Warehouse offers a wide range of solutions with multi-functional tools that can be of great use for any DBA. For most of these database resources, the Microsoft Azure platform takes care of the management, scalability, reliability, and performance recommendations. It also provides the underlying infrastructure to run the SQL Server database engine. Thus, the database administrator can focus on other aspects of work with SQL Server on cloud.

CodingSight - How to Connect Azure  SQL Database Securely

For most MS SQL database administrators, managing Azure SQL will be easy. The Azure SQL DB engine runs on virtual machines. You can apply your usual database administration and management skills to it. If you want to brush up on your cloud skills as a database administrator, you can refer to the following guideline for more information and teaching videos.

Secure Connection to Azure SQL Database with Azure Private Link

When planning to deploy your Azure SQL server, you have to consider some essential aspects, so that you can connect to Azure SQL database securely. Most use cases relate to connectivity from a virtual machine that is running your client application and is deployed within the Azure virtual network to Azure SQL Managed Instance.

Check the below solution diagram below for guidance:

Deploying Azure SQL Server Solution Diagram

When you deploy an Azure SQL Server, you will note that the resource comes with a public IP assigned by Azure. Also, it will be assigned a DNS similar to the naming convention mydatabasename.database.windows.net. If you perform the nslookup to this DNS name, depending on the Azure region you chose, the result will return a public IP that is taken from the Azure public IP ranges database. The list of Azure public IP ranges is here – it is updated regularly with every new region announced by Microsoft.

According to our deployment diagram above, if you want your virtual machines to run your client applications to talk to your Azure SQL, you should assign an Azure private endpoint and attach it to the Azure SQL Server. This private link will allow your client application to connect with your Azure SQL database via a private IP. This reduces latency and improves throughput. Once the Azure private endpoint is created, it will get a private IP taken from the Azure virtual network.

Establishing a Connection with Azure SQL via a Private IP

However, you need to match additional requirements to allow network traffic. If the client is the Azure Virtual Machine, you can accomplish the task using Network Security Groups (NSG) with service tags

Just select SQL from the Service Tags dropdown list when adding a new inbound or outbound rule:

Adding Inbound Security Rule

If the client that has to establish a connection is located on the workstation on-premises, you may need to consult your network admin to allow network traffic through the corporate firewall.

How to Fix the “Network-related or Instance-specific Error”

One of the most common connectivity errors that take place when you are trying to establish a connection with your Azure SQL Server is as follows:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: TCP Provider, error: 0 – A connection attempt failed because the connected party did not properly respond after some time, or established connection failed because the connected host has failed to respond.) (Microsoft SQL Server, Error: 10060)

This very long error message means that your Azure SQL Server is not accepting connections from the virtual machine inside the Azure virtual network because it is blocked or missing a configuration. Also, the reason might be the absence of an open port outbound rule in the network security group of your virtual machine.

For some database administrators who are new to Azure SQL, this can be daunting. However, let me share with you one thing that will help you solve this problem.

Adding Outbound Port Rule

It is usually found on missing outbound ports from the network security group attached to your virtual machine. Thus, in the Azure portal, go back to your virtual machine and the network security group, and revise the outbound rules:

Outbound Rules in the Azure Portal

If it is not in your outbound rules, you need to add an allow rule. It must be in the outbound communication from the client (virtual machine) to all Azure SQL Server addresses in the region on ports in the range of 110000 – 11999.

Adding Allow Rule to the Azure Portal

Create a new allow rule under the Outbound port rules tab:

  • Both the source and destination should have Any value selected from the dropdown list.
  • In the Destination port ranges, enter the value 1433 and 11000-11999. You can have a single outbound rule with both the port entries separated by a comma in the format like 1433, 11000-11999.
  • In the Protocol field, select Any.
  • The Priority value will depend on the number of outbound rules existing in your network security group.
  • Click Save.
Adding Outbound Security Rule

With this new outbound rule, we allow our virtual machines to connect to the Azure SQL Server. Add this rule to other network security groups that are attached to your virtual machines.

Testing Connectivity to Azure SQL Server

One more case is testing connectivity to the Azure SQL Server from the local workstation or a client which is not part of your secure corporate network. Here, you need to whitelist the static IP address of your client or your laptop.

You can do this by checking the networking configuration of the Azure SQL Server as shown in the screenshot below.

Checking the Networking Configuration of the Azure SQL Server

This should fix the connectivity issues.

Options Available to Connect to Azure SQL Database

There are 3 options:

  1. Redirect. It is the recommended practice, and I have described it above. This connectivity setup reduces latency as we are establishing connectivity from the client or virtual machine directly to the Azure SQL.
  2. Proxy. You are trying to establish connectivity to Azure SQL from outside the Azure virtual network, say your laptop, then connect to your corporate network, and then – to Azure SQL. In this case, latency increases, as you need to pass through the Azure gateways to connect successfully to the Azure SQL.
  3. Default. This configuration is in place after you have deployed Azure SQL. Afterward, depending on your use case, you configure connectivity either with a redirect or proxy setting.

Conclusion

Thus, we have examined different connectivity options and understood their impacts on Azure SQL deployment and client configuration from the Azure virtual network or outside the corporate network.

Hopefully, this article will help you save your time and free from headaches of the connectivity issues troubleshooting.

Read More

How SQL Server 2016 Took Azure SQL Data Warehouse to Next Level
Migrating SQL Server Database to Azure SQL (PaaS)

Carla Abanes

Carla Abanes

Carla is a certified MS SQL database administrator that also doubles as cloud architect. She is experienced managing both AWS and Azure platforms. In her free time she loves to sit down with a good book and coffee.

Leave a Reply

Your email address will not be published. Required fields are marked *