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.
Related Posts:
Here is an Easy Fix for SQL Service Startup Issues… December 28, 2020
Connect To SQL Server - Back to Basics March 27, 2019
SQL Server Extended Availability Groups April 1, 2018
Single User Mode - Back to Basics May 31, 2018
Lost that SQL Server Access? May 30, 2018
The post Firewall Ports You Need to Open for Availability Groups appeared first on SQLServerCentral.
Read more