SQL DBA Blog

All admins need a tool to test connectivity to remote servers on TCP ports. In Windows, this is commonly done using PuTTy or PowerShell.

This post is a note on my favourite way of testing remote TCP connections in Windows, which is using PowerShell:
# Check a Port is Open (Pre Win08/Svr2012)
# Check a Port is Open (Test-NetConnection)
# Troubleshooting Remote Ports

Important ports to remember in the life of a DBA may include:
# SQL Server (1433)
# RedShift (5439)
# PostgreSQL (5432)
# MySQL (3306)
# Oracle (1521)

Check a Port is Open (Pre Win08/Svr2012)

This is for when you’re on a legacy server running an old version of PowerShell. I managed to spawn a Windows Server 2008 R2 box from the AWS Marketplace for this demo.

# 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."}

Enter IP address and port number when prompted.  

Below is an example of no connectivity, it’s failing to connect on port 1433. The server I’m testing does not have SQL Server installed, so there was nothing listening on that port.

Check a Port is Open (Test-NetConnection)

I’ve used Test-NetConnection frequently for years. It’s built-in to recent Editions of Window Server and is easier to use. We can also use ‘tnc’ as displayed in the example code below.

# Test remote port is open
Test-NetConnection -ComputerName lab-sql1.whyte.net -Port 1433

# Same as above command using alternative syntax/ip
tnc 172.31.18.100 -port 1433

We can see from the screenshot above this test passed as TcpTestSucceeded came back true.

Note:
The traffic between you and another server may be flowing through various components that can include; local/internal/external firewalls, NAT Gateways, Security Groups/NACLs, load balancers & more.
Diagnosing connectivity issues can be very complex. This is a simple test and might not be reflected in certain network traffic logs – if you’re troubleshooting maybe run your second port test with Putty.

Troubleshooting Remote Ports

If connectivity is failing, a few things to check may include:
# There has to be something ‘listening’ on the remote server port.
# Network (Inc. DNS) configurations & Security Groups.
# Firewalls (at the Infrastructure level or local host config).