Test Data Manager

You can see this error message 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 I am setting up a connection profile for my MS SQL Server Database in Datamaker and testing the connection, I am receiving 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:

I am trying to use a local SQL Server for setting up the required connection profile in TDM Portal. I am able to connect to this SQL Server using SQL Management Studio, as well as through Datamaker. When I try to setup the connection profile in TDM Portal, I am getting 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 I try to use the Repository with Subset, I get 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. 

Steps to reproduce:  
1. Launch Datamaker. 
2. Connect to gtrep repository. 
3. Test and Verify connection: 
- Profile: gtrep 
- DBMS: ODBC 
- Use specified login details 
- Store password 
- Default Schema: gtrep 
- The connection details are valid. 
4. Login to Datamaker with a source and target: 
- Travel and Travel_E (from training) 
5. Open Project Manager. 
- Verify it shows repository information. 
6. Navigate to source SQL Window. 
7. Run select * for PEOPLE 
- This verifies there are no database or TCP problems from Datamaker. 
8. Click on 'Data Subset' in toolbar. 
9. Choose 'Designer Extracts and Transactions' from list. 
- While Subset is connecting, I receive the following error message: 
'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.".' 
10. Click 'OK' on the error message. 
- I receive the next message: Unable to connect to GTSubset repository profile gtrep. Please edit this profile in GTSubset. 
11. Launch GTSubset as standalone. 
12. Choose the gtrep (repository) connection profile. 
13. Enter the connection details: 
- Connection Name: gtrep 
- Username: admin 
- Password: xxxx 
- DBMS: MS SQL Server 
- Server: local 
- Port No: 1433 
- Database: gtrep 
- Default User: dbo 
14. Click Save. 
15. Click Connect. 
- Receive error message again.

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. 

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

Enable TCP/IP:
1. Go to Start 
2. All Programs 
3. Microsoft SQL Server  
4. Configuration Tool 
5. Click SQL Server Configuration Manager 
6. Expand SQL Server Network Configuration 
7. Protocol 
8. Enable TCP/IP Right box 
9. Double Click on TCP/IP 
10. 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 above state can stop though TCP connection to port 1433 is assigned. 

Enable TCP/IP (Listen All):

1. Open SQL Server Configuration Manager, and then expand SQL Server Network Configuration. 
2. Click Protocols for InstanceName, and then make sure TCP/IP is enabled in the right panel and double-click TCP/IP. 
3. On the Protocol tab, notice the value of the Listen All item. 
4. 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. 
5. Make sure the TCP Port is 1433. 
6. 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 able to connect to Subset.