TCP Port Test Fails When SQL Server Service Is Stopped

One of my favorite tools when troubleshooting SQL Server connection errors is PowerShell's Test-NetConnection commandlet.

Test-NetConnection -ComputerName MyDbServerName -Port 1433

The above commandlet will attempt to contact a device named "MyDbServerName" and then check to see if anything is listening on port 1433 (the default SQL Server TCP port).

"TCP Connect Failed"

If the device can be contacted (i.e., the ping succeeds), but the value of TcpTestSucceeded is False there are three primary explanations:

1. there is a firewall on the remote device blocking the requested port
2. SQL Server is set to use dynamic TCP ports
3. the SQL Server service listening on that port is Stopped (i.e., not Running)

Which of these two explanations is most likely depends on whether this is your first time trying to connect to the database server.

• if you get this error the first time you try to connect to the database server, it is most likely a firewall misconfiguration or SQL Server is using dynamic TCP ports
• if you get this error on a computer that has previously been able to connect to the database server, it likely means the service is not running

Static vs. Dynamic SQL Server TCP Ports

Checking for TCP connectivity on a specific port is generally only worth doing if SQL Server has been configured to use static ports.

If it is set to use dynamic ports, then the port it is listening on will be different every time the service restarts.  

To determine whether SQL Server is using static or dynamic ports, follow these steps on the machine where SQL Server is installed:

1. Open "SQL Server Configuration Manager"
2. Click on "SQL Server Network Configuration" > "Protocols for MSSQLSERVER"
3. Double-click on TCP/IP
4. Click on the "IP Addresses" tab
5. Scroll to the bottom of the window
6. Check the values of "TCP Dynamic Ports" and "TCP Port" in the IPAll section

If dynamic ports are being used, the value of "TCP Dynamic Ports" will be 0.  Otherwise it will be blank.

If "TCP Dynamic Ports" is blank, then it means we are using a static port.  Specifically, the static port we are using is listed in the box next to "TCP Port."  The screenshot below shows a default instance of SQL Server (named "MSSQLSERVER") that is listening on the default SQL Server port, 1433.

PowerShell Commands

Here are a few handy PowerShell commands in addition to Test-NetConnection.

I should note that these are not PowerShell commands per se, but it makes sense to call them from PowerShell because Test-NetConnection is a PowerShell-specific command.

sc.exe query

SC is a command line program used for communicating with the Service Control Manager and services.

NOTE: The identifier sc is also an alias to Set-Content in PowerShell, so when you call the utility from PowerShell you must include the .exe file extension.

To return the state of the default SQL Server instance:

sc.exe query MSSQLSERVER

To return the state of the default SQL Server Express instance:

sc.exe query MSSQL$SQLEXPRESS

To return the state of any other named SQL Server instance, replace "SQLEXPRESS" with the instance name:

sc.exe query MSSQL$MyNamedInstance

net start / stop (AND sc.exe start / stop)

To start or stop a service, you can use the net start MyServiceName or net stop MyServiceName commands, respectively.

The sc.exe utility also supports start and stop commands, but these commands "fire and forget" a status update message to the service controller.  These commands will return immediately, showing a STATE of START_PENDING or STOP_PENDING to indicate that the message has been sent, but that the service's state has not yet changed.

For that reason, I prefer the net start / stop versions as they will not return to the command line until the service has either been started/stopped or the start/stop fails.

Sample Usage

The screenshot below provides proof of how Test-NetConnection -Port 1433 behaves with respect to the SQL Server service's running status.

If the service is running, the TCP test succeeds on port 1433.  If the service is stopped, the TCP test fails.

Note that this is an Administrator PowerShell session.  By default, only administrators can start or stop Windows services (you can override this for individual services).

Additionally, note that the RemoteAddress and SourceAddress are the same because I was testing access to my local machine (mjw20) from my local machine (mjw20).

Here's a copy-pasteable recap of the commands I sent to PowerShell:

PS> sc.exe query MSSQLSERVER
PS> Test-NetConnection -ComputerName mjw20 -Port 1433
PS> net stop MSSQLSERVER
PS> Test-NetConnection -ComputerName mjw20 -Port 1433
PS> sc.exe query MSSQLSERVER
PS> net start MSSQLSERVER
PS> Test-NetConnection -ComputerName mjw20 -Port 1433

Cover image created with Microsoft Designer