Firewall Ports You Need to Open for Availability Groups from Blog Posts – SQLServerCentral

0
274
5 min read

Something that never ceases to amaze me is the frequent request for help on figuring out what ports are needed for Availability Groups in SQL Server to function properly. These requests come from a multitude of reasons such as a new AG implementation, to a migration of an existing AG to a different VLAN.

Whenever these requests come in, it is a good thing in my opinion. Why? Well, that tells me that the network team is trying to instantiate a more secure operating environment by having segregated VLANs and firewalls between the VLANs. This is always preferable to having firewall rules of ANY/ANY (I correlate that kind of firewall rule to granting “CONTROL” to the public server role in SQL Server).

So What Ports are Needed Anyway?

If you are of the mindset that a firewall rule of ANY/ANY is a good thing or if your Availability Group is entirely within the same VLAN, then you may not need to read any further. Unless, of course, if you have a software firewall (such as Windows Defender / Firewall) running on your servers. If you are in the category where you do need to figure out which ports are necessary, then this article will provide you with a very good starting point.


Windows Server Clustering 

TCP/UDP

Port

Description

TCP/UDP

53

User & Computer Authentication [DNS]

TCP/UDP

88

User & Computer Authentication [Kerberos]

UDP

123

Windows Time [NTP]

TCP

135

Cluster DCOM Traffic [RPC, EPM]

UDP

137

User & Computer Authentication [NetLogon, NetBIOS , Cluster Admin, Fileshare Witness]

UDP

138

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service, Fileshare Witness]

TCP

139

DSF, Group Policy [DFSN, NetLogon, NetBIOS Datagram Service, Fileshare Witness]

UDP

161

SNMP

TCP/UDP

162

SNMP Traps

TCP/UDP

389

User & Computer Authentication [LDAP]

TCP/UDP

445

User & Computer Authentication [SMB, SMB2, CIFS, Fileshare Witness]

TCP/UDP

464

User & Computer Authentication [Kerberos Change/Set Password]

TCP

636

User & Computer Authentication [LDAP SSL]

TCP

3268

Microsoft Global Catalog

TCP

3269

Microsoft Global Catalog [SSL]

TCP/UDP

3343

Cluster Network Communication

TCP

5985

WinRM 2.0 [Remote PowerShell]

TCP

5986

WinRM 2.0 HTTPS [Remote PowerShell SECURE]

TCP/UDP

49152-65535

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}RPC and DCOM ] *

SQL Server –

TCP/UDP

Port

Description

TCP

1433

SQL Server/Availability Group Listener [Default Port {CAN BE CHANGED}]

TCP/UDP

1434

SQL Server Browser

UDP

2382

SQL Server Analysis Services Browser

TCP

2383

SQL Server Analysis Services Listener

TCP

5022

SQL Server DBM/AG Endpoint [Default Port {CAN BE CHANGED}]

TCP/UDP

49152-65535

Dynamic TCP/UDP [Defined Company/Policy {CAN BE CHANGED}]

*Randomly allocated UDP port number between 49152 and 65535

So I have a List of Ports, what now?

Knowing is half the power, and with great knowledge comes great responsibility – or something like that. In reality, now that know what is needed, the next step is to go out and validate that the ports are open and working. One of the easier ways to do this is with PowerShell.
$RemoteServers = "Server1","Server2"
$InbndServer = "HomeServer"
$TCPPorts   =  "53",
            "88",
            "135",
            "139",
            "162",
            "389",
            "445",
            "464",
            "636",
            "3268",
            "3269",
            "3343",
            "5985",
            "5986",
            "49152",
            "65535",
            "1433",
            "1434",
            "2383",
            "5022"
          
$UDPPorts = "53",
            "88",
            "123",
            "137",
            "138",
            "161",
            "162",
            "389",
            "445",
            "464",
            "3343",
            "49152",
            "65535",
            "1434",
            "2382"
 
$TCPResults = @()
$TCPResults = Invoke-Command $RemoteServers {param($InbndServer,$TCPPorts)
                $Object = New-Object PSCustomObject
                $Object | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $env:COMPUTERNAME
                $Object | Add-Member -MemberType NoteProperty -Name "Destination" -Value $InbndServer
                    Foreach ($P in $TCPPorts){
                        $PortCheck = (TNC -Port $p -ComputerName $InbndServer ).TcpTestSucceeded
                        If($PortCheck -notmatch "True|False"){$PortCheck = "ERROR"}
                        $Object | Add-Member Noteproperty "$("Port " + "$p")" -Value "$($PortCheck)"
                    }
                $Object
           } -ArgumentList $InbndServer,$TCPPorts | select * -ExcludeProperty runspaceid, pscomputername
 
$TCPResults | Out-GridView -Title "AG and WFC TCP Port Test Results"
 
$TCPResults | Format-Table * #-AutoSize
$UDPResults = Invoke-Command $RemoteServers {param($InbndServer,$UDPPorts)
                $test = New-Object System.Net.Sockets.UdpClient;
                $Object = New-Object PSCustomObject
                $Object | Add-Member -MemberType NoteProperty -Name "ServerName" -Value $env:COMPUTERNAME
                $Object | Add-Member -MemberType NoteProperty -Name "Destination" -Value $InbndServer
                    Foreach ($P in $UDPPorts){
                        Try
                        {
                            $test.Connect($InbndServer, $P);
                            $PortCheck = "TRUE";
                            $Object | Add-Member Noteproperty "$("Port " + "$p")" -Value "$($PortCheck)"
                        }
                        Catch
                        {
                            $PortCheck = "ERROR";
                            $Object | Add-Member Noteproperty "$("Port " + "$p")" -Value "$($PortCheck)"
                        }
                    }
                $Object
            } -ArgumentList $InbndServer,$UDPPorts | select * -ExcludeProperty runspaceid, pscomputername
 
$UDPResults | Out-GridView -Title "AG and WFC UDP Port Test Results"
 
$UDPResults | Format-Table * #-AutoSize

This script will test all of the related TCP and UDP ports that are required to ensure your Windows Failover Cluster and SQL Server Availability Group works flawlessly. If you execute the script, you will see results similar to the following.

Data Driven Results

In the preceding image, I have combined each of the Gridview output windows into a single screenshot. Highlighted in Red is the result set for the TCP tests, and in Blue is the window for the test results for the UDP ports.

With this script, I can take definitive results all in one screen shot and share them with the network admin to try and resolve any port deficiencies. This is just a small data driven tool that can help ensure quicker resolution when trying to ensure the appropriate ports are open between servers. A quicker resolution in opening the appropriate ports means a quicker resolution to the project and all that much quicker you can move on to other tasks to show more value!

Put a bow on it

This article has demonstrated a meaningful and efficient method to (along with the valuable documentation) test and validate the necessary firewall ports for Availability Groups (AG) and Windows Failover Clustering. With the script provided in this article, you can provide quick and value added service to your project along with providing valuable documentation of what is truly needed to ensure proper AG functionality.

Interested in learning about some additional deep technical information? Check out these articles!

Here is a blast from the past that is interesting and somewhat related to SQL Server ports. Check it out here.

This is the sixth article in the 2020 “12 Days of Christmas” series. For the full list of articles, please visit this page.

The post Firewall Ports You Need to Open for Availability Groups first appeared on SQL RNNR.

The post Firewall Ports You Need to Open for Availability Groups appeared first on SQLServerCentral.