The TCP/IP connection to the host local, port 1433 has failed

Products

CA Test Data Manager (Data Finder / Grid Tools) CA Unified Infrastructure Management On-Premise (Nimsoft / UIM)

Issue/Introduction

This error message ca be seen with various Test Data Manager (TDM) components and either MySQL, SQL Server, or SQL Server Express as the database. Here are a few example scenarios:

Scenario 1:

When setting up a connection profile for MS SQL Server Database in Datamaker and testing the connection, we received the following error message:

The server replied:
Could not get JDBC Connection; The TCP/IP connection to the host LOCALHOST, port 1433 has failed.
Error: "connect timed out. 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."

Scenario 2:

When trying to use a local SQL Server for setting up the required connection profile in TDM Portal. We are able to connect to this SQL Server using SQL Management Studio, as well as through Datamaker. When trying to set up the connection profile in TDM Portal, we get the below error.

Failed to connect.
The server replied: Could not get JDBC Connection; The TCP/IP connection to the host, port 1433 has failed. Error: "connect timed out. 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".

Scenario 3:

When trying to use the Repository with Subset, we get the following error message when launching the Subset from Datamaker, or launching Subset individually:

The TCP/IP connection to the host local, port 1433 has failed. Error: "null. Verify the connection properties, check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port, and that no firewall is blocking TCP connections to the port.

Environment

CA Test Data Manager (TDM)

UIM ( Unified Infrastructure Management) with SQL Server DB

Cause

There are several potential causes of this error message:

For SQL Server, specifying both the Instance Name or Port number, instead of just one.
For SQL Server Express, not having a static TCP port and having a dynamic entry instead.
SQL Server may not be running on the host or accepting TCP/IP connections at the port.
TCP connections could be blocked by a firewall or not enabled.
For SQL Server, using 'local' as the Server Name instead of localhost or the IP address.
Your profiles.xml file from C:\%AppData%\roaming\Grid-Tools folder may be corrupted or contain incorrect information.
The database may not be up and running.
The hostname, username, or password may be incorrect.

Resolution

Here are some various troubleshooting steps you can take to find the root cause of the error and their subsequent resolutions:

Enable TCP/IP:

Click the Windows Start button
Under Programs, select Microsoft SQL Server
Launch the SQL Server Configuration Manager
Expand SQL Server Network Configuration
Protocol
Enable TCP/IP Right box
Double-Click on TCP/IP
Go to IP Addresses Tap and Put port 1433 under TCP port.
- If you see that SQL Server/ SQL Server Browser State is 'stopped', right click on SQL Server/SQL Server Browser and click start.
- In some cases the above state can stop though TCP connection to port 1433 is assigned.

Enable TCP/IP (Listen All):

Open SQL Server Configuration Manager, and then expand SQL Server Network Configuration.
Click Protocols for Instance Name, and then make sure TCP/IP is enabled in the right panel and double-click TCP/IP.
On the Protocol tab, notice the value of the Listen All item.
Click the IP Addresses tab: If the value of Listen All is yes, the TCP/IP port number for this instance of SQL Server is the value of the TCP Dynamic Ports item under IPAll. If the value of Listen All is no, the TCP/IP port number for this instance of SQL Server is the value of the TCP Dynamic Ports item for a specific IP address.
Make sure the TCP Port is 1433.
Click OK.

Host reachable and running:

Check the host is actually reachable and running.
- A good check I often use is to use telnet, eg on a windows command prompt run: telnet 127.0.0.1 1433
  - If you get a blank screen, it indicates network connection established successfully, and it's not a network problem.
  - If you get 'Could not open connection to the host' then this is network problem.

SQL Server:

Check to see if your SQL server hostname, username, and password is correct.
Check there is no firewall rule blocking TCP connection to port 1433.
Remove your port information and try to reconnect.
- You should specify either Instance Name or Port number for SQL Server, not both.

SQL Server Express:

Are you using a dynamic or static port?
- Remove the dynamic entry and enter the static under TCP port.
You also need to enable TCP/IP as by default it assumes a local memory connection.

Profiles.xml for Subset:

Go to C:\%AppData%\Roaming\Grid-Tools
Open your Profiles.xml file in a program like Notepad.
Verify that the server name, password, and other connection details are listed correctly throughout the file. (They will appear multiple times)
Save the file.
Re-launch Datamaker and Subset.
- If Profiles.xml was corrupted:
  - Delete profiles.xml from C:\%AppData%\roaming\Grid-Tools folder.
  - Launch GTSubset and create the new profiles for source and Repository connections.

Localhost or IP Address:

Typically, when you connect to SQL Server DB from SQL Server Management Studio or from ODBC admin, using the server name 'local' works because it is internally translated.
For connecting to a generic application, like Subset, that asks for server name, you need to enter either localhost or the IP address.
When you change the Server Name to 'localhost' or the IP Address, you will be able to connect to Subset.

Additional Information

If you are still experiencing any issues, please open a support case by going to https://support.broadcom.com