SQL Server Firewall Configuration

Ensure secure and reliable connectivity between Nodinite and SQL Server with proper firewall configuration. This comprehensive guide covers all required ports for on-premise SQL Server, Azure SQL Managed Instance, and Always On Availability Groups.

✅ Complete port requirements for Nodinite and SQL Server communication
✅ Separate guidance for on-premise and Azure SQL Managed Instance
✅ Always On Availability Groups (AOAG) firewall configuration
✅ Windows authentication, Kerberos, and MSDTC requirements
✅ Troubleshooting tips for common firewall issues

Overview

Nodinite components communicate with SQL Server databases for configuration, logging, and monitoring. Proper firewall configuration is essential for reliable operation across different deployment scenarios.

Key Communication Paths:

1. Nodinite Application Server → SQL Server — Core Services connect to Configuration and Log Databases
2. SQL Server → SQL Server — Linked Servers enable cross-database queries
3. Domain Controllers — Kerberos authentication and DNS resolution

Supported SQL Server Deployments:

• On-Premise SQL Server — Standalone instances, failover clusters, Always On Availability Groups
• Azure SQL Managed Instance — Fully managed PaaS with near 100% SQL Server compatibility
• Hybrid Scenarios — On-premise Nodinite connecting to Azure SQL Managed Instance via VPN/ExpressRoute

Tip

Quick Start: If you're installing Nodinite and SQL Server on the same Windows Server with no other servers involved, no firewall rules are required for local communication. You only need firewall rules when Nodinite and SQL Server are on different servers or different networks.

Communication Architecture

Nodinite uses a distributed architecture where multiple components communicate with SQL Server databases:

Diagram: Nodinite components communicate with SQL Server databases, with Kerberos authentication via Domain Controllers

On-Premise SQL Server

This section covers firewall requirements for on-premise SQL Server deployments, including standalone instances, failover clusters, and Always On Availability Groups.

Communication Overview

Three primary communication paths require firewall configuration:

1. Nodinite Application Server → SQL Server — All Nodinite Core Services and Web applications
2. SQL Server → SQL Server — Linked Servers (when Log Databases are on different instances)
3. All Servers → Domain Controller — Kerberos authentication and DNS resolution

Diagram: On-premise SQL Server firewall communication paths

1. Nodinite Application Server → SQL Server

Required for all Nodinite Core Services to connect to Configuration and Log Databases.

Note

Stateful Firewalls: Most modern firewalls automatically allow return traffic for established connections. Inbound rules are typically only needed if using non-stateful firewalls or specific security policies.

2. SQL Server → SQL Server (Linked Servers)

Required when Nodinite Configuration Database and Log Databases are hosted on different SQL Server instances.

Important

Linked Server Requirement: Nodinite relies on SQL Server Linked Servers for cross-database queries. Both SQL Server instances must be able to communicate bidirectionally. See Linked Servers for configuration details.

3. All Servers → Domain Controller

Required for Kerberos authentication, DNS resolution, and Active Directory group membership.

Note

DNS Resolution: All servers require DNS access for name resolution. This can optionally be solved using entries in the local hosts file on each server, but DNS is recommended for maintainability.

SQL Server Browser Service

The SQL Server Browser Service (UDP 1434) is required for connecting to named instances without specifying a port number.

When SQL Browser is Required:

• ✅ Connecting to named instances: Server=SQL01\INSTANCE;...
• ❌ Not required if specifying explicit port: Server=SQL01,1435;...
• ❌ Not required for default instances (always use port 1433)

Alternative to SQL Browser:

Instead of opening UDP 1434, you can specify the port explicitly in connection strings:

Server=SQL01\INSTANCE,1435;Initial Catalog=DatabaseName;...

Tip

Best Practice: Use explicit ports in production connection strings to avoid dependency on SQL Browser Service and improve connection reliability.

Always On Availability Groups (AOAG)

Additional firewall considerations for SQL Server Always On Availability Groups:

AOAG Firewall Checklist:

• ✅ Allow Nodinite → AG Listener on TCP 1433 (or custom listener port)
• ✅ Allow bidirectional communication between all replicas on TCP 5022 (database mirroring)
• ✅ Allow bidirectional communication between all replicas on TCP 1433 (instance port)
• ✅ Ensure DNS resolution for AG Listener name
• ✅ Configure multi-subnet failover in connection strings: MultiSubnetFailover=True

Tip

See the comprehensive Always On Availability Groups and SQL Server Connection Strings guides for detailed AOAG configuration and connection string examples.

Common On-Premise Firewall Scenarios

Scenario 1: Single Server (Simplest)

Setup: Nodinite and SQL Server on the same Windows Server

Firewall Rules: ✅ None required for local SQL Server communication

Scenario 2: Two Servers (Most Common)

Setup: Nodinite on Server A, SQL Server on Server B

Required Rules:

• ✅ Server A → Server B: TCP 1433 (SQL Server)
• ✅ Server A → Server B: TCP 135, TCP 49152–65535 (MSDTC)
• ✅ Server A → Domain Controller: TCP/UDP 53, 88, 389
• ✅ Server B → Domain Controller: TCP/UDP 53, 88, 389

Scenario 3: Multiple SQL Servers (Linked Servers)

Setup: Nodinite on Server A, Configuration Database on Server B, Log Databases on Server C

Additional Rules:

• ✅ Server B → Server C: TCP 1433 (Linked Server queries)
• ✅ Server B → Server C: TCP 135, TCP 49152–65535 (Linked Server RPC)

Scenario 4: AOAG Multi-Subnet

Setup: Nodinite on Server A, SQL AOAG with replicas on Server B (primary) and Server C (secondary)

Required Rules:

• ✅ Server A → AG Listener: TCP 1433
• ✅ Server B ↔ Server C: TCP 5022 (mirroring endpoint)
• ✅ Server B ↔ Server C: TCP 1433 (replica sync)

Restricting Dynamic RPC Port Range

The default Windows dynamic RPC port range (49152–65535) is large. You can restrict it to improve security:

PowerShell commands to restrict RPC range:

# Set RPC dynamic port range to 49152-49162 (10 ports)
netsh int ipv4 set dynamicport tcp start=49152 num=10
netsh int ipv4 set dynamicport udp start=49152 num=10

# Verify settings
netsh int ipv4 show dynamicport tcp
netsh int ipv4 show dynamicport udp

Restart required: Yes, restart the server for changes to take effect.

Warning

Caution: Restricting the RPC port range too aggressively can cause connection failures. Allocate at least 10 ports per concurrent MSDTC/RPC connection. Monitor for port exhaustion.

Azure SQL Managed Instance

Azure SQL Managed Instance requires different firewall configuration compared to on-premise SQL Server. Communication is encrypted over HTTPS (TCP 443) and requires public IP whitelisting or private endpoint configuration.

Communication Overview

Two primary communication paths:

1. Nodinite Application Server → Azure SQL Managed Instance — All Nodinite Core Services
2. Nodinite Application Server → Azure Infrastructure — DNS resolution

Diagram: Azure SQL Managed Instance firewall communication paths

1. Nodinite Application Server → Azure SQL Managed Instance

Required for all Nodinite Core Services to connect to Configuration and Log Databases hosted in Azure.

Important

Encryption Required: Azure SQL Managed Instance always enforces encrypted connections. You must use Encrypt=True in connection strings. See SQL Server Connection Strings - Azure SQL Managed Instance for examples.

2. Nodinite Application Server → Azure DNS

Required for resolving Azure SQL Managed Instance FQDN.

Azure SQL Managed Instance Firewall Configuration

Azure SQL Managed Instance has two firewall layers:

1. Azure SQL Firewall Rules

Configure in Azure Portal under Firewalls and virtual networks:

Option A: Public Endpoint (Internet Access)

Add your Nodinite server's public IP address to Azure SQL firewall rules:

1. Navigate to Azure Portal → SQL Managed Instance → Networking
2. Under Public endpoint, click Configure
3. Add firewall rule with your Nodinite server's public IP address range

Warning

Security Risk: Public endpoints expose SQL Server to the internet. Use strong SQL authentication passwords and consider IP whitelisting. Private endpoints are recommended for production.

Option B: Private Endpoint (VNet Integration) — Recommended

Use Azure Private Link for secure, private connectivity:

1. Create Private Endpoint for SQL Managed Instance
2. Ensure Nodinite server can reach the Private Endpoint:
   • Azure VM: Connect to same VNet or peered VNet
   • On-Premise: Connect via VPN Gateway or ExpressRoute

Benefits:

• ✅ No public internet exposure
• ✅ Traffic stays on Azure backbone network
• ✅ No public IP whitelisting required

2. Network Security Groups (NSG)

If Nodinite runs on an Azure VM, configure NSG rules:

Hybrid Scenarios (On-Premise to Azure)

Scenario: Nodinite on-premise, Azure SQL Managed Instance in Azure

Required Network Connectivity:

1. VPN Gateway or ExpressRoute connection between on-premise network and Azure VNet
2. Private Endpoint for SQL Managed Instance in Azure VNet
3. On-Premise Firewall Rules:
   • Allow outbound TCP 443, 1433 to Azure VNet IP range
   • Allow outbound TCP/UDP 53 for DNS resolution

Recommended Setup:

On-Premise Network → VPN/ExpressRoute → Azure VNet → Private Endpoint → SQL Managed Instance

Tip

DNS Configuration: Configure on-premise DNS to resolve Azure SQL Managed Instance private endpoint IP addresses. Use Azure Private DNS zones or conditional forwarding.

Azure SQL Managed Instance Checklist

• ✅ Add Nodinite server's public IP to Azure SQL firewall rules (if using public endpoint)
• ✅ OR configure Private Endpoint for secure connectivity (recommended)
• ✅ Ensure DNS resolution for *.database.windows.net
• ✅ Use Encrypt=True in all connection strings (enforced by Azure)
• ✅ Configure NSG rules if Nodinite runs on Azure VM
• ✅ For hybrid scenarios, establish VPN/ExpressRoute connectivity
• ✅ Test connectivity: Test-NetConnection -ComputerName instance.database.windows.net -Port 1433

Troubleshooting Firewall Issues

Common Symptoms and Solutions

Testing Connectivity

Test SQL Server Port (TCP 1433)

PowerShell:

Test-NetConnection -ComputerName SQL01 -Port 1433

Expected result: TcpTestSucceeded : True

Test SQL Browser (UDP 1434)

PowerShell:

Test-NetConnection -ComputerName SQL01 -Port 1434

Note

UDP testing with Test-NetConnection may show PingSucceeded but not TcpTestSucceeded since UDP doesn't establish connections.

Test MSDTC (TCP 135 + Dynamic)

PowerShell:

# Test RPC Endpoint Mapper
Test-NetConnection -ComputerName SQL01 -Port 135

# Test specific dynamic port (if known)
Test-NetConnection -ComputerName SQL01 -Port 49152

Test Azure SQL Managed Instance

PowerShell:

# Test HTTPS connectivity
Test-NetConnection -ComputerName myinstance.database.windows.net -Port 443

# Test TDS connectivity
Test-NetConnection -ComputerName myinstance.database.windows.net -Port 1433

Test DNS Resolution

PowerShell:

# On-Premise SQL Server
Resolve-DnsName SQL01

# Azure SQL Managed Instance
Resolve-DnsName myinstance.database.windows.net

Advanced Troubleshooting

Enable SQL Server Logging

Enable SQL Server error log to see connection attempts:

-- Enable login auditing (requires restart)
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', 
    N'Software\Microsoft\MSSQLServer\MSSQLServer',
    N'AuditLevel', REG_DWORD, 2

Log location: C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Log\ERRORLOG

Check Windows Firewall Rules

PowerShell (run as Administrator):

# List all SQL Server firewall rules
Get-NetFirewallRule | Where-Object {$_.DisplayName -like "*SQL*"}

# Check if port 1433 is allowed
Get-NetFirewallRule | Where-Object {$_.LocalPort -eq 1433}

# View detailed rule information
Get-NetFirewallRule -DisplayName "SQL Server" | Get-NetFirewallPortFilter

Monitor Network Traffic

Use Wireshark or NetMon to capture network traffic and verify packets are reaching SQL Server.

Filter for SQL Server traffic:

tcp.port == 1433

External References

• Configure the Windows Firewall to Allow SQL Server Access
• How to configure RPC dynamic port allocation with firewalls
• Azure SQL Managed Instance connectivity architecture
• SQL Server Always On Availability Groups firewall configuration
• MSDTC firewall configuration

Related Topics

• SQL Server Connection Strings
• Always On Availability Groups
• MSDTC Configuration
• SQL Server Options
• Linked Servers