none
[Forum FAQ] Open Windows Firewall Ports for SQL Server PowerShell Script RRS feed

  • General discussion

  • In future versions of Windows, Microsoft might remove the Netsh functionality for Windows Firewall with Advanced Security.


    Microsoft recommends that you transition to Windows PowerShell if you currently use netsh to configure and manage Windows Firewall with Advanced Security.


    Quote from:
    Windows Firewall with Advanced Security Administration with Windows PowerShell


    In this script, we use the “New-NetFirewallRule" context instead of the "netsh firewall" context to control Windows Firewall behavior for SQL server.

    SQLPorts.ps1
    
    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

    Note

    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}"

    More information:

    Netsh Commands for Windows Firewall with Advanced Security

    http://technet.microsoft.com/en-us/library/cc771920(v=ws.10).aspx

    How to open the firewall port for SQL Server on Windows Server 2008

    http://support.microsoft.com/kb/968872/en-us


    Please click to vote if the post helps you. This can be beneficial to other community members reading the thread.

    Friday, July 11, 2014 1:49 PM