Troubleshooting Error "TCP/IP connection to the host <HOSTNAME>, port <PORT> has failed
When attempting to link a MSSQL dSource, the following error is displayed when validating the database user credentials:
The TCP/IP connection to the host <HOSTNAME>, port <PORT> has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
*where <HOSTNAME> and <PORT> show the defined MSSQL server hostname and service port in your error message.
However, this error can have several causes which are not immediately obvious. This tip explains the possible causes and the steps an end user can do to self diagnose and troubleshoot this error.
Cause & Resolution
The error may in fact have one or more of the following causes:
- Failure to perform DNS resolution of the MSSQL server name returned by MSSQL itself
- Connecting to the designated port from the Delphix Engine
- Incorrect username and/or password
- Granular MSSQL access restrictions based on login source host
It is a much simpler to investigate these things in order starting with MSSQL. Because the first two items are network related, if they are not the root cause then something should be logged by MSSQL indicating a reason for the failure. If MSSQL never detected an attempt, then attention can be focused on network issues. A logical approach might be:
1. Check the Windows source host
As a user in the Administrators group, log on to the Windows host for the desired dSource and open Event Viewer. Any MSSQL level log on attempt that actually reached the host should result in a MSSQL event logged in the Application log. However, checking for events in System and Security logs is also recommended. If a failed attempt has been logged, MSSQL will indicate why log on was rejected in the event text. Incorrect username and/or passwords, or granular access restrictions should be clearly visible here.
2. Verify DNS resolution of the MSSQL server name
If MSSQL logged nothing to any of the Windows event logs, then next look at the network. The hostname displayed in the error message is returned by MSSQL during environment discovery and must be resolvable to any host that will connect to the MSSQL instance. To confirm that DNS resolution for this hostname also works for the Delphix Engine you should perform an
nslookup from another host on the same subnet as the Delphix Engine and using the same configured DNS server. For example, a Delphix Engine is on network 188.8.131.52/16 and is not able to verify the credentials for MSSQL server "somehost.corp" and was configured to use DNS sever 184.108.40.206. Another host in the subnet (such as 220.127.116.11) can be used to verify DNS with the following command:
$ nslookup <HOSTNAME> <DNS_SERVER>
$ nslookup somehost.corp 18.104.22.168
If resolution fails then the DNS servers must be updated to resolve the server name returned by MSSQL, or MSSQL reconfigured to provide a server name that is resolvable by all hosts that need to reach it. In most cases, it is simpler and more correct to address this problem at the DNS configuration level.
If DNS resolution for the server name succeeds, a test should also be performed for the returned IP address. Correct name resolution requires both forward (name based) and reverse (IP address based) name resolution to function correctly.
3. Verify the configured instance port
The instance port shown in the error message should be open and accepting connections from the Delphix Engine. Similar to the DNS test above, another host on the same subnet as the Delphix Engine should be used to attempt to verify the port is open. Using the same example where the Delphix Engine is on network 22.214.171.124/16, another host (such as 126.96.36.199) could be used to perform a telnet connection to the defined port. In most cases this will be port 1433.
$ telnet <HOSTNAME> <PORT>
$ telnet somehost.corp 1433
It this succeeds then some message should indicate that the connection is open.
If testing connectivity using telnet on Windows, success might be indicated by the command prompt window turning blank. An error message will be shown if the connection fails.
This test cannot show whether or not firewall rules exist that would specifically block access to the MSSQL server port from the Delphix Engine.The test is a sanity check but a network administrator may be required to confirm exactly what is and is not configured on any firewalls between the Delphix Engine and the MSSQL host.
4. Verify that an MSSQL authenticated login is possible from another host via MSSQL Management Studio
Using another host on the same subnet as the Delphix Engine, perform a log on test to the MSSQL server using the same hostname and port shown in the error message and the same username and password that was supplied in the Add dSource workflow. Ensure that the log on attempt is configured for SQL Server Authentication and not Windows Authentication.
5. Use the Delphix Command Line Interface (CLI) to test connectivity from the Delphix Engine itself to the desired host/port: