One Star

tMSSqlConnection frustation!

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
27 REPLIES
One Star

Re: tMSSqlConnection frustation!

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

Re: tMSSqlConnection frustation!

I had done that but I neglected to note this in my describing my other misadventures. Thanks for the suggestion though!
Craig
One Star

Re: tMSSqlConnection frustation!

Hi Craig
Now I think this is an issue at MSSQL side...
Regards,
Pedro
One Star

Re: tMSSqlConnection frustation!

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

Re: tMSSqlConnection frustation!

Hi Craig
Is MSSQL mixed auth mode(means both sqlserver auth mode and windows auth mode)?
Regards,
Pedro
One Star

Re: tMSSqlConnection frustation!

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

Re: tMSSqlConnection frustation!

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,
One Star

Re: tMSSqlConnection frustation!

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

Re: tMSSqlConnection frustation!

Do you use instances?
One Star

Re: tMSSqlConnection frustation!

The SQL Server instance name is SQLR2; however, I don't see a field for entering the instance name. Where would this go?
Craig
One Star

Re: tMSSqlConnection frustation!

In Additional JDBC parameters put "instance=SQLR2"
I think you will need username and password as well.
One Star

Re: tMSSqlConnection frustation!

Cool, I'll give it a try. Got my fingers crossed! Will let you know how it turns out.
Craig
One Star

Re: tMSSqlConnection frustation!

This is some form of progress. Now I get a different message which suggests a connection might have occurred!
Exception in component tMSSqlConnection_1
java.sql.SQLException: Unable to get information from SQL Server: (local).
This gives me something different to try to figure out.
janhess, thanks for jumping in with your tip!
Craig
One Star

Re: tMSSqlConnection frustation!

Try localhost if this is on your machine and use username and password
One Star

Re: tMSSqlConnection frustation!

More progress (I think)...
If I use username and password, I get a "login failed" message.
Leaving them blank and using localhost results in a message I've seen on the forum.
Exception in component tMSSqlConnection_1
java.sql.SQLException: I/O Error: SSO Failed: Native SSPI library not loaded. Check the java.library.path system property.
I'll check with how others dealt with this and see the door finally opens.
Craig
One Star

Re: tMSSqlConnection frustation!

Have you tried sql server authentication?
I found this on a forum on the web.
Copy the file 'jtds-1.2.2-dist\x86\SSO\ntlmauth.dll' from the unzipped JTDS --> <SQL Developer Home>\jdk\jre\bin. Then restart SQL Developer.
One Star

Re: tMSSqlConnection frustation!

One Star

Re: tMSSqlConnection frustation!

janhess, thanks for your efforts. I'll be tied up the rest of today/tonight so it will be tomorrow before I can work more on this. I'll let you know as soon as I have had a chance to work more on it.
I wonder if this problem is limited to this component on Windows with SQL Server or if it's more widespread. It seems to be somewhat related to how the parameters are setup for the call to the jtds/jdbc driver. Hopefully I'll have a solution before much longer.
Regards,
Craig
One Star

Re: tMSSqlConnection frustation!

I had the same the problem with the TOS versions 4.2 and 5.0.
I think you should try with a version 4.1 of TOS, at least to see if your connection parameters are good.
One Star

Re: tMSSqlConnection frustation!

I've tested my connection using instance and sql server authentication at 5.0.1 and it was ok.
One Star

Re: tMSSqlConnection frustation!

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

Re: tMSSqlConnection frustation!

Which 5.x version were you using?
One Star

Re: tMSSqlConnection frustation!

I was using TOS 5.0.1.
One Star

Re: tMSSqlConnection frustation!

I tested 5.0.1 using an import from 4.2.3 from 4.0.1 and it was ok. I'll try to set up a new connection and see if mine works.
One Star

Re: tMSSqlConnection frustation!

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!
One Star

Re: tMSSqlConnection frustation!

This is my connection for sql server authentication - I don't remember using anything different for Windows but we run from a Unix.
One Star

Re: tMSSqlConnection frustation!

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