Err Connecting SQL server which has encryption enabled.

One Star SJJ
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
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.
One Star SJJ
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
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.
One Star SJJ
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
One Star SJJ
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
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.
One Star

Re: Err Connecting SQL server which has encryption enabled.

it looks helpful

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER 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

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

6 Ways to Start Utilizing Machine Learning with Amazon We Services and Talend

Look at6 ways to start utilizing Machine Learning with Amazon We Services and Talend

Blog