Issue:
A pipeline that has a JDBC multi-table consumer origin hits the following error when tried to run,
JDBC_00 - Cannot connect to specified database: com.streamsets.pipeline.api.StageException: JDBC_06 - Failed to initialize connection pool: com.zaxxer.hikari.pool.PoolInitializationException: Exception during pool initialization: The connection to the host 10.233.42.79, named instance wamssql02 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.
Is there a way to switch the pipeline to use only TCP connections instead?
Solution:
Data Collector depends on the services provided by the JDBC driver. It does not control the connection at a lower level, That is the JDBC driver's job. You should be able to get it to work by embedding the instance's port number in the JDBC connection string.
The following article delves into more details related to the connection logic for the JDBC driver:
If UDP port 1434 is disabled, the SQL Server client cannot dynamically determine the port of the named instance of SQL Server. Therefore, the SQL Server client may be unable to connect to the named instance of SQL Server. In this situation, the SQL Server client must specify the dynamically allocated port where the named instance of SQL Server 2008, SQL Server 2005, or SQL Server 2000 is listening.
So, the UDP port seems to be used for the initial discovery of the instance's port.
This information (although for SQL Server 2017), suggests that we can embed the instance's port in the JDBC connection string and will cause the UDP logic to be skipped.
Also, note that embedding the instance's port number might be considered a best practice based on this note:
For optimal connection performance, you should set the portNumber when you connect to a named instance. This will avoid a round trip to the server to determine the port number. If both a portNumber and instanceName are used, the portNumber will take precedence and the instanceName will be ignored.
