* DCP - SQL Connection Debug
SQL Connection Debug

Connection Debugging for the Doctors Control Panel Application

(How to Debug persistent failure of DCP 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.

https://docs.microsoft.com/en-us/sql/sql-server/install/configure-the-windows-firewall-to-allow-sql-server-access?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-a-server-to-listen-on-a-specific-tcp-port?view=sql-server-2017

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.

fwcmv

snip_20151008231259

snip_20151008230729

snip_20151008232421

snip_20151008232446

snip_20151008232519

Ensure that the Sql Server Browser service is Running and set to automatic start.

snip_20151008232602

snip_20151008233023

snip_20151008233059

snip_20151008233338

snip_20151008233318

snip_20151008233410

snip_20151008233512

snip_20151008233550

snip_20151008233614