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

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 Unlock access to the largest independent learning library in Tech for FREE! Get unlimited access to 7500+ expert-authored eBooks and video courses covering every tech area you can think of. Renews at €18.99/month. Cancel anytime
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.