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
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
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
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.
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
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.
