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
Hi Craig
This is a common issue about SQL SERVER 2008 Installation in Win7.
Check whether TCP/IP protocal has been started up in SQL SERVER Configure Manager.
Regards,
Pedro
You could be correct. I'm torn between thinking it has something to do with the jdbc driver/component configuration and a networking issue. However, the fact that other programs can access the database tilts me towards the jdbc driver/component configuration (maybe somehow related to Windows Authentication for SQL Server).
I didn't mention it but I've also checked to make sure that TOS is using a direct connection (no proxy), which some users found to be a problem.
I appreciate your suggestion!
Craig
The database was installed to use Windows Authentication. I made a couple of attempts to reconfigure SQL Server for mixed mode, but I had similar connection issues and I was reluctant to leave SQL Server running in mixed mode so I undid those changes.
I never thought I'd spend an entire day just getting the connection working, especially since the options all seem pretty basic. I must be overlooking something simple, but I haven't been able to figure it out yet.
Craig
Hi Craig For windows authentication the username and password should be left blank in talend. And then you should also have the ntlmauth.dll in the windows\system32 folder. I had the same problem before and this worked for me. So hope this will be able to help you. Regrds,
Based on the reports of others, maybe even you own report, I was hopeful that the NTLMAUTH.DLL would be the secret sauce but I didn't see any change. I used the 64-bit dll and put it in \windows\system32. I didn't reboot, which I'm wondering now if that would have helped in case the dll doesn't get unloaded.
I also tried the current version of the jtds.jar file (this is the java driver that Talend uses). Talend ships with 1.2 and the current version is 1.2.5. I figured I had nothing to lose by trying but I couldn't tell any difference with this change either. Again, if a jar update requires a restart, then I might have to redo this.
Oh, btw, thanks for your suggestion, too!
Craig
