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.
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 126.96.36.199/16 and is not able to verify the credentials for MSSQL server "somehost.corp" and was configured to use DNS sever 188.8.131.52. Another host in the subnet (such as 184.108.40.206) can be used to verify DNS with the following command:
$ nslookup <HOSTNAME> <DNS_SERVER>
$ nslookup somehost.corp 220.127.116.11
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.
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 18.104.22.168/16, another host (such as 22.214.171.124) 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.
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.