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:
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;
For Default Instances
- TCP port 1433
- TCP port 1434 (For DAC Connection)
For Named Instances
- TCP Port (Whichever port the SQL Server is listening on – Check the Error Log for Port Details)
- UDP Port 1434 (For Browser Services)
- UDP (and TCP) Port on which Dedicated Admin connection is listening (UDP is what worked for me). Check the SQL Server log to note as to on which port was Dedicated admin connection was established:
“Message Dedicated admin connection support was established for listening locally on port XXXXX”
For Named instances, make sure that the Browser Service is running if the external applications are connecting using the Instance name.
Also note that for Named instances, it is better to configure SQL Server to listen on a static port otherwise firewall management will become difficult as SQL Server will listen on a different port every time it’s restarted.
You must log on to comment.
verasearch3 said
see also:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-ver15