Saturday, February 1, 2020

Firewall Ports for SQL Server Always On - Disaster Recovery Multi subnet setup



Background

I had a recent requirement to setup a SQL Server Always On cluster in a multi subnet scenario for
disaster recovery. The setup was on servers not under my control and firewalls between them. Also
from experience any service request to open ports takes too long (leading to delays in my work :()
so no real way of trial and error for the setup.
I felt let down by the microsoft documentation for the actual setup. So I "found" the required
ports to connect my two servers. So in my home lab I setup a multi subnet network to track
the ports required.


TL;DR summary :)

I used windows firewall and its rules order (see blog for details) meant I had a rule to block the following
ports. And kept TCP and UDP the same to keep it straightforward.

Blocked Ports - both TCP and UDP
0-134, 136,140-444,446-1432,1434-3342,3344-3388, 3390-4999, 6001-65535

Which relates to the
Allowed Ports - both TCP and UDP
135, 137-139, 445, 1433, 3343, 3389, 5000-6000

Warnings/Notes

*RDP Port 3389 only used for testing as covered by the rest of this blog
*For Azure I will investigate dynamic ports 5000-6000 and requirements for work as these ports may
change for my work requirements - on my homelab these ports are fine
*Also for my request was in Azure I had to add the Azure load balancing ports to the final work
related setup

Details on how I arrived at the above ports

Home Landscape Setup

Two Servers running Windows 2016

  1. SQLA   10.10.10.35
  2. SQLB   10.10.20.35


Port range for these servers TCP/UDP

  • netsh int ipv4 show dynamicport tcp
  • netsh int ipv4 show dynamicport udp
C:\Users\robert>netsh int ipv4 show dynamicport tcp
Protocol tcp Dynamic Port Range
---------------------------------
Start Port      : 49152
Number of Ports : 16384
C:\Users\robert>netsh int ipv4 show dynamicport udp
Protocol udp Dynamic Port Range
---------------------------------
Start Port      : 49152
Number of Ports : 16384
C:\Users\robert>


Which means a port range 0-65535 which would form the basis of the firewall rules.

1) Enable Firewall

Usually on my home lab I disable the firewalls but I needed to replicate a firewall and my intention was to just setup the rules between the two database servers



I started out blocking all ports and then adding a rule to allow ports - but the BLOCK is always first.
So only block specific ports was the way to go.

My trial and error approach would be use BLOCK but narrow the ports that are blocked.


Microsoft Document
“Firewall rules are applied with the following precedence:
  • Allow this firewall rule to override block rules
  • Block connection
  • Allow connection
  • Default profile behavior (allow connection or block connection, as specified on the Profile tab of the Windows Firewall with Advanced Security Properties dialog)”


2) Install network related test tools



a) Telnet
dism /online /Enable-Feature /FeatureName:TelnetClient




b) PortQry


c) WireShark





Installed with all default options, I didn’t select extra or deselect any options
It also installs Npcap - with default options


CheckPoint/Snapshot both servers

3) Setup Firewall to block SQLA & SQLB from connecting


Working on server SQLB -
For testing a connection SQLA with RDP port 3389  (this is default enabled by windows firewall)
Port 3389 listening


Working on Server SQLA

Note SQLB   10.10.20.35 for this rule


On SQLA - setup NEW rule for TCP connections to block
Only for SQLB


New Firewall Created


REPEAT Rules but choose UDP protocol


Both UDP and TCP ports blocked


REPEAT PORTQRY test on SQLB




3389 is now filitered / blocked by the firewall


NOW allow 3389 by adpating the blocking rules.




0-3388,3390-65535
Repeat for UDP




Test with PORTQRY on SQLB again


3389 is again listening.


ALIGN SQLB firewall ports - but block SQLA ;()
Noting SQLA IP address 10.10.10.35 for this rule






Install Failover cluster on both servers 


Powershell Admin account


Test cluster 
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
test-cluster : Unable to connect to sqlb via WMI.  This may be due to networking issues or firewall configuration on
sqlb.
    The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)
At line:1 char:1
+ test-cluster sqla,sqlb
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Test-Cluster], ClusterCmdletException
    + FullyQualifiedErrorId : Test-Cluster,Microsoft.FailoverClusters.PowerShell.TestClusterCommand


Failed with RPC error and indicating firewall issues ;) well lets see.


Googling this RPC issue found the following but server 2016 was not listed in the “applies to” section




Above link has no mention of version and indicates the registry fix is appropriate for 2016


Regedit on BOTH sqla and SQLb


Name = Ports
Add another REG_SZ and name as per the document


Now set the values
5000-6000


I ignored this message for ports
As the value was set


REBOOT BOTH SERVERS


Now we have defined ports time to adapt FIREWALL rules
We need to allow the following for RPC - default port 135 and the dynamic range
135
5000-6000


So change from
0-3388, 3390-65535
To
0-134,136-3388,3390-4999,6001-65535



Both UDP and TCP


& BOTH SERVERS




Test cluster 
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb
test-cluster : Failed to access remote registry on 'sqlb.rjruss.org'.  Ensure that the remote registry service is
running, and have remote administration enabled.
    The network path was not found.
At line:1 char:1
+ test-cluster sqla,sqlb
+ ~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Test-Cluster], ClusterCmdletException
    + FullyQualifiedErrorId : Test-Cluster,Microsoft.FailoverClusters.PowerShell.TestClusterCommand


Still Fails :(

SQLA Open Fireshark 

Start scan on Ethernet port (active adapter)
Filter for  sqlb IP address
ip.dst==10.10.20.35


Add dest port column via edit->preferences->Columns
Start the test-cluster command again - look for failures


Seems to fail on 445  & 137
NetBIOS name service: port 137 TCP, UDP
SMB over IP (Microsoft-DS): port 445 TCP, UDP


Also DOH I realised I would need the windows Cluster and also add SQL server ports
Review of ports now need to add


TCP
1433,  3343, 139, 445 
UDP
3343, 137, 138 


To keep it simple adding 137 & 138 as TCP ports as well.


So new ports for rules for the block rule adapted to allow these required ports


0-134, 136,140-444,446-1432,1434-3342,3344-3388, 3390-4999, 6001-65535




ADDED ALLOW RULES FOR BOTH
135, 137-139, 445, 1433, 3343, 3389, 5000-6000


PS C:\Windows\system32> import-module failoverclusters
PS C:\Windows\system32> test-cluster sqla,sqlb


Starts to work



“ClusterConditionallyApproved” is good to proceed
Warning - is acceptable as my setup only has one network and the servers are on different subnets


I decided on the following for my Always On setup

SQLA
10.10.10.35
SQLB
10.10.20.35
cluster name sqlcluster
10.10.10.86
10.10.20.86
Listener name sqlagl
10.10.10.87
10.10.20.87


Create the Cluster


New-Cluster -Name sqlcluster -Node sqla,sqlb -NoStorage  -StaticAddress 10.10.10.86,10.10.20.86


PS C:\Windows\system32> New-Cluster -Name sqlcluster -Node sqla,sqlb -NoStorage  -StaticAddress 10.10.10.86,10.10.20.86
WARNING: There were issues while creating the clustered role that may prevent it from starting. For more information
view the report file below.
WARNING: Report file location: C:\Windows\cluster\Reports\Create Cluster Wizard sqlcluster on 2020.02.01 At
13.27.17.htm


Name
----
Sqlcluster


Warning only.


My final destination was Azure and my setup does not  need a cluster disk - as I will switch off the quorum - see later blog for Always On setup 


Check Cluster Manager


The Cluster Core Resource IP details had SQLA interface offline.


I rebooted SQLB to test
SQLA IP was now online


READY NOW for SQL ALWAYS ON setup

No comments:

Post a Comment

Google +