I'm out of ideas on how to get the tMSSqlConnection to connect to my SQL Server 2008 R2 database. This seems like it should be easy but for me it's been a humbling exercise in futility. Sure hope some can point out something I've overlooked.
Let me start with the typical error message:
Exception in component tMSSqlConnection_1
java.sql.SQLException: Network error IOException: Connection refused: connect
at net.sourceforge.jtds.jdbc.ConnectionJDBC2.<init>(ConnectionJDBC2.java:410)
TOS version: 5.0.1
Component basic config: Host: "127.0.0.1", Port: "1433", Schema: "dbo", Database: "QHR", Username: "MyUsername" Password: "MyPassword"
SQL Server 2008 R2: Windows Authentication, IP addresses enabled, Dynamic port, Remote connections allowed
Variations I've tried:
Host: "SQLR2" (the instance name), "(local)", "(localhost)", "192.168.10.20" (LAN IP address), "Servername\\SQLR2".
Port: (a different port number that was matched with a static port number on SQL Server side.)
Schema: (out of desperation tried "", but nothing else)
Username: tried blank usernames, tried blank username after adding NTLMAUTH.DLL (from jTDS 1.2.5) for Windows Authentication / SSO
Password: (same as for Username above)
Microsoft Firewall: enabled & disabled
I have also tried connect with named pipes after enabling this on SQL Server. I get the following error:
Exception in component tMSSqlConnection_1
java.sql.SQLException: Network error IOException: \\(local)\pipe\sql\query (The network path was not found)
I have no problem connecting with SQL Server Management Studio, SQL query tools, or Tableau (a data visualization app that can make MS SQL connections).
My system is running Windows 7 Professional with all patches & updates.
Cheers,
Craig
I tried the various suggestions with ntlmauth.dll and the jtds.jar file without success. So I figured I try an older version. Well, in less than 10 minutes I installed it and had a working connection.
I don't know what the problem is with the 5.x version but someone needs to figure it out. It seems fairly clear that Talend doesn't understand the root cause of the problem. Or perhaps no one uses MS SQL Server these days <wink!>.
I would again like to thank the people that offered the suggestions and support! It's awesome to see people helping each other out this way.
Craig
That would be interesting. Just a reminder that I'm using Windows Authentication (and not using SQL Server authentication). Outside of that I believe my setup is pretty generic. And would you also let me know what additional parameters you use. Good luck!
here was my solution, i was using Windows 7 64bit - hope it helps
1. verify version of Talend that was installed via- Help -> About Talend Studio -> Installation Details
2. from same location verified version of Java that Talend was using, since i had both 6 and 7 installed.
3. Download jtds-1.2.6
4. Copy "jtds-1.2.6.jar"
FROM C:\Users\...\Desktop\jtds-1.2.6-dist
TO C:\Program Files\Java\jre7\lib
5. Copy "ntlmauth.dll"
FROM C:\Users\...\Desktop\jtds-1.2.6-dist\x64\SSO
TO C:\Program Files\Java\jre7\bin
6. Restarted PC
7. Set TCP port 1433 in SQL Server Configuration Manager under SQL Server Network Configuration -> Protocols for Server -> TCP/IP -> IPAll -> TCP Port (for some reason this was blank)
8. Added Additional parameters to connection setup:
?Integrated Security?=true
