Configuring SQL Server Ports for SharePoint: Firewall Rules and Best Practices

Introduction

Microsoft SharePoint, whether deployed as a farm on-premises or in a hybrid setup, relies heavily on Microsoft SQL Server for its data storage and retrieval operations. SQL Server hosts a range of databases—configuration, content, service application, and search-related—which SharePoint constantly accesses to function correctly.

One critical aspect of a successful SharePoint deployment is ensuring that network communication between the SharePoint servers and the SQL Server is not blocked by firewalls. This article provides a comprehensive guide to configuring the necessary firewall rules for SQL Server, with a specific focus on SharePoint environments, including static vs. dynamic ports, PowerShell automation, and security considerations.

Default Ports Used by SQL Server

SQL Server uses different ports depending on how the instance is configured:

Note: It is best practice to assign static ports to SQL Server instances when used with enterprise systems like SharePoint to simplify firewall rule management.

Setting Static Ports for SQL Server Instances

To avoid dynamic port issues and simplify firewall configuration, configure SQL Server to use static ports:

1. Open SQL Server Configuration Manager.
2. Navigate to SQL Server Network Configuration > Protocols for [Instance Name].
3. Double-click TCP/IP > Go to the IP Addresses tab.
4. In the section for each IP (especially IPAll):
   • Remove any values from TCP Dynamic Ports.
   • Set TCP Port to a static number (e.g., 14333).
5. Restart the SQL Server service.

Now you can configure the firewall to allow only this port.

Creating Windows Firewall Rules (Manually)

On the SQL Server host machine:

1. Open Windows Defender Firewall with Advanced Security.
2. Click Inbound Rules > New Rule.
3. Choose Port, then select TCP.
4. Enter your port number (e.g., 1433 or 14333).
5. Select Allow the connection.
6. Apply to Domain, Private, and Public, as appropriate.
7. Name the rule (e.g., SQL Server Port for SharePoint).

Repeat this for any other necessary ports like 1434 (UDP) if using named instances.

PowerShell Script to Configure SQL Server Firewall Ports

The following PowerShell script automates the creation of inbound firewall rules for SQL Server:

# Define the ports you want to open
$Ports = @(
    @{ Name = "SQL Default Instance (TCP 1433)"; Port = 1433; Protocol = "TCP" },
    @{ Name = "SQL Browser (UDP 1434)"; Port = 1434; Protocol = "UDP" },
    @{ Name = "SQL Analysis Services (TCP 2383)"; Port = 2383; Protocol = "TCP" },
    @{ Name = "SQL Service Broker (TCP 4022)"; Port = 4022; Protocol = "TCP" }
)

foreach ($port in $Ports) {
    New-NetFirewallRule `
        -DisplayName $port.Name `
        -Direction Inbound `
        -Protocol $port.Protocol `
        -LocalPort $port.Port `
        -Action Allow `
        -Profile Domain,Private `
        -Enabled True
}

Run this script as Administrator on your SQL Server machine.

Network-Level Firewall and DMZ Considerations

In larger environments with segmented networks or demilitarized zones (DMZs), firewall rules should be configured on hardware firewalls and not just Windows Firewall. In such cases:

• Whitelist only SharePoint front-end and application servers as allowed IP sources.
• Use least privilege by restricting unnecessary ports.
• Monitor traffic between tiers using network flow logs or intrusion detection systems.

Security Best Practices

• Never expose SQL Server ports to the internet.
• Use encryption (SSL/TLS) for SQL Server connections (via Force Encryption or certificates).
• Enable Windows Authentication and avoid mixed-mode when possible.
• Isolate SQL Server via VLANs or subnetting where feasible.
• Use IPSec or firewall ACLs to enforce transport security between SharePoint and SQL.

Summary Table: SQL Server Ports for SharePoint Firewall Configuration

References and Further Reading

• SQL Server on Wikipedia
• SharePoint on Wikipedia
• Microsoft Learn: Configure Windows Firewall for SQL Server Access
• Microsoft Learn: SQL Server Network Configuration