I Recommend using the microsoft utility PortQry version 2.0
The graphical UI version of PrtQry is easiest to use.
PortQry Can be downloaded from Microsoft (CLICK THE LINK TO GO TO THE PAGE)
The link to download is at very bottom of the Microsoft page
Full instructions are on the page.
Brief Intro to the PortQry Utility (UI Version)
Type the Full Name of the Sql Server in the Top Box (127.0.0.1 tests the local setup)
Select 'Sql Service' in the Query Type
Click on the 'Query' Button'
Look to find UDP port 1434 (ms-sql-m service): LISTENING or FILTERED -- this indicates successful connection on UDP
If UDP port 1434 (ms-sql-m service): NOT LISTENING is found , then there is a problem
Either the SqlBrowser Service is not running or firewall on the server is blocking UDP port 1434
SQL Server also requires TCP Port 1433 to be open on the server and passed through firewall.
TCP Result(s) should be LISTENING
2 Simple Configuration Errors to look for
Look in Windows Firewall with Advanced Security and enable incoming rule, for port 1433 on TCP to any port, inside the Ports and Protocols tab. Also allowing the sqlserver.exe process through the firewall will work in a pinch.
checked SQL Server Network Configuration, in the protocols for the instance I was working with look at TCP/IP. By default it seems mine was set to disabled, which allowed for instance connections on the local machine but not using SSMS on another machine. Enabling TCP/IP did the trick for me.
PortQry reports the status of a TCP/IP port in one of the following three ways:
Listening: A process is listening on the port on the computer that you selected. Portqry.exe received a response from the port.
Not Listening: No process is listening on the target port on the target system. Portqry.exe received a response "Destination Unreachable - Port Unreachable" message back from the target UDP port. Or if the target port is a TCP port, Portqry received a TCP acknowledgement packet with the Reset flag set.
Filtered: The port on the computer that you selected is being filtered. Portqry.exe did not receive a response from the port. A process may or may not be listening on the port. By default, TCP ports are queried three times, and UDP ports are queried one time before a report indicates that the port is filtered.
Return Code analysis:
If the return code is 0, it indicates that connection was successful.
If return code of 1, it indicates that this DC was unable to communicate on required port. This indicates that server on destination machine is not running or that port is FILTERED on the firewall.
Return code 2 is normally reported for UDP ports as we don’t get an ACK for that communication. This can be ignored if it’s returned for a UDP port.
This could impact requirement for firewall port rules
On windows server using windows firewall – allowing the sqlserver.exe process through firewall is the easiest fix.
Usually there will be multiple instances of sqlxerver.exe running in the situation where multiple instances of sqlserver are installed.
More information on sql dynamic ports.
Configuring sql server for static port and allowing the port through the firewall is the recommended method by Microsoft as described the second link.
Special Note 2:
Your test cases where you cannot connect with "ServerName\Instance" but ARE able to connect to the server via "ServerName,Port" is what happens when you VPN into a network with Microsoft VPN. (I had this issue). For my VPN Issue I simply use the static port numbers to get around it.
This is appearently due to VPN not forwarding UDP Packets, allowing only TCP Connections.
In your case your firewall or security settings or antivirus or whatever may be blocking UDP.
I would suggest you check your firewall setting to specifically allow for UDP.
=============================================Example of a SUCCESS on UDP port 1434
UDP port 1434 (ms-sql-m service): LISTENING or FILTERED
Sending SQL Server query to UDP port 1434...
UDP port 1434 (ms-sql-m service): FILTERED
portqry.exe -n 192.168.1.10 -e 1434 -p UDP exits with return code 0x00000002.
=============================================Example of a FAILURE on TCP port 1433
TCP port 1433 (ms-sql-s service): FILTERED
portqry.exe -n 192.168.1.10 -e 1433 -p TCP exits with return code 0x00000002.
=============================================Example of a SUCCESS on TCP port 1433
TCP port 1433 (ms-sql-s service): LISTENING
portqry.exe -n 192.168.1.10 -e 1433 -p TCP exits with return code 0x00000000.
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers. SQL Server Configuration Manager is a Microsoft Management Console snap-in that is available from the Start menu, or can be added to any other Microsoft Management Console display. Microsoft Management Console (mmc.exe) uses the SQLServerManager<version>.msc file (such as SQLServerManager13.msc for SQL Server 2016 (13.x)) to open Configuration Manager. Here are the paths to the last four versions when Windows in installed on the C drive.
SQL Server 2017
SQL Server 2016
SQL Server 2014 (12.x)
SQL Server 2012 (11.x)
very simple way to find sql running ports which need to be opened in firewall.
make a note of the sqlsrvr.exe PID from taskmanager then run this command:
netstat -ano | findstr *PID*
it will show TCP and UDP connections of your SQL server (including ports) standard is 1433 for TCP and 1434 for UDP
Ensure that the Sql Server Browser service is Running and set to automatic start.