CA TDM Portal unable to start masking - due to SQL server named instance PORT issue

Products

• CA Test Data Manager (Data Finder / Grid Tools)

Issue/Introduction

We tried to start masking of our schemas today and immediately tool throws this error.

 Connection error: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the hosthostname.domain.com, named instance SQL01 failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host. Seed Data Database service has not started, Seed Data Database service has not started

 

In the connection profile, the repository connection to SCRAMBLE has the system defined PORT 10001.

I have spoken with our DBAs and they told me that due to org policy, they can’t use default port.

On the host (CA TDM server), I don’t see any services that refers to SQL Server.

Can someone help us on this?

Environment

TDM Portal 4.9
FDM 4.9

Component : CA Test Data Manager

Cause

TDM sports named instance on the default port only.
You can change the connection to use IP address or FQDN and port and it will work fine.

Resolution

We can see the following in the logs:

2020-12-09 18:16:03.857 UTC [INFO ] [FdmProgressNotifier ] --- [U:][M:][P:] c.c.t.f.s.FdmProgressNotifier: run: job: 1070: Sent final notification: state: RUNNING errors: (1) vSOT01: Connection error: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host hostnamedomain.com, named instance SQL01 failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host., Seed Data Database service has not started, Seed Data Database service has not started
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: logFDMOutputs: copying fdm console outputs to masking log...
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: logFDMOutputs: -------------------------------------------------------------------------------
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: logFDMOutputs: fdm#: 0 schema: SOT01 csv:SOT01.csv
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: logFDMOutputs: -------------------------------------------------------------------------------
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.440 Fastdatamasker version: 4.9.44.0
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.471 Fastdatamasker build date - July 23 2020
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.503 PID:[email protected]
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.503 Java version 1.8.0_212
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.503 OPERATING SYSTEM USER: USERNAME
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.518 Skipping cloakware configuration
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.534 attempting to connect
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.534 Using URL:jdbc:oracle:thin:@//Hostname:1525/OPSHD
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:47.847 Connecting with driver: oracle.jdbc.driver.OracleDriver using URL: jdbc:oracle:thin:@//hostname:1525/OPSHD
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:48.862 Connected to jdbc:oracle:thin:@//hostname:1525/OPSHD username:sot01
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:48.862 Driver Major Version:19
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:48.862 Driver Minor Version:3
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:49.128 attempting to connect
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:49.128 Using URL:jdbc:sqlserver://hostname.domain.com;instanceName=SQL01;DatabaseName=scramble
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:50.315 #FDM_METRICS{"jobState":"RUNNING","totalRows":"0","rowsMasked":"0","errors":""}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:53.316 #FDM_METRICS{"jobState":"RUNNING","totalRows":"0","rowsMasked":"0","errors":""}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:56.331 #FDM_METRICS{"jobState":"RUNNING","totalRows":"0","rowsMasked":"0","errors":""}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:15:59.338 #FDM_METRICS{"jobState":"RUNNING","totalRows":"0","rowsMasked":"0","errors":""}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:02.341 #FDM_METRICS{"jobState":"RUNNING","totalRows":"0","rowsMasked":"0","errors":""}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:03.795 Seed Data Database service has not started
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:03.795 close: closing down. Final job state: FAILED
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:03.795 run: FDM progress thread: Sending final notification: state: FAILED
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:03.795 #FDM_METRICS{"jobState":"FAILED","totalRows":"0","rowsMasked":"0","errors":"Connection error: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host hostname.domain.com, named instance SQL01 failed. Error: \"java.net.SocketTimeoutException: Receive timed out\". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host., Seed Data Database service has not started, Seed Data Database service has not started"}#END_FDM_METRICS
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: #0: 2020-12-09 12:16:03.795 exit value = 1
2020-12-09 18:16:03.857 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: logFDMOutputs: -------------------------------------------------------------------------------
2020-12-09 18:16:03.873 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: runFDM: job: 1070: Final status of fdm runs:
2020-12-09 18:16:03.873 UTC [INFO ] [SimpleAsyncTaskExecutor-2] --- [U:][M:][P:] c.c.t.f.s.CallFastDataMasker: runFDM: job: 1070: fdm#: 0 state: FAILED schema: SOT01 errors: (1) SOT01: Connection error: com.microsoft.sqlserver.jdbc.SQLServerException: The connection to the host hostname.domain.com, named instance SQL01 failed. Error: "java.net.SocketTimeoutException: Receive timed out". Verify the server and instance names and check that no firewall is blocking UDP traffic to port 1434. For SQL Server 2005 or later, verify that the SQL Server Browser Service is running on the host., Seed Data Database service has not started, Seed Data Database service has not started



when using an instance name we expect the default port to be used of 1433.
This is why you are getting the error.

Please remove the instance name and change the connection string to use hostname and port with no instance name.

An example of this would be:

URL:jdbc:sqlserver://hostname.domain.com:10001;DatabaseName=scramble


this will connect to the same server and instance based on the TCP port provided.