Connection Debugging for the Nursing Control Panel Application
(How to Debug persistent failure of NCP to Connect to database)
Multi instance sql server installations – those other than the default instance will utilise dynamic ports https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-2017
On startup, SQL Server Browser starts and claims UDP port 1434. SQL Server Browser reads the registry, identifies all SQL Server instances on the computer, and notes the ports and named pipes that they use. When a server has two or more network cards, SQL Server Browser will return all ports enabled for SQL Server.
When SQL Server clients request SQL Server resources, the client network library sends a UDP message to the server using port 1434. SQL Server Browser responds with the TCP/IP port or named pipe of the requested instance. The network library on the client application then completes the connection by sending a request to the server using the port or named pipe of the desired instance.
Using a Firewall
To communicate with the SQL Server Browser service on a server behind a firewall, open UDP port 1434 in addition to the TCP ports used by SQL Server (for example, 1433 or 20252).
Recommended utility to diagnose:
I Recommend using the microsoft utility PortQry version 2.0
The graphical UI version of PrtQry is easiest to use.
Microsoft PortQry utility link. The download link is at very bottom of the Microsoft 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
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 BUT WILL NEED TO BE LOCKED TO PRIVATE/DOMAIN NETWORK OF FIXED IP ADDRESSES..
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.
Ensure that the Sql Server Browser service is Running and set to automatic start.
