[resolved] remote MS SQL 2008 R2 connection timeout

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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

[Anonymous]

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