Scripts to open Windows Firewall Ports for SQL Server

I was looking for a PowerShell replacement of the script provided in MS KB “How to open the firewall port for SQL Server on Windows Server 2008” which uses the netsh command to open the Windows firewall ports for SQL Server. Because in future versions of Windows, Microsoft might remove the Netsh functionality for Windows Firewall with Advanced Security. Microsoft also recommends that you transition to Windows PowerShell if you currently use netsh to configure and manage Windows Firewall with Advanced Security. And I found the answer it on the TechNet Forums. For reference I have posted the two scripts after the read more. First the good ol’ netsh version:

@echo ========= SQL Server Ports ===================
@echo Enabling SQLServer default instance port 1433
netsh firewall set portopening TCP 1433 "SQLServer"
@echo Enabling Dedicated Admin Connection port 1434
netsh firewall set portopening TCP 1434 "SQL Admin Connection"
@echo Enabling conventional SQL Server Service Broker port 4022
netsh firewall set portopening TCP 4022 "SQL Service Broker"
@echo Enabling Transact-SQL Debugger/RPC port 135
netsh firewall set portopening TCP 135 "SQL Debugger/RPC"
@echo ========= Analysis Services Ports ==============
@echo Enabling SSAS Default Instance port 2383
netsh firewall set portopening TCP 2383 "Analysis Services"
@echo Enabling SQL Server Browser Service port 2382
netsh firewall set portopening TCP 2382 "SQL Browser"
@echo ========= Misc Applications ==============
@echo Enabling HTTP port 80
netsh firewall set portopening TCP 80 "HTTP"
@echo Enabling SSL port 443
netsh firewall set portopening TCP 443 "SSL"
@echo Enabling port for SQL Server Browser Service's 'Browse' Button 
netsh firewall set portopening UDP 1434 "SQL Browser"
@echo Allowing multicast broadcast response on UDP (Browser Service Enumerations OK)
netsh firewall set multicastbroadcastresponse ENABLE

The PowerShell version of the script:

Write-host ========= SQL Server Ports ===================

Write-host Enabling SQLServer default instance port 1433

#netsh firewall set portopening TCP 1433 "SQLServer"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 1433" –Direction inbound –LocalPort 1433 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 1433" –Direction outbound –LocalPort 1433 -Protocol TCP -Action Allow

Write-host Enabling Dedicated Admin Connection port 1434

#netsh firewall set portopening TCP 1434 "SQL Admin Connection"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 1434" -Direction inbound –LocalPort 1434 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 1434" -Direction outbound –LocalPort 1434 -Protocol TCP -Action Allow

Write-host Enabling conventional SQL Server Service Broker port 4022

#netsh firewall set portopening TCP 4022 "SQL Service Broker"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 4022" -Direction inbound –LocalPort 4022 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 4022" -Direction outbound –LocalPort 4022 -Protocol TCP -Action Allow

Write-host Enabling Transact-SQL Debugger/RPC port 135

#netsh firewall set portopening TCP 135 "SQL Debugger/RPC"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 135" -Direction inbound –LocalPort 135 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 135" -Direction outbound –LocalPort 135 -Protocol TCP -Action Allow

Write-host ========= Analysis Services Ports ==============

Write-host Enabling SSAS Default Instance port 2383

#netsh firewall set portopening TCP 2383 "Analysis Services"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 2383" -Direction inbound –LocalPort 2383 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 2383" -Direction outbound –LocalPort 2383 -Protocol TCP -Action Allow

Write-host Enabling SQL Server Browser Service port 2382

#netsh firewall set portopening TCP 2382 "SQL Browser"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 2382" -Direction inbound –LocalPort 2382 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 2382" -Direction outbound –LocalPort 2382 -Protocol TCP -Action Allow

Write-host ========= Misc Applications ==============

Write-host Enabling HTTP port 80

#netsh firewall set portopening TCP 80 "HTTP"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 80" -Direction inbound –LocalPort 80 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 80" -Direction outbound –LocalPort 80 -Protocol TCP -Action Allow

Write-host Enabling SSL port 443

#netsh firewall set portopening TCP 443 "SSL"

New-NetFirewallRule -DisplayName "Allow inbound TCP Port 443" -Direction inbound –LocalPort 443 -Protocol TCP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound TCP Port 443" -Direction outbound –LocalPort 443 -Protocol TCP -Action Allow

Write-host Enabling port for SQL Server Browser Service's 'Browse

#netsh firewall set portopening UDP 1434 "SQL Browser"

New-NetFirewallRule -DisplayName "Allow inbound UDP Port 1434" -Direction inbound –LocalPort 1434 -Protocol UDP -Action Allow

New-NetFirewallRule -DisplayName "Allow outbound UDP Port 1434" -Direction outbound –LocalPort 1434 -Protocol UDP -Action Allow

The following command will check if the “Execution Policy” is set to unrestricted. If not, it will set it to unrestricted, then run the target PowerShell script. Finally, the command will set the Execution Policy back to the previous value.

PowerShell -noprofile -command "& {$variable = Get-ExecutionPolicy; If((Get-ExecutionPolicy) -ne 'Unrestricted'){Set-ExecutionPolicy unrestricted -force;}; .<YourScript>; Set-ExecutionPolicy $variable}"

Source.

Branko Vucinec

About Branko Vucinec

Hi! I'm Branko, a Systems Engineer focused on Microsoft technologies from the Netherlands. I enjoy helping organizations with the business and people opportunities and challenges surrounding tech.

Comments