Survey report on 445 ports used in SQL Server AlwaysOn

SQL Server AlwaysOn Zhong 445 Survey report on port usage

Part I: official documentation from Microsoft

Microsoft Knowledge Base article 832017:service overview and network port requirements for Windows

For our production environment SQL Server AlwaysOn FCI & AG Hybrid deployment, based on Active Directory and cluster Service.

The following information is referenced from KB 832017 and describes the related port requirements based on these two services:

1. Active Directory (Local Security Authority)

Active Directory runs under the Lsass.exe process and includes the authentication and replication engines for Windows DOMA in controllers. Domain controllers, client computers and application servers require network connectivity to Active Directory over Specifi C hard-coded ports. Additionally, unless a tunneling protocol is used to encapsulate traffic to Active Directory, a range of ephemeral TCP por TS between 49152 to 65535 is required.

List of dependent ports:

Application protocol	Protocol	Ports
Active Directory Web Services (ADWS)	Tcp	9389
Active Directory Management Gateway Service	Tcp	9389
Global Catalog	Tcp	3269
Global Catalog	Tcp	3268
Icmp		No Port number
LDAP Server	Tcp	389
LDAP Server	Udp	389
LDAP SSL	Tcp	636
IPsec ISAKMP	Udp	500
Nat-t	Udp	4500
Rpc	Tcp	135
RPC randomly allocated high TCP ports1	Tcp	1024-5000 49152-655352
Smb	Tcp	445

2. Cluster Service

The Cluster service controls Server Cluster operations and manages the Cluster database. A cluster is a collection of independent computers, act as a single computer. Managers, programmers, and users see the cluster as a single system. The software distributes data among the nodes of the cluster. If a node fails, other nodes provide the services and data that were formerly provided by the missing node. When a node was added or repaired, the cluster software migrates some data to that node.

System Service Name: CLUSSVC

Application	Protocol	Ports
Cluster Service	Udp	3343
Cluster Service	Tcp	3343 (this port is required during a node join operation.)
Rpc	Tcp	135
Cluster Administrator	Udp	137
Randomly allocated high UDP ports1	Udp	Random port number between 1024x768 and 65535 Random port number between 49152 and 655352

Note:

Additionally, for successful validation on Windows Failover Clusters on + and above, allow inbound and outbound traffic For ICMP4, ICMP6, and Port 445/tcp for SMB.

Part Two: The reply from Microsoft Data Platform MVP Mail list

Part III: In-depth learning about the principles of AD and WSFC

1. Ad-to-445 dependency

When each client is powered on, it needs to access a SYSVOL share on the DC inside the ad and download the Group Policy. Each boot will be accessed through this sharing Group Policy.

SYSVOL:

SYSVOL is a shared folder that stores copies of the domain's public file servers and replicates between all domain controllers in the domain. The Sysvol folder is created when you install AD, and it is used to store information such as GPOs, script, and so on. Also, the information that is stored in the Sysvol folder is copied to all DCs in the domain.

Netlogon:

The Netlogon (network logon) service registers all SRV resource records for the domain controller. These records appear in the DNS server's forward query area in your domain name in the _msdcs, _sites, _tcp, and _udp folders. Other computers use these records to query information about the domain's Active Directory.

2. WSFC dependency on 445

The cluster database is one of the main concepts in the operation of WSFC, and the cluster database contains the current state of the cluster application, for example, the current node 1 runs a DHCP role, the status is on-line, a file server role is running, the status is offline, and the cluster configuration, cluster member configuration, cluster resource additions, creation , start, delete, stop, offline and other state changes, the cluster database is to help each node know what the other side of the Cluster service running, once the other side down, will follow the status information inside the cluster database connected to the shared storage for failover on-line operation.

WSFC is used in the replication cluster database through administrative sharing. The cluster database is in the node C and registry, and when one node modifies the cluster information, it is replicated to the other nodes and to the witness disk.

One node goes down, other nodes refer to the cluster database, access the shared disk, and go online to the app hosted by that node. The daily runtime is synchronized at each node to ensure that each node records consistently.

Part IV: Learning about new features related to Windows Server 2016 and SQL Server 2016/2017

1. Domain Independent Availability Groups (a new feature introduced in SQL Server 2016)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/domain-independent-availability-groups

In Windows Server + SQL Server 2016 environments, AlwaysOn AG supports the construction of AG in a workgroup environment, or across a domain environment. Even if no domain ag,ag is required to support WSFC, and WSFC needs to open 445 ports, FCI so far, still need to be deployed in a domain environment. This feature does not eliminate 445 port access.

2. Distributed availability groups (a new feature introduced in SQL Server 2016)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/distributed-availability-groups

Distributed availability groups, which can extend AlwaysOn ag across two different wsfc, to expand AG. This feature also does not eliminate 445 port access.

3. Read-scale availability groups (a new feature introduced in SQL Server 2017)

Https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/read-scale-availability-groups

In SQL Server 2016 and earlier versions, all availability groups require clustering. Clusters are used to provide business continuity for high availability and disaster recovery (HADR). Additionally, the secondary replica is configured to perform read operations. If the target is not high availability, configuring and running the cluster consumes considerable operational overhead. SQL Server 2017 introduces a read scale availability group that does not require clustering.

If the business requirement is a resource that transforms a task-critical workload running on the primary replica, the user can now use read-only routing or connect directly to a readable secondary replica. Without the need to rely on integration with any clustering technology. These new features are supported by SQL Server 2017 on Windows and Linux platforms.

This is not a high availability configuration. There is no need to monitor infrastructure, coordinate fault detection, and automate failover. Without clustering, SQL Server cannot provide a low recovery time objective (RTO) that can be provided by an automated high availability solution. If you need high availability features, use Cluster Administrator (Windows Server failover cluster on Windows or Pacemaker on Linux).

The Read scale Availability group provides disaster recovery capabilities. Recovery point Objective (RPO) 0 is available when a read replica is in synchronous-commit mode. To fail over the read scale availability group, see fail over the primary replica on the read scale availability group.

Overall, this feature has only the DR function and does not support ha. This feature is intended to be used only to deploy multiple read replicas on multiple servers, sharing read pressure.

4. SQL Server availability Groups on Linux

Https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-availability-group-overview

Configure AG on Linux based on pacemaker.

5. SQL Server Failover Cluster Instance on Linux

Https://docs.microsoft.com/en-us/sql/linux/sql-server-linux-shared-disk-cluster-concepts

Configuring an FCI official document on Linux is also rudimentary and, from shared storage, currently supports iSCSI, NFS, and SMB protocols. Look forward to follow-up support for fiber protocols and hybrid deployment with AG.

Part V: Solutions and recommendations for the prevention of worm viruses

Under analysis, there are 3 solutions:

1. Upgrade the appropriate security patches

For important security patches It is recommended to hit, from secondary to primary in Maintenance window rolling upgrade.

2. Modify the database server share port

Based on previous experience, you can modify 445 ports on the client and server side, and the share will function properly. However, this scenario does not prevent access to the share.

3. Place the database server into the DMZ zone

Based on past experience, e-commerce company in order to pass the PCI international authoritative certification, in the construction of the PCI environment, the production payment and account database into the environment, in fact, is a DMZ area, restricting access to the Regional database server IP and port.

I suggest:

1. Hit the security patch on the SMB vulnerability.

2. Access the database server to the DMZ zone.

Survey report on 445 ports used in SQL Server AlwaysOn