topic Re: Exceptions in tDBConnection to SQL Server when externally running jar in Talend Studio

Exceptions in tDBConnection to SQL Server when externally running jar

BartG — Fri, 15 Nov 2024 22:05:46 GMT

Hi,

I've been working in Talend Open Studio for a while now, designing a few jobs. They run just fine.

Now I wanted to export them so they can run independently on a server triggered by a scheduled task. I right-click a job, choose "Build Job", and it generates a zip with jar and batch file etc. When I try to run the batch file, I always seem to get Java exceptions that are related to the database connection. I don't have those issues in TOS itself.

I've tried many suggestions that I found here or elsewhere, like putting the ntlmauth.dll everywhere etc, but I can't get past these exceptions.

When I configure my tDBConnection (MS SQL Server) to "Open source JTDS", I leave username and password empty, and I add "IntegratedSecurity=true" to the "Additional JDBC Parameters". It works in TOS. But when I build the job and execute the batch file, I get this:

Exception in component tDBOutput_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

java.lang.NullPointerException

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tWaitForFile_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:2367)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:8200)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:7991)

When I configure my tDBConnection (MS SQL Server) to "Microsoft", I fill in a username and password, and I leave the "Additional JDBC Parameters" empty. It works in TOS. But when I build the job and execute the batch file, I get this:

Exception in component tDBOutput_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

java.lang.NullPointerException

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tWaitForFile_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:2367)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:8200)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:7991)

Exception in component tDBConnection_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:73a555e9-eff5-43c5-9c7a-ca7baed47bef

at com.microsoft.sqlserver.jdbc.SQLServerConnection.terminate(SQLServerConnection.java:3806)

at com.microsoft.sqlserver.jdbc.TDSChannel.enableSSL(IOBuffer.java:1907)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3329)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tDBConnection_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:6765)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:8218)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:7991)

Caused by: javax.net.ssl.SSLHandshakeException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

...

Caused by: sun.security.validator.ValidatorException: PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

...

Caused by: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

...

I use the latest TOS 8.0.1. I deleted and reinstalled TOS. I use the jTDS from https://sourceforge.net/projects/jtds/ and put the ntlmauth.dll in several folders (studio, system32). I use the "zulu11.62.17-ca-jdk11.0.18-win_x64" as recommended by Talend docs.

What can I do to get past these exceptions? This job needs to run on a server in a reliable way and not fall apart unexpectedly. I should be able to have control over the components and versions it needs to run properly.

Thanks

Re: Exceptions in tDBConnection to SQL Server when externally running jar

Anonymous — Thu, 16 Feb 2023 01:54:50 GMT

IntegratedSecurity=true means to use windows authentication, do you run the job batch on the same windows machine? NullPointerException is always difficult to judge the cause from the error message, sometimes we need to run the Job code in debug mode to find the cause.

When using Microsoft driver, fill in the username and password, add the following parameters to Additional JDBC Parameters field.

integratedSecurity=false;encrypt=true;trustServerCertificate=true

can you try and let me know if it fixes the issue?

Regards

Shong

Re: Exceptions in tDBConnection to SQL Server when externally running jar

BartG — Thu, 16 Feb 2023 09:27:01 GMT

Hi Shong,

Thanks for the feedback.

All designing and tests are run on the same machine so far.

With the Microsoft driver, and adding your parameters "integratedSecurity=false;encrypt=true;trustServerCertificate=true" it gives me the following errors when running the job inside TOS:

Starting job Split_FPT_input_to_FLx_output_ViaSQL_edit at 10:16 16/02/2023.

[statistics] connecting to socket on port 3534

[statistics] connected

Exception in component tDBConnection_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<removed>’. ClientConnectionId:751d7c42-5072-475e-93c7-5e99bb4f7cbe

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:265)

at com.microsoft.sqlserver.jdbc.TDSTokenHandler.onEOF(tdsparser.java:300)

at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:133)

at com.microsoft.sqlserver.jdbc.TDSParser.parse(tdsparser.java:37)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.sendLogon(SQLServerConnection.java:6299)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.logon(SQLServerConnection.java:4911)

at com.microsoft.sqlserver.jdbc.SQLServerConnection$LogonCommand.doExecute(SQLServerConnection.java:4849)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:7620)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:3916)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:3358)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:2950)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectInternal(SQLServerConnection.java:2790)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:1663)

at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1064)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)

at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tDBConnection_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:733)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:6320)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:6112)

Exception in component tDBOutput_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

java.lang.NullPointerException

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tWaitForFile_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:3232)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:6344)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:6112)

[statistics] disconnected

Job Split_FPT_input_to_FLx_output_ViaSQL_edit ended at 10:16 16/02/2023. [Exit code = 1]

When I build the job (still with you parameters) and run the batch file, it gives the following errors:

C:\Users\<removed>\Desktop\Split_FPT_input_to_FLx_output_ViaSQL_edit>java -Dtalend.component.manager.m2.repository="C:\Users\<removed> \Desktop\Split_FPT_input_to_FLx_output_ViaSQL_edit/../lib" -Xms256M -Xmx1024M -cp .;../lib/routines.jar;../lib/log4j-slf4j-impl-2.13.2.jar;../lib/log4j-api-2.13.2.jar;../lib/log4j-core-2.13.2.jar;../lib/filecopy-2.0.3.jar;../lib/jboss-marshalling-2.0.12.Final.jar;../lib/dom4j-2.1.3.jar;../lib/talend_file_enhanced-1.1.jar;../lib/slf4j-api-1.7.29.jar;../lib/mssql-jdbc.jar;../lib/talendcsv-1.0.0.jar;../lib/crypto-utils-0.31.12.jar;split_fpt_input_to_flx_output_viasql_edit_0_1.jar; flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit --context=Default

Exception in component tDBOutput_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

java.lang.NullPointerException

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.tWaitForFile_1Process(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:2367)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.runJobInTOS(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:8200)

at flx_mapping.split_fpt_input_to_flx_output_viasql_edit_0_1.Split_FPT_input_to_FLx_output_ViaSQL_edit.main(Split_FPT_input_to_FLx_output_ViaSQL_edit.java:7991)

Exception in component tDBConnection_1 (Split_FPT_input_to_FLx_output_ViaSQL_edit)

com.microsoft.sqlserver.jdbc.SQLServerException: Login failed for user '<removed>’. ClientConnectionId:e258510d-c6c3-4e71-8eac-7011f2bef584