Shulou(Shulou.com)06/01 Report--

SQL Server AlwaysOn port 445 usage survey report

Part 1: Documentation from Microsoft

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

SQL Server 2014 AlwaysOn FCI & AG hybrid deployment for our production environment, based on Active Directory and Cluster Service.

The following information, referenced in KB 832017, describes the port requirements associated with 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 domain controllers. Domain controllers, client computers and application servers require network connectivity to Active Directory over specific hard-coded ports. Additionally, unless a tunneling protocol is used to encapsulate traffic to Active Directory, a range of ephemeral TCP ports between 1024 to 5000 and 49152 to 65535 are required.

Dependency port list:

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 ports¹

TCP

1024 - 5000

49152 - 65535²

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 that 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 is 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 ports¹

UDP

Random port number between 1024 and 65535

Random port number between 49152 and 65535²

Note:

Additionally, for successful validation on Windows Failover Clusters on 2008 and above, allow inbound and outbound traffic for ICMP4, ICMP6, and port 445/TCP for SMB.

Part 2: Replies from Microsoft Data Platform MVP Mail List

Part 3: In-depth study of AD and WSFC principles

1. AD Dependence on 445

Each client needs to access a sysvol share on the DC in AD to download the group policy when booting. Each time you boot up, you have to access it, and distribute the group policy through this sharing.

SYSVOL：

SYSVOL is a shared folder that stores copies of a domain's common file server, and they replicate among all domain controllers in the domain. Sysvol folder is created when AD is installed, and it is used to store GPO, Script, and other information. At the same time, information stored in the Sysvol folder is copied to all DCs in the domain.

Netlogon：

The Netlogon service registers all srv resource records for domain controllers. These records appear in the_msdcs, _sites, _tcp, and _udp folders in the DNS server's forward query zone for your domain name. Other computers use these records to query domain Active Directory for information.

2. WSFC Dependency on 445

Cluster database is one of the main concepts of WSFC operation. The cluster database records the current status of cluster applications. For example, node 1 is currently running a DHCP role, the status is online, a file server role is running, the status is offline, and the cluster configuration, cluster member configuration, cluster resource addition, creation, start, delete, stop, and offline status changes. Cluster database is to help each node know what kind of cluster service is running on the other side. Once the other side goes down, it will connect to the shared storage according to the state information in the cluster database for failover online operation.

WSFC is used in replication cluster databases through administrative sharing. Cluster databases are located on disk C and registry on each node. When one node modifies cluster information, it replicates to other nodes and witness disks.

When one node goes down, the other nodes refer to the cluster database, access the shared disk, and go online with the application hosted by the node. Synchronization at each node during daily operation ensures consistent records at each node.

Part 4: 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 2016 + SQL Server 2016 environments, AlwaysOn AG supports AG setup in Workgroup environments or cross-domain environments. Even if you build a domainless AG, AG still needs WSFC support, and WSFC needs to open port 445;FCI has so far still needed to be deployed in a domain environment. This feature does not eliminate port 445 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, AlwaysOn AG can be extended across two different WSFCs. This feature also does not eliminate port 445 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, clustering is required for all availability groups. Clustering is used to provide business continuity, high availability and disaster recovery (HADR). In addition, secondary replicas are configured to perform read operations. If the goal is not high availability, configuring and running the cluster consumes significant operational overhead. SQL Server 2017 introduces read-scaling availability groups that do not require clustering.

If the business requirement is to convert resources for mission-critical workloads running on the primary replica, users can now use read-only routing or connect directly to readable secondary replicas. Without relying on integration with any clustering technology. SQL Server 2017 on Windows and Linux platforms supports these new features.

This is not a high availability configuration. There is no need to monitor infrastructure, coordinate failure detection, and perform automatic failover. Without clustering, SQL Server cannot deliver the low recovery time objectives (RTOs) that automated high availability solutions can deliver. If high availability ××× is required, use Cluster Manager (Windows Server failover cluster on Windows or Pacemaker on Linux).

Read Scaled Availability Groups provide disaster recovery capabilities. When a read-only replica is in synchronous commit mode, it provides a recovery point objective (RPO) of 0. To fail over a read scaled availability group, see Failing over a primary replica on a read scaled availability group.

Overall, this feature only has DR functionality and does not support HA. This feature is useful just to distribute the read burden in order to deploy multiple read-only replicas on multiple servers.

4. SQL Server 2017 Availability Groups on linux

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

Configuring AG on Linux is based on Pacemaker.

5. SQL Server 2017 Failover Cluster Instance on linux

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

The official documentation for configuring FCI on Linux is still very rudimentary. From the perspective of shared storage, iSCSI, NFS and SMB protocols are currently supported. Expect follow-up support for fiber optic protocols and hybrid deployment with AG.

Part V: Solutions and recommendations for worm prevention

After analysis, there are three solutions:

1. Upgrade appropriate security patches

For important security patches, it is recommended to roll from Secondary to Primary in the maintenance window.

2. Modify database server shared ports

Based on past experience, port 445 can be modified on both client and server sides, and sharing can work normally. However, this scenario does not prevent access to shares.

3. Place the database server in the core quarantine zone

According to past experience, in order to pass PCI international authority certification, e-commerce companies will put production payment and account database into this environment when building PCI environment, which is actually a core isolation area, restricting access to IP and port of database server in this area.

I suggest:

1. Security patch for SMB vulnerability.

2. Access the database server to the core quarantine zone.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.