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
False there are three primary explanations:
- there is a firewall on the remote device blocking the requested port
- SQL Server is set to use dynamic TCP ports
- 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:
- Open "SQL Server Configuration Manager"
- Click on "SQL Server Network Configuration" > "Protocols for MSSQLSERVER"
- Double-click on TCP/IP
- Click on the "IP Addresses" tab
- Scroll to the bottom of the window
- 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.
Here are a few handy PowerShell commands in addition to
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 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
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
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.
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