topic Re: [resolved] remote MS SQL 2008 R2 connection timeout in Talend Studio

[resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Wed, 20 Jan 2016 10:39:58 GMT

Hello,
I'm struggling with connection to MS SQL server 2008 R2 on a remote server...
Indeed, I'm always getting a timeout error ( java.lang.RuntimeException: java.sql.SQLException: Login timed out.).

TCP/IP is enabled on the server.
I use a SQL server login.
I can connect to this remote SQL server with SQL Server Management Studio.
I can connect to a local SQL Server express 2012 on my laptop with TOS without any issue.
I use TOS 6.1.0
Any idea of what's going wrong?
Many thanks in advance 🙂
Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Thu, 21 Jan 2016 10:33:07 GMT

Hello,
Some news regarding this issue:
I've re-created the connection error with my local SQL Server:
If I set the real IP address or MyComputerName.MyDomain as server address, I get a timeout error.
If I set 127.0.0.1 or localhost as server address, connection is established...
Any idea?
Thanks,
Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Fri, 22 Jan 2016 03:12:45 GMT

Hi,
Your network is working well for you? Have you set up MSSQL DB connection in metadata? Is there any more error message when go to "details.."?
Best regards
Sabrina

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Fri, 22 Jan 2016 11:40:49 GMT

Hi Sabrina,
Thank you for your answer 🙂
Network is fine for me, and MSSQL DB connection is set up in metadata.
Here is the complete error message I get:
java.lang.RuntimeException: java.sql.SQLException: Login timed out.
      at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:195)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1089)
      at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:314)
      at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:272)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$31.runWithCancel(DatabaseForm.java:2674)
      at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$31.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: java.sql.SQLException: Login timed out.
      at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:433)
      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:186)
      ... 9 more
Caused by: java.net.SocketTimeoutException: Connect timed out
      at java.net.SocksSocketImpl.readSocksReply(Unknown Source)
      at java.net.SocksSocketImpl.connect(Unknown Source)
      at java.net.Socket.connect(Unknown Source)
      at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288)
      at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)
      at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331)
      ... 12 more

Thank you,
Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Mon, 25 Jan 2016 03:04:21 GMT

Hi,
Are you using sql server authentication and windows authentication? Would you mind posting your DB connection in metadata screenshot into forum? What does your string of connection look like? Does this issue repro on other build version?
Best regards
Sabrina

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Mon, 25 Jan 2016 15:36:42 GMT

Hello,
I'm using SQL Server identification.
Here is a screenshot of the DB connection in metadata:

The connection string look like:
jdbc:jtds:sqlserver://IP_Address_SQL_server:1433/Database_Name;Instance_Name
I don't know if this issue reproduces on another build version, I'm only using TOS 6.1.0...
Thank you,
Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Tue, 26 Jan 2016 03:34:16 GMT

Hi,
We cannot repro this issue on V 6.1.0 on our end. Is your remote sqlserver running?
Best regards
Sabrina

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Tue, 26 Jan 2016 15:36:27 GMT

Hello,
Indeed, our remote SQL Server is running, I can access it through SQL Server Management Studio.
I've asked our Administrator to activate SQL Server Browser, it seems this might be required to allow jtds to connect when instances are set up on SQL Server.
I'll let you know the results of this test.
Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Wed, 27 Jan 2016 03:17:28 GMT

Hi,

I've asked our Administrator to activate SQL Server Browser, it seems this might be required to allow jtds to connect when instances are set up on SQL Server.

Let us know if it doesn't work for you.
Best regards
Sabrina

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Thu, 11 Feb 2016 06:50:47 GMT

Hello,
We've finally found what was causing this issue!
No TCP port was specified for the instance of SQL server I'm trying to connect!
Now I can retrieve some data from my remote SQL Server with tMSSQinput, but I still get a "timeout" error when testing the connection in Metadata... No matter, now I can do what I need; if I can fix the Metadata issue, I'll share the result with you (but no hurry from my side:-))!

So maybe it's wise to list the SQL server settings to allow connection with Talend:
In SQL Server Management Studio, go to "properties" of your instance

In "security", select "SQL Server and Windows authentification mode"
In connections, select "allow remote connections"

In SQL Server Configuration Manager

In SQL Server services, activate SQL Serve Browser
In SQL Server Native Client configuration, activate TCP/IP, and put it in the higer position (before named pipes)
In Network configuration for SQL Server, select your instance and activate TCP/IP; in TCP/IP properties, scroll down to IP All, and check if a TCP Port is specified; if a "0" value is set in "dynamics TCP ports", remove it.

Hope this will help the community,

Sylvain

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Fri, 11 Mar 2016 15:15:51 GMT

Hello,
We seem to get the same problem in talend 6.1.1 :
Echec de la connexion Vous devez modifier les paramètres de la base de données.
java.lang.RuntimeException: java.sql.SQLException: Login timed out.
at org.talend.core.model.metadata.builder.database.JDBCDriverLoader.getConnection(JDBCDriverLoader.java:195)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataUtils.connect(ExtractMetaDataUtils.java:1089)
at org.talend.core.model.metadata.builder.database.ExtractMetaDataFromDataBase.testConnection(ExtractMetaDataFromDataBase.java:314)
at org.talend.metadata.managment.repository.ManagerConnection.check(ManagerConnection.java:272)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$31.runWithCancel(DatabaseForm.java:2674)
at org.talend.repository.ui.wizards.metadata.connection.database.DatabaseForm$31.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: java.sql.SQLException: Login timed out.
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:433)
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:186)
... 9 more
Caused by: java.net.SocketTimeoutException: Connect timed out
at java.net.SocksSocketImpl.readSocksReply(Unknown Source)
at java.net.SocksSocketImpl.connect(Unknown Source)
at java.net.Socket.connect(Unknown Source)
at net.sourceforge.jtds.jdbc.SharedSocket.createSocketForJDBC3(SharedSocket.java:288)
at net.sourceforge.jtds.jdbc.SharedSocket.<init>(SharedSocket.java:251)
at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:331)
... 12 more
My jobs work but we can't check the connection or retrieve table schemas from metadata (we also get the problem with AS400 connections).
do you have any issues about it ?
Thanks for your help,
Regards,
Rudy

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Thu, 17 Mar 2016 08:39:32 GMT

Hi Rudy,
Are you able to connect to the SQL Server database successfully via client without using Talend Tool?
Is there any connectivity or firewall issue for you?
Best regards
Sabrina

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Wed, 30 Mar 2016 15:40:22 GMT

Hi,
Thanks for your reply,
I can connect with SQL Server Management Studio, There's only an error when looking for schemas.
Regards
Rudy

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Wed, 30 Mar 2016 16:22:20 GMT

I 've just found how to solve my problem : I delete the proxy settings for SOCKS entry in preferences>>General>> Network Connections
hope this help
Rudy

Re: [resolved] remote MS SQL 2008 R2 connection timeout

Anonymous — Thu, 31 Mar 2016 09:03:15 GMT

Hi Moun74,
Thanks for your feedback and share your solution with us.
Best regards
Sabrina