Sql-server – SQL Server connectivity without specifying tcp/port

Have you allowed remote connections in Surface Area Configuration -> Services and Connections -> Remote Connections?

I haven't specifically tried recently, but I have enabled TCP & Named pipes. You can also force a protocol if you need to, e.g. in the server name, use NP:myserver in order to force named pipes, or TCP:myserver in order to force TCP.

Try enabling named pipes in the connection setup too - client and server.

Also, hamachi normally uses its own IP range, so you should check that you can actually telnet to port 1433 of your server.

Some additional investigation is needed here. First, it is possible that you are filtering out too much of the netstat output in your findstr filter. Instead of "processID", check for the port number using ":1434". Also, using the -q switch instead of -a might be better as it will show bound, nonlistening ports. For example:

netstat -qof | findstr ":1434"

OR, if you start the command prompt as an administrator, you can add the -b switch to print out the name of program associated with each process (saves the step of needing to map the Process ID to the program). When doing this, the output of each entry is on 2 lines, so you won't see the additional info if you use findstr as those lines will never have the matching string. Instead, just pipe to more as follows:

netstat -qofb | more

Now, using netstat is handy to research to see if a port is in use, but for this issue it is more direct to see what SQL Server thinks is going on, and fortunately there are some views that have the info so you don't need to scan / parse the error log. You should check the following:

• sys.tcp_endpoints
• sys.dm_tcp_listener_states

So just execute the following:

SELECT * FROM sys.tcp_endpoints;
SELECT * FROM sys.dm_tcp_listener_states;

If the first view, sys.tcp_endpoints, shows that "Dedicated Admin Connection" has a state_desc other than "STARTED", then there is an issue.

Now, in the second view, sys.dm_tcp_listener_states, if you do have "::1" (IP v 6) and/or "127.0.0.1" (IP v 4) for ip_address then it is only listening locally (i.e. remote administrative access is OFF). If you instead see "::" (IP v 6) and/or "0.0.0.0" (IP v 4), then remote admin access is ON.

By default remote administrative access is OFF. You can enable it by executing the following (this is not an "advanced" option):

EXEC sp_configure N'remote admin connections', 1; RECONFIGURE;