Issue connecting SQL Server from Talend Data Integ... - Qlik Community

AVasavada1623202633 · ‎2021-06-08

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)

gjeremy1617088143 · ‎2021-06-09

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

AVasavada1623202633 · ‎2021-06-09

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?

gjeremy1617088143 · ‎2021-06-09

are you using jtds or jdbc ?

AVasavada1623202633 · ‎2021-06-09

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

Prakhar1 · ‎2021-06-09

Add

-Djdk.tls.client.protocols=TLSv1

In Jvm parameters

AVasavada1623202633 · ‎2021-06-09

@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?

AVasavada1623202633 · ‎2021-06-09

@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?

Prakhar1 · ‎2021-06-10

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.

Prakhar1 · ‎2021-06-10

try to add

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

this as well in jvm parameter

Issue connecting SQL Server from Talend Data Integration Open Studio

SQL

Studio

Talend Data Integration

v7.x