SQLCMD – How to Use Dynamic Parameters for Connection

Check the SQL Server error log to determine the remote DAC port number. It will be contained in the message Dedicated admin connection support was established for listening remotely on port nnnn.. Verify that port is allowed through the firewall.

You can verify TCP port connectivity with TELNET or the Powershell one-liner below, specifying the port number as the argument piped to the echo command.

nnnn | % { echo ((new-object Net.Sockets.TcpClient).Connect("YourServerName",$_)) "server listening on TCP port $_" }

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;