See this section to configure and validate a SQL Server Named Instance on an Azure SQL Virtual Machine (VM). It includes details for service validation, port configuration, firewall rules, and debugging steps.
Step 1: Check and validate the required Services
Ensure the SQL Server (Named Instance), SQL Server Agent (Named Instance), and SQL Browser services are running by performing the following steps:
Open Run (Win + R), type services.msc, and press Enter.
Locate the following services:
SQL Server (INSTANCE_NAME)
SQL Server Agent (INSTANCE_NAME)
SQL Server Browser
If any service is not running:
Right-click the service and then click Properties.
Set Startup type to Automatic.
Click Start > OK.
Step 2: Assign and open Port for the Named Instance
Step 1: Enable TCP/IP in SQL Server Configuration Manager
Open SQL Server Configuration Manager (SQLServerManager16.msc for SQL Server 2019).
Navigate to SQL Server Network Configuration → Protocols for [Named Instance]
Right-click TCP/IP and select Enable.
Step 2: Assign a Static Port
In SQL Server Configuration Manager, go to Protocols for [Named Instance].
Right-click TCP/IP and Select Properties.
Under the IP Addresses tab:
Scroll to the IPAll section.
In the TCP Port field, enter a port number (e.g. 1499).
Click Apply and then click OK.
Step 3: Restart SQL Server Service
After updating the port:
Open Run (Win + R), type services.msc and Press Enter.
Locate SQL Server (INSTANCE_NAME).
Right click > Restart.
Step 3: Allow Firewall Access for SQL Server
Run the following PowerShell commands to open the required ports:
# Allow SQL Server Named Instance Port (e.g., 1499)New-NetFirewallRule-DisplayName"Allow SQL Server 1499"-Direction Inbound-Protocol TCP-LocalPort 1499-Action Allow# Allow SQL Browser (for Named Instances)New-NetFirewallRule-DisplayName"Allow SQL Browser"-Direction Inbound-Protocol UDP-LocalPort 1434-Action Allow
Debugging Connection Issues
Step 1: Create a Test VM in the Same Subnet
Deploy a new VM in the same subnet as a SQL Server VM.
Connect to the VM and open Command Prompt or PowerShell.
Step 2: Test SQL Server Connectivity
Run the following command to check if the named instance is accessible:
sqlcmd -S"<SQLVMNAME>\<NAMED_INSTANCE>"-U<SQL_AUTH_USER>-P<SQL_AUTH_PASSWORD>-Q"SELECT name FROM sys.databases"
Example:
sqlcmd -S"TestAzureSQLOnA\ABC"-U abc -P druva@123 -Q"SELECT name FROM sys.databases"name --------------------------------------------------------------------------------------------------------------------------------master tempdb model
On success, this returns a list of databases available in the Named Instance.
Step 3: Troubleshooting Connection Failures
If the command fails:
Verify SQL Services: Ensure SQL Server and SQL Browser services are running.
Check TCP/IP Settings: Ensure TCP/IP is enabled and the correct port is assigned.
Confirm Firewall Rules: Make sure the firewall allows inbound traffic on SQL Server Port (eg. 1499) and SQL Browser Port (1434).
Validate Network Connectivity: Ensure the test VM is in the same subnet as the SQL VM.