Commands / Test-sqmSQLFirewall
Maintenance

Test-sqmSQLFirewall

Tests whether firewall and network allow a raw TCP connection to SQL Server, independent of any SQL login. For named instances, first queries the SQL Browser service (UDP 1434) to learn the dynamic TCP port before testing it — exactly like the SQL Native Client does under the hood.

Module: sqmSQLTool
Requires: PowerShell 3.0+
ShouldProcess: No
Output: PSCustomObject[]

Execution Flow

START foreach -Server (pipeline-capable) -Instance given? UDP 1434 CLNT_UCAST_INST packet to SQL Browser, parse "tcp;<port>" from reply Port returned? NO: warn, fallback -Port YES: effectivePort = dynamic port NO: effectivePort = -Port (default 1433) TcpClient.BeginConnect(server, effectivePort, TimeoutSeconds) TCP connected? Status = OK Status = Failed Add result (Server, Port, DynamicPort, TcpReachable, Status) next server → Return PSCustomObject[] (one per server) DONE
If the SQL Browser (UDP 1434) is unreachable — common when only TCP is opened in the firewall — the function falls back to whatever -Port was given (default 1433) and logs a warning, rather than failing outright. This means "Failed" for a named instance can mean either "TCP port blocked" or "SQL Browser blocked and the fallback port also happens to be wrong" — check DynamicPort in the result to tell them apart.

Parameters

ParameterTypeRequiredDescription
-ServerString[]RequiredHostname(s) or IP(s) to test. Pipeline-capable.
-PortInt (1–65535)OptionalTCP port to test. Default: 1433. Ignored when -Instance is given and the SQL Browser returns a dynamic port.
-InstanceStringOptionalNamed instance to resolve via SQL Browser (UDP 1434) before testing TCP.
-TimeoutSecondsInt (1–60)OptionalTCP connection timeout. Default: 5.
-ContinueOnErrorSwitchOptionalContinue with the next server on error instead of aborting the whole run.
-EnableExceptionSwitchOptionalThrow exceptions immediately (overrides -ContinueOnError).

Return Value

Returns a PSCustomObject[] — one entry per server — with: Server, Port, Instance, DynamicPort (bool), TcpReachable (bool), Status (OK, Failed, or Error), Message.

Examples

Example 1 — Test the default instance on port 1433

Test-sqmSQLFirewall -Server "SQL01"

Example 2 — Test a custom port

Test-sqmSQLFirewall -Server "SQL01" -Port 54321

Example 3 — Resolve and test a named instance's dynamic port

Test-sqmSQLFirewall -Server "SQL01" -Instance "SAGE"

Example 4 — Sweep a server list via pipeline

"SQL01","SQL02","SQL03" | Test-sqmSQLFirewall -Instance "PROD" -TimeoutSeconds 3
⬆ Index
All Commands