Error while running the Talend task which involves left outer join and UDF

Vandy — Fri, 15 Nov 2024 21:51:10 GMT

Hi Team,

I have built a job to take multiple tables as input from Microsoft SQL Server database and perform some operations based on filter conditions in tMap component and create multiple tables for different conditions. Perform the same by creating an UDF(User Defined Function/Routine) and call it in tMap. I am facing error while running this job.

Error is as follows:

Starting job Copy_of_Table_join at 16:57 02/05/2023.

[statistics] connecting to socket on port 4008

[statistics] connected

Exception in component tDBOutput_3 (Copy_of_Table_join)

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:272c46d0-25b6-4d60-a6ef-c97d2b6ceb32

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

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

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

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

at de_talendpractice.copy_of_table_join_0_1.Copy_of_Table_join.tDBInput_5Process(Copy_of_Table_join.java:1984)

at de_talendpractice.copy_of_table_join_0_1.Copy_of_Table_join.runJobInTOS(Copy_of_Table_join.java:3863)

at de_talendpractice.copy_of_table_join_0_1.Copy_of_Table_join.main(Copy_of_Table_join.java:3701)

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

at java.base/sun.security.ssl.Alert.createSSLException(Alert.java:130)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:371)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:314)

at java.base/sun.security.ssl.TransportContext.fatal(TransportContext.java:309)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:654)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.onCertificate(CertificateMessage.java:473)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.consume(CertificateMessage.java:369)

at java.base/sun.security.ssl.SSLHandshake.consume(SSLHandshake.java:396)

at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:480)

at java.base/sun.security.ssl.HandshakeContext.dispatch(HandshakeContext.java:458)

at java.base/sun.security.ssl.TransportContext.dispatch(TransportContext.java:201)

at java.base/sun.security.ssl.SSLTransport.decode(SSLTransport.java:172)

at java.base/sun.security.ssl.SSLSocketImpl.decode(SSLSocketImpl.java:1510)

at java.base/sun.security.ssl.SSLSocketImpl.readHandshakeRecord(SSLSocketImpl.java:1425)

at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:455)

at java.base/sun.security.ssl.SSLSocketImpl.startHandshake(SSLSocketImpl.java:426)

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

... 10 more

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

at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:388)

at java.base/sun.security.validator.PKIXValidator.engineValidate(PKIXValidator.java:271)

at java.base/sun.security.validator.Validator.validate(Validator.java:256)

at java.base/sun.security.ssl.X509TrustManagerImpl.checkTrusted(X509TrustManagerImpl.java:242)

at java.base/sun.security.ssl.X509TrustManagerImpl.checkServerTrusted(X509TrustManagerImpl.java:113)

at com.microsoft.sqlserver.jdbc.HostNameOverrideX509TrustManager.checkServerTrusted(SQLServerTrustManager.java:86)

at java.base/sun.security.ssl.AbstractTrustManagerWrapper.checkServerTrusted(SSLContextImpl.java:1441)

at java.base/sun.security.ssl.CertificateMessage$T12CertificateConsumer.checkServerCerts(CertificateMessage.java:638)

... 22 more

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

at java.base/sun.security.provider.certpath.SunCertPathBuilder.build(SunCertPathBuilder.java:141)

at java.base/sun.security.provider.certpath.SunCertPathBuilder.engineBuild(SunCertPathBuilder.java:126)

at java.base/java.security.cert.CertPathBuilder.build(CertPathBuilder.java:297)

at java.base/sun.security.validator.PKIXValidator.doBuild(PKIXValidator.java:383)

... 29 more

[statistics] disconnected

Job Copy_of_Table_join ended at 16:57 02/05/2023. [Exit code = 1]

I will attach the required screenshots, it would be of a great help if someone could guide me on the same.

Thank you in advance

Re: Error while running the Talend task which involves left outer join and UDF

Anonymous — Thu, 04 May 2023 02:54:09 GMT

Hello @Vandhana C ,

Regarding to the error log, the problem is that it can not connect to the mssql db due to ssl settings.

To fix the issue, please add the below jdbc parameters in tDBOutput_3 Advanced settings as the below

"encrypt=true;trustServerCertificate=true"

please refer to the below article

https://learn.microsoft.com/en-us/sql/connect/jdbc/connecting-with-ssl-encryption?view=sql-server-ver16

Re: Error while running the Talend task which involves left outer join and UDF

Vandy — Thu, 04 May 2023 06:08:43 GMT

Hi @achen (Talend Beijing Tech Co., Ltd.) ,

It worked!

Thank you so much for the help.

topic Re: Error while running the Talend task which involves left outer join and UDF in Talend Studio

Error while running the Talend task which involves left outer join and UDF

Re: Error while running the Talend task which involves left outer join and UDF

Re: Error while running the Talend task which involves left outer join and UDF