Testing to ensure connectivity to remote servers is a crucial step for system administrators. In the Windows environment, tools like PuTTy or PowerShell come in handy for testing TCP port connections. This post dives into my preferred method using PowerShell, covering key aspects such as checking open ports, troubleshooting, and highlighting essential ports for database administrators.

Using PowerShell Test-NetConnection

For up-to-date PowerShell versions, the Test-NetConnection (tnc) cmdlet allows us to test network connectivity to remote servers with ease.

Although this post is directed to testing connectivity to a SQL Server host, here are some other default database server ports of note:
# PostgreSQL (5432)
# MySQL (3306)
# Oracle (1521)
# Sybase (5000)
# DB2 (50000)

Below is an example of testing port 1433 which is the default SQL Server port:

# Test Network Connection (SQL Server Port)
Test-NetConnection -ComputerName lab-sql1.whyte.net -Port 1433

Alternatively, the shorter syntax using tnc is also available:

# Tnc network test to local MSSQL example
tnc 127.0.0.1 -Port 1433

# Test Internet connectivity (Google on HTTPS port)
tnc 8.8.8.8 -Port 443

Ensure successful connectivity by checking if TcpTestSucceeded returns true.

Checking Open Ports (Pre Win08/Svr2012)

For legacy servers running older PowerShell versions, this method proves effective. Below is a script for checking whether a port is open. Simply enter the IP address and port number when prompted:

# Check a port is open (pre Win08/Svr2012)
$Ipaddress= Read-Host "Enter the IP address:"
$Port= Read-host "Enter the port number to access:"
$t = New-Object Net.Sockets.TcpClient
$t.Connect($Ipaddress,$Port)
if($t.Connected)
    {"Port $Port is operational."}
else {"Port $Port is closed."}

Troubleshooting Network Connectivity Issues

If connectivity fails, consider the following checks:

1. Server Listening: Ensure there is a service actively ‘listening’ on the remote server port.
2. Network Configurations: Verify DNS settings, network configurations, and Security Groups.
3. Firewalls: Check both infrastructure-level and local host firewalls for potential restrictions.

Note: Diagnosing complex connectivity issues may involve various components, including firewalls, NAT Gateways, and load balancers. While this test is straightforward, for thorough troubleshooting, consider running additional tests with tools like PuTTY.