Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
BartG
Contributor
Contributor
‎2023-02-15 08:22 AM

Exceptions in tDBConnection to SQL Server when externally running jar

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

Labels (5)
Labels
907 Views
0 Likes
4 Replies
Anonymous
Not applicable
‎2023-02-15 08:54 PM

Hi

​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

 

 

907 Views
0 Likes
BartG
Contributor
Contributor
‎2023-02-16 04:27 AM
Author

In response to Anonymous

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

       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: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)

 

So it unfortunately seems worse: it doesn't even run inside TOS anymore 😕

 

907 Views
0 Likes
Anonymous
Not applicable
‎2023-02-16 06:53 AM

In response to BartG

if ​IntegratedSecurity=false, it will use SQL Server authentication, you need to use standard SQL Server username/password, are you able to login SQL Server management studio with login account?

 

 

907 Views
0 Likes
BartG
Contributor
Contributor
‎2023-02-20 07:25 AM
Author

In response to BartG

Hi, yes I just used my own account and entered the username and password. This account works with integrated security because it's the account that I use on my own system. I also use this account in SQL Server Management Studio to manage all databases and to development, so this account certainly has access to the SQL Server.

907 Views
0 Likes