How to ping/test connection to SQL Server without software (like through cmd)?

Question

Is there a way to test a connection to a SQL Server through the command line or without any extra software? I've tried the ping and telnet methods shown in this article, but they both fail to find my SQL Server. Note that the connection is fine, I can connect to the server through SSMS, but it would be useful to be able to troubleshoot the connection otherwise.

For example, we have people working from home and I want to be able to test the connection to the database server without having to install SSMS on their machine.

The server name looks like: SERVER\SQLEXPRESS

And I tried ping SERVER\SQLEXPRESS (Ping request could not find host) and telnet SERVER 1433 (Could not open connection to the host, on port 1433)

EDIT: I can ping the server just fine ping SERVER

EDIT2: Everything I have tried I test with both the name and the ip. The DNS is fine so it shouldn't cause an issue to use one or the other

Cushitic

Can you use netcat? Something like nc -vz hostname 1433 perhaps. – Preselector

ping and telnet are, afaik, a part of windows. And if you can't ping the machine, you certainly cannot telnet to a port on that machine. And you don't ping the database engine name, you ping the machine so your ping attempt doesn't work because you did not supply a machine name. Try pinging using the IP address. – Marinemarinelli

ping server. You cannot ping server\SQLEXPRESS the SQL instance name. Does telnet server 1433 do nothing or does it connect with no output? Note that pings are often blocked by firewalls and/or anti-malware software. – Valli

@Marinemarinelli Firewall might block pings, so they could easily fail while the connection will work. Also, pinging an IP address is an intermediate troubleshooting test, but ultimately you'll need server to be resolvable unless the application connecting to SQL can be changed to an IP. – Valli

@Preselector using ncat -vz SERVER 1433 only gives me timeouts, while I can connect to other ports (i.e 8080, or 29418 for gerrit) – Cushitic

@Valli true but the issue of general connectivity remains as the first hurdle. And I will add that you can connect via IP just as easily as you can using a name so you don't need the name to be resolvable - it's just a little easier for humans to do. – Marinemarinelli

@Marinemarinelli I can ping the server itself just fine, and I'm assuming since SSMS can connect to the SQL Server instance, the ports are open as well. Am I wrong in this assumption? Added in the question that I use both the name and ip to do tests. DNS is fine, it's not the issue – Cushitic

@Valli telnet server 1433 eventually gives "could not open connection to the host on port 1433: Connect failed" – Cushitic

Are you saying that SSMS connect to SERVER\SQLEXPRESS, but that same computer cannot telnet SERVER 1433 ?? I am having difficulty imaging how this would be possible. Though perhaps it means your SQL server is using a custom port instead of the default 1433. Or maybe it's not using IP at all. – Valli

@Marinemarinelli Yep -- that's why I said it's an intermediate troubleshooting step unless the application can be changed to use an IP address. – Valli

This might give you more things to double check. dba.stackexchange.com/questions/62165/… – Valli

SQL Express only listens on localhost:1433 by default. You have to use SQL Server Configuration Manager to enable it to listen on other IP addresses. Are you trying to telnet to port 1433 on the same machine, or from one across a network? – Electrokinetic

@Electrokinetic Different machine on our local network. Will try to see if it has been changed to another Port. – Cushitic

Answer 1

..it would be useful to be able to troubleshoot the connection otherwise.

For example, we have people working from home and I want to be able to test the connection to the database server without having to install SSMS on their machine.

Assuming your users are using windows you could send them a udl file.

If you create a blank text document and save it with the extension udl instead of txt. You can then double click the file. Below is an example of one I created on my desktop.

Opening that file in windows will give you the following prompt and allow you to test connection.

You can do this yourself and send it to the user or walk them through it. I use it quite often when testing connectivity on boxes that don't have SSMS installed and it gives the added bonus of providing proper error messages.

Outgeneral

Not as straight-forward as I'd hoped (since there is the added step of having to create or transfer that file on the machine), but this is definitely a very nice and easy way to test a connection. – Cushitic