Connection Debugging for the Doctors Control Panel Application

OR

How to Debug persistent failure of DCP to Connect to database.

 

Theory:

Special Note:

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).

 

 

Recommendation:

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.

 

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.

 

 

 

 

=============================================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.

 

 

=============================================End Example

 

 

 

=============================================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.

 

=============================================End Example

 

 

=============================================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.

 

 

=============================================End Example

 

 

 

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

C:\Windows\SysWOW64\SQLServerManager14.msc

SQL Server 2016

C:\Windows\SysWOW64\SQLServerManager13.msc

SQL Server 2014 (12.x)

C:\Windows\SysWOW64\SQLServerManager12.msc

SQL Server 2012 (11.x)

C:\Windows\SysWOW64\SQLServerManager11.msc

 

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

 

 


Check On SqlServer Browser Service on Server using Windows Control panel

 

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


Create Firewall Rule on Server using Windows Control Panel allowing UDP Port 1434

 

Terms of use

Privacy Statement

Copyright © 2018 Doctors Control Panel Services