Recently, I had a session on using SQL Server on Azure, and one of the questions that came up was how to access SQL Server in a Hybrid Setup. This is a common scenario where developers need to connect to a SQL Server running on a VM from their applications within their own environment. During the session, we encountered an error while trying to establish the connection, and after some debugging, we realized it was a firewall issue.
In this blog post, I will walk you through the steps to set up the firewall for Azure SQL Server VMs and enable SQL Server traffic.
Steps to Enable SQL Server Traffic
To enable connection to the SQL Server from on-premises applications, you need to open port 1433 on the SQL Server VM. Here are the steps to do so:
- Login to the SQL Server VM and open the firewall settings.
- Create an inbound rule for TCP port 1433 to allow connections.
- Follow the default values on the wizard for the next steps and name the rule ‘SQL TCP’.
- Click OK to save the rule.
Alternatively, you can use the following PowerShell cmdlet to configure the inbound firewall rule:
netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCPConfiguring the firewall is an important step when working with VMs on Azure to access SQL Server. While exploring the core capabilities of Azure, I have noticed that there are nuances that are quite different from working with an on-premise SQL Server. DBAs and administrators may find it challenging to understand these use-cases that need to be configured, especially if they are accustomed to connecting to SQL Server directly in their data centers.
However, configuring the firewall for SQL Server on Azure VMs is a common task, and it’s essential to understand the steps involved. By following the instructions provided in this blog post, you will be able to establish a successful connection between your on-premises applications and the SQL Server running on an Azure VM.
Thank you for reading!