topic Re: Issue connecting SQL Server from Talend Data Integration Open Studio in Talend Studio

Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Sat, 16 Nov 2024 00:04:37 GMT

Hi - I am trying to setup a connection to Microsoft SQL Server from Talend Open Studio for Data Integration but it is failing with the below error. It seems like we need to change some configuration in java to allow TLS1.1 and TLS1.2 protocols. Does anyone know where to configure that? Any help or pointers is appreciated. Thanks in advance.

Connection failure. You must change the Database Settings.

java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException:

The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "The server selected protocol version TLS11 is not accepted by client preferences [TLS12]". ClientConnectionId:5addf972-b218-40ef-8d53-497d1571def0

at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:208)

at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1146)

at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:315)

at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:290)

at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:4208)

at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$67.runWithCancel(DatabaseForm.java:1)

at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$1.runnableWithCancel(AProgressMonitorDialogWithCancel.java:77)

at org.talend.repository.ui.dialog.AProgressMonitorDialogWithCancel$ARunnableWithProgressCancel$1.call(AProgressMonitorDialogWithCancel.java:161)

at java.util.concurrent.FutureTask.run(Unknown Source)

at java.lang.Thread.run(Unknown Source)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "The server selected protocol version TLS11 is not accepted by client preferences [TLS12]". ClientConnectionId:5addf972-b218-40ef-8d53-497d1571def0

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

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

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

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

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

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

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

at org.talend.core.model.metadata.builder.database.DriverShim.connect(DriverShim.java:41)

at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:199)

... 9 more

Caused by: javax.net.ssl.SSLHandshakeException: The server selected protocol version TLS11 is not accepted by client preferences [TLS12]

at sun.security.ssl.Alert.createSSLException(Unknown Source)

at sun.security.ssl.TransportContext.fatal(Unknown Source)

at sun.security.ssl.ServerHello$ServerHelloConsumer.onServerHello(Unknown Source)

at sun.security.ssl.ServerHello$ServerHelloConsumer.consume(Unknown Source)

at sun.security.ssl.SSLHandshake.consume(Unknown Source)

at sun.security.ssl.HandshakeContext.dispatch(Unknown Source)

at sun.security.ssl.TransportContext.dispatch(Unknown Source)

at sun.security.ssl.SSLTransport.decode(Unknown Source)

at sun.security.ssl.SSLSocketImpl.decode(Unknown Source)

at sun.security.ssl.SSLSocketImpl.readHandshakeRecord(Unknown Source)

at sun.security.ssl.SSLSocketImpl.startHandshake(Unknown Source)

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

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

gjeremy1617088143 — Wed, 09 Jun 2021 06:49:58 GMT

Hi, maybe you can add : "TLSProtocol=TLSv1.1" in additionnal JDBC paremeter field of the connection component.

By default it's 1.2

Send me Love and Kudos

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Wed, 09 Jun 2021 14:55:34 GMT

Hi @guenneguez jeremy - I tried with multiple combinations but still not working. Below are the combinations I tried:

enableTLS=true

sslProtocol=TLSv1

sslProtocol=TLSv1.1

sslProtocol=TLSv1.2

TLSProtocol=TLSv1

TLSProtocol=TLSv1.1

TLSProtocol=TLSv1.2

We are using windows authentication so I also added integratedSecurity=true parameter.

Any other thoughts?

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

gjeremy1617088143 — Wed, 09 Jun 2021 14:58:12 GMT

are you using jtds or jdbc ?

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Wed, 09 Jun 2021 15:00:14 GMT

jdbc. I selected "Microsoft" in the DB Version.

Below is my connection string:

jdbc:sqlserver://servername;instancename=XXXX;integratedSecurity=true;DatabaseName=XXX;enableTLS=true;sslProtocol=TLSv1

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

Prakhar1 — Wed, 09 Jun 2021 15:48:33 GMT

Add

-Djdk.tls.client.protocols=TLSv1

In Jvm parameters

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Wed, 09 Jun 2021 15:56:01 GMT

@prakhar dubey - Can you please guide me when can I add/update the jvm paramters on Talend? Or is it something you do it at the java level?

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Wed, 09 Jun 2021 16:27:24 GMT

@prakhar dubey - I think I figured where to add it. I am on windows and I edited the file named : TOS_DI-win-x86_64. I added the entry you recommended in the end and restarted Talend but it didnt work. I tried with TLSv1.1 and TLSv1.2 but no luck. Anything else you suggest I do?

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

Prakhar1 — Thu, 10 Jun 2021 06:10:12 GMT

Goto Run tab in talend studio

then in advance setting , select the "Use JVM arguments"

and then click on new and paste this line.

This is how you add a JVM parameter.

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

Prakhar1 — Thu, 10 Jun 2021 06:16:54 GMT

try to add

-Djavax.net.debug=ssl:handshake:verbose

this as well in jvm parameter

Re: Issue connecting SQL Server from Talend Data Integration Open Studio

AVasavada1623202633 — Fri, 11 Jun 2021 00:42:17 GMT

@prakhar dubey - I tried to add jvm parameter as you suggested but I am not even able to run the job because of compilation errors. The tMSSQLInput component needs a table or a view to pull data from and without connecting to the database, it wont work.