Err Connecting SQL server which has encryption enabled.

Highlighted
One Star
One Star

Err Connecting SQL server which has encryption enabled.

Hi there,
I am trying to connect to SQL server using jdbc:jtds string. It was working fine before but recently admin has enabled forceencryption on SQL server and talend job failed to connect. I tried adding ssl=require; as additional parameters but it doesn't seems to help much. See Below:

And this is the error i am getting:

Any thing in this regard will be a great help.
Appreciate
Sojja
Highlighted
Moderator

Re: Err Connecting SQL server which has encryption enabled.

Hi,
We can't see the screenshot on our side. Could you please check it?
Make sure the screenshot file is not bigger than 2MB.

In addition, can you connect to your SQL server  successfully after it has encryption through client without using talend tool?

Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
One Star
One Star

Re: Err Connecting SQL server which has encryption enabled.

Hi,
Thanks for the quick response.
And yes, i am able to connect to SQL server from management studio and also from other applications(.net). But it just the talend job where i am facing this problem.
In between my attachment(even if less than 2MB) does not seems to work in here. So i am just typing the string and the error below:
Connection String: 
jdbc:jtds:sqlserver://ServerIP:1433/DBNAME;ssl=require;
Error: 
Connection failure. You must change the Database Settings.
  java.lang.RuntimeException: java.sql.SQLException: I/O Error: Socket closed
      at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:177)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1047)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:301)
      at org.talend.repository.ui.utils.ManagerConnection.check(ManagerConnection.java:261)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm.checkConnection(DatabaseForm.java:2096)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm.access$41(DatabaseForm.java:2021)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$24.widgetSelected(DatabaseForm.java:2349)
      at org.eclipse.swt.widgets.TypedListener.handleEvent(TypedListener.java:234)
      at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
      at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
      at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4066)
      at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3657)
      at org.eclipse.jface.window.Window.runEventLoop(Window.java:825)
      at org.eclipse.jface.window.Window.open(Window.java:801)
      at org.talend.repository.ui.actions.metadata.CreateConnectionAction.doRun(CreateConnectionAction.java:207)
      at org.talend.repository.ui.actions.AContextualAction$2.run(AContextualAction.java:617)
      at org.talend.repository.RepositoryWorkUnit.executeRun(RepositoryWorkUnit.java:93)
      at org.talend.core.repository.model.AbstractRepositoryFactory.executeRepositoryWorkUnit(AbstractRepositoryFactory.java:237)
      at org.talend.repository.localprovider.model.LocalRepositoryFactory.executeRepositoryWorkUnit(LocalRepositoryFactory.java:3212)
      at org.talend.core.repository.model.ProxyRepositoryFactory.executeRepositoryWorkUnit(ProxyRepositoryFactory.java:1979)
      at org.talend.repository.ui.actions.AContextualAction.run(AContextualAction.java:626)
      at org.eclipse.jface.action.Action.runWithEvent(Action.java:498)
      at org.eclipse.jface.action.ActionContributionItem.handleWidgetSelection(ActionContributionItem.java:584)
      at org.eclipse.jface.action.ActionContributionItem.access$2(ActionContributionItem.java:501)
      at org.eclipse.jface.action.ActionContributionItem$5.handleEvent(ActionContributionItem.java:411)
      at org.eclipse.swt.widgets.EventTable.sendEvent(EventTable.java:84)
      at org.eclipse.swt.widgets.Widget.sendEvent(Widget.java:1053)
      at org.eclipse.swt.widgets.Display.runDeferredEvents(Display.java:4066)
      at org.eclipse.swt.widgets.Display.readAndDispatch(Display.java:3657)
      at org.eclipse.ui.internal.Workbench.runEventLoop(Workbench.java:2640)
      at org.eclipse.ui.internal.Workbench.runUI(Workbench.java:2604)
      at org.eclipse.ui.internal.Workbench.access$4(Workbench.java:2438)
      at org.eclipse.ui.internal.Workbench$7.run(Workbench.java:671)
      at org.eclipse.core.databinding.observable.Realm.runWithDefault(Realm.java:332)
      at org.eclipse.ui.internal.Workbench.createAndRunWorkbench(Workbench.java:664)
      at org.eclipse.ui.PlatformUI.createAndRunWorkbench(PlatformUI.java:149)
      at org.talend.rcp.intro.Application.start(Application.java:159)
      at org.eclipse.equinox.internal.app.EclipseAppHandle.run(EclipseAppHandle.java:196)
      at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.runApplication(EclipseAppLauncher.java:110)
      at org.eclipse.core.runtime.internal.adaptor.EclipseAppLauncher.start(EclipseAppLauncher.java:79)
      at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:369)
      at org.eclipse.core.runtime.adaptor.EclipseStarter.run(EclipseStarter.java:179)
      at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
      at sun.reflect.NativeMethodAccessorImpl.invoke(Unknown Source)
      at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
      at java.lang.reflect.Method.invoke(Unknown Source)
      at org.eclipse.equinox.launcher.Main.invokeFramework(Main.java:619)
      at org.eclipse.equinox.launcher.Main.basicRun(Main.java:574)
      at org.eclipse.equinox.launcher.Main.run(Main.java:1407)
      at org.eclipse.equinox.launcher.Main.main(Main.java:1383)
  Caused by: java.sql.SQLException: I/O Error: Socket closed
      at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1053)
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:465)
      at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1304)
      at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:390)
      at net.sourceforge.jtds.jdbc.ConnectionJDBC3.<init>(ConnectionJDBC3.java:50)
      at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184)
      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:168)
      ... 49 more
  Caused by: java.net.SocketException: Socket closed
      at java.net.SocketInputStream.socketRead0(Native Method)
      at java.net.SocketInputStream.read(Unknown Source)
      at java.net.SocketInputStream.read(Unknown Source)
      at net.sourceforge.jtds.ssl.TdsTlsInputStream.read(TdsTlsInputStream.java:72)
      at sun.security.ssl.InputRecord.readFully(Unknown Source)
      at sun.security.ssl.InputRecord.read(Unknown Source)
      at sun.security.ssl.SSLSocketImpl.readRecord(Unknown Source)
      at sun.security.ssl.SSLSocketImpl.readDataRecord(Unknown Source)
      at sun.security.ssl.AppInputStream.read(Unknown Source)
      at java.io.DataInputStream.readFully(Unknown Source)
      at java.io.DataInputStream.readFully(Unknown Source)
      at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:846)
      at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:727)
      at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:466)
      at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:103)
      at net.sourceforge.jtds.jdbc.ResponseStream.peek(ResponseStream.java:88)
      at net.sourceforge.jtds.jdbc.TdsCore.wait(TdsCore.java:3932)
      at net.sourceforge.jtds.jdbc.TdsCore.executeSQL(TdsCore.java:1046)
      ... 56 more
  
Appreciate,
Sojja
Highlighted
Moderator

Re: Err Connecting SQL server which has encryption enabled.

Hi,
Could you please try to add "encrypt=true;trustServerCertificate=true;ssl=require" into JDBC parameter to see if it works?
Best regards
Sabrina
--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.
Highlighted
One Star
One Star

Re: Err Connecting SQL server which has encryption enabled.

Hi,
I tried "encrypt=true;trustServerCertificate=true;ssl=require" , "encrypt=true;trustServerCertificate=true;ssl=request" ,"encrypt=true","trustServerCertificate=true"and "ssl=request". But Nothing seems to make that connection work. Everytime i am getting same error "Socket closed"
Is there anything else i am missing in here.
Thanks,
S
Highlighted
One Star
One Star

Re: Err Connecting SQL server which has encryption enabled.

Hi,
Is it something to do with TCP/IP protocol? No luck in this yet. Any help will be great.
Appreciate.
S
Highlighted
One Star

Re: Err Connecting SQL server which has encryption enabled.

hi SJJ!
Did you solved this issue?
I've the same issue.
I got connection via SQL Workbench/J () + JTDS driver, so, I can affirm that JDBC driver and/or remote connection works fine.
Highlighted
One Star

Re: Err Connecting SQL server which has encryption enabled.

it looks helpful

2019 GARTNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

Best Practices for Using Context Variables with Talend – Part 2

Part 2 of a series on Context Variables

Blog

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog