PowerShell Script for SQL Server Firewall Rules

Opening the right ports in Windows Firewall is essential for SQL Server and Analysis Services, but manually configuring them can be tedious and prone to errors. This PowerShell script—Add-SQLFirewallRules.ps1—automates the entire process, ensuring consistent, secure configurations with minimal effort.

In this post, we’ll discuss what’s new in this script and guide you step-by-step on how to use it.

Script Overview

Add-FirewallRule Function

• Parameters:
  • DisplayName: Descriptive name for the rule
  • LocalPort: The port number you want to open
  • Protocol: TCP or UDP (default: TCP)
  • Profile: Profiles to apply (Domain, Private, and/or Public)
  • RemoteAddress: IP or subnet to allow (default: Any)

The function checks for existing rules, creates new ones if they don’t exist, and gives you verbose output when used with -Verbose.

Remove-FirewallRule Function

• Parameters:
  • LocalPort: The port number you want to remove rules for
  • Protocol: TCP or UDP (default: TCP)

Removes both inbound and outbound firewall rules matching the specified port and protocol.

PowerShell Script: Add-SQLFirewallRules.ps1

Save the script to a file named Add-SQLFirewallRules.ps1 and run it from an elevated PowerShell session.

<#
.SYNOPSIS
Adds or removes firewall rules for SQL Server, Analysis Services, and miscellaneous ports, 
with modern best practices like duplicate-checking and configurable profiles.

.DESCRIPTION
The script creates or removes inbound and outbound Windows Firewall rules for SQL Server, 
Analysis Services, and other common ports. It avoids duplicate rules, 
allows specifying different firewall profiles, and supports optional remote address restrictions.

.PARAMETER DisplayName
A string that represents the human-readable description for the firewall rule.

.PARAMETER LocalPort
The local port number for the firewall rule.

.PARAMETER Protocol
The network protocol for the firewall rule (TCP or UDP). Default is TCP.

.PARAMETER Profile
Which firewall profiles to apply (Domain, Private, Public). Default is all three.

.PARAMETER RemoteAddress
Which remote addresses to allow. Default is "*" (any).

.EXAMPLE
PS> .\Add-SQLFirewallRules.ps1
Creates or updates firewall rules for the specified SQL and miscellaneous ports.

#>

#region Add-FirewallRule
function Add-FirewallRule {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [string]$DisplayName,

        [Parameter(Mandatory = $true)]
        [int]$LocalPort,

        [Parameter(Mandatory = $false)]
        [ValidateSet("TCP","UDP")]
        [string]$Protocol = "TCP",

        [Parameter(Mandatory = $false)]
        [string[]]$Profile = @("Domain","Private","Public"),

        [Parameter(Mandatory = $false)]
        [string]$RemoteAddress = "*"
    )

    process {
        # Define rule names
        $inboundName  = "Allow inbound $Protocol Port $LocalPort"
        $outboundName = "Allow outbound $Protocol Port $LocalPort"

        # Check for existing rules
        $existingInbound  = Get-NetFirewallRule -DisplayName $inboundName -ErrorAction SilentlyContinue
        $existingOutbound = Get-NetFirewallRule -DisplayName $outboundName -ErrorAction SilentlyContinue

        # Inbound rule creation
        if (!$existingInbound) {
            try {
                New-NetFirewallRule `
                    -DisplayName $inboundName `
                    -Direction Inbound `
                    -LocalPort $LocalPort `
                    -Protocol $Protocol `
                    -Action Allow `
                    -Profile $Profile `
                    -RemoteAddress $RemoteAddress
                Write-Verbose "Created inbound rule for $DisplayName on port $LocalPort"
            }
            catch {
                Write-Error "Failed to create inbound rule for $DisplayName on port $LocalPort. Error: $_"
            }
        }
        else {
            Write-Verbose "Inbound rule for $DisplayName on port $LocalPort already exists. Skipping..."
        }

        # Outbound rule creation
        if (!$existingOutbound) {
            try {
                New-NetFirewallRule `
                    -DisplayName $outboundName `
                    -Direction Outbound `
                    -LocalPort $LocalPort `
                    -Protocol $Protocol `
                    -Action Allow `
                    -Profile $Profile `
                    -RemoteAddress $RemoteAddress
                Write-Verbose "Created outbound rule for $DisplayName on port $LocalPort"
            }
            catch {
                Write-Error "Failed to create outbound rule for $DisplayName on port $LocalPort. Error: $_"
            }
        }
        else {
            Write-Verbose "Outbound rule for $DisplayName on port $LocalPort already exists. Skipping..."
        }
    }
}
#endregion Add-FirewallRule

#region Remove-FirewallRule
function Remove-FirewallRule {
    [CmdletBinding()]
    param(
        [Parameter(Mandatory = $true)]
        [int]$LocalPort,

        [Parameter(Mandatory = $false)]
        [ValidateSet("TCP","UDP")]
        [string]$Protocol = "TCP"
    )

    process {
        $inboundName  = "Allow inbound $Protocol Port $LocalPort"
        $outboundName = "Allow outbound $Protocol Port $LocalPort"

        try {
            Remove-NetFirewallRule -DisplayName $inboundName -ErrorAction SilentlyContinue
            Remove-NetFirewallRule -DisplayName $outboundName -ErrorAction SilentlyContinue
            Write-Verbose "Removed firewall rules for port $LocalPort ($Protocol)"
        }
        catch {
            Write-Error "Failed to remove firewall rules for port $LocalPort ($Protocol). Error: $_"
        }
    }
}
#endregion Remove-FirewallRule

Write-Host "======== SQL Server Ports ==================="
$SQLPorts = @{
    "SQL Server Default Instance" = 1433
    "Dedicated Admin Connection"  = 1434
    "SQL Service Broker"          = 4022
    "T-SQL Debugger/RPC"          = 135
}

foreach ($entry in $SQLPorts.GetEnumerator()) {
    Add-FirewallRule -DisplayName $entry.Name -LocalPort $entry.Value -Verbose
}

Write-Host "======== Analysis Services Ports =============="
$AnalysisServicesPorts = @{
    "SSAS Default Instance"       = 2383
    "SQL Server Browser Service"  = 2382
}

foreach ($entry in $AnalysisServicesPorts.GetEnumerator()) {
    Add-FirewallRule -DisplayName $entry.Name -LocalPort $entry.Value -Verbose
}

Write-Host "======== Misc Applications ===================="
$MiscPorts = @{
    "HTTP" = 80
    "SSL"  = 443
    "SQL Server Browser Service's 'Browse" = @{
        Port     = 1434
        Protocol = "UDP"
    }
}

foreach ($entry in $MiscPorts.GetEnumerator()) {
    if ($entry.Value -is [hashtable]) {
        Add-FirewallRule -DisplayName $entry.Name -LocalPort $entry.Value.Port -Protocol $entry.Value.Protocol -Verbose
    }
    else {
        Add-FirewallRule -DisplayName $entry.Name -LocalPort $entry.Value -Verbose
    }
}

How to Use the Script

1. Download or Copy the Script
   Save it as Add-SQLFirewallRules.ps1 in a folder of your choice.
2. Run PowerShell as Administrator
   Right-click PowerShell and choose “Run as administrator,” otherwise you won’t have the permissions needed to create or remove firewall rules.
3. Execute the Script
   .\Add-SQLFirewallRules.ps1 -Verbose
   • The script will create the rules for SQL Server, Analysis Services, and common web ports (80/443).
   • Use -Verbose for detailed logging of which rules are created or skipped.
4. Verification
   • Check via PowerShell:
     Get-NetFirewallRule | Where-Object DisplayName -like "*Allow*Port*"
   • Check via Windows Defender Firewall:
     Go to “Windows Defender Firewall with Advanced Security” to see newly created rules.
5. Removing Rules
   If you ever need to remove the rules for a specific port, say TCP 1433:
   Remove-FirewallRule -LocalPort 1433 -Protocol TCP -Verbose

Best Practices

1. Open Only Essential Ports
   Remove or comment out any ports you don’t need, minimizing your attack surface.
2. Use Remote Address Restrictions
   If only specific IP ranges need access, set -RemoteAddress to a subnet (e.g., 10.0.0.0/24).
3. Version Control
   Keep your script in a Git repository or similar, making it easy to track changes or revert if needed.
4. Test in a Safe Environment
   If possible, test the script on a staging or development server before production.

The Add-SQLFirewallRules.ps1 script offers a clean, modern approach to configuring Windows Firewall for SQL Server, Analysis Services, and other common services. With the improvements outlined—duplicate checks, configurable profiles, remote address filtering, and built-in error handling—it’s a quick and reliable way to standardize your firewall configurations across multiple servers.

Have questions, or want to share your own enhancements? Leave a comment below!