Can't connect to SQL Server via Microsoft SQL Server 2012 JDBC Driver

1
votes

This is weird. I'm on a Mac OS X 10.7.5 64-bit host running VirtualBox 4.2.6. I've got a Windows 7 SP1 guest (64-bit) with bridged networking running Microsoft SQL Server Express 2012. I configured SQL Server to use TCP/IP on port 1433, not dynamic ports (setting is blank, not 0). The Windows 7 guest has IPv4 address 192.168.99.132 and Windows Firewall is turned off.

I downloaded the Microsoft SQL Server 2012 JDBC driver along with Squirrel SQL client 3.4.0 for both Windows and Mac. When I run Squirrel on the Windows 7 guest OS itself, I can connect to SQL Server just fine via the Microsoft JDBC driver using the guest machine names of either "winny", "localhost", or "192.168.99.132" (JDBC URLs like jdbc:sqlserver://winny\SQLEXPRESS:1433;databaseName=vha). However, when I try to connect to SQL Server from Squirrel running on the Mac host, I get an exception (see stack trace below).

Here's the weird part. If, from the Mac host, I use the jTDS SQL Server driver (version 1.2.7 because I'm using JDK6, so can't use jTDS 1.3.0 -- that version is JDK7 only), I connect right away with the JDBC URL jdbc:jtds:sqlserver://192.168.99.132:1433/vha.

Any ideas why the Microsoft driver fails but jTDS succeeds from a remote host? The only piece of infomration missing from the jTDS URL that's in the Microsoft one is the instance name ("SQLEXPRESS"). And yes, I've tried the Microsoft URL with and without the instance name.

Java version on the Mac host is:

java version "1.6.0_37"
Java(TM) SE Runtime Environment (build 1.6.0_37-b06-434-11M3909)
Java HotSpot(TM) 64-Bit Server VM (build 20.12-b01-434, mixed mode)

Java version on the Windows 7 guest is:

java version "1.6.0_35"
Java(TM) SE Runtime Environment (build 1.6.0_35-b10)
Java HotSpot(TM) 64-Bit Server VM (build 20.10-b01, mixed mode)

Stack trace when connecting from Mac host to Windows 7 guest:

java.util.concurrent.ExecutionException: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.99.132, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at java.util.concurrent.FutureTask$Sync.innerGet(FutureTask.java:232)
    at java.util.concurrent.FutureTask.get(FutureTask.java:91)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.awaitConnection(OpenConnectionCommand.java:132)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$100(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$2.run(OpenConnectionCommand.java:115)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:439)
    at java.util.concurrent.FutureTask$Sync.innerRun(FutureTask.java:303)
    at java.util.concurrent.FutureTask.run(FutureTask.java:138)
    at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)
    at java.lang.Thread.run(Thread.java:680)
Caused by: java.lang.RuntimeException: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.99.132, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:171)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.access$000(OpenConnectionCommand.java:45)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand$1.run(OpenConnectionCommand.java:104)
    ... 6 more
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 192.168.99.132, port 1433 has failed. Error: "null. Verify the connection properties. Make sure that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".
    at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDriverError(SQLServerException.java:190)
    at com.microsoft.sqlserver.jdbc.SQLServerException.ConvertConnectExceptionToSQLServerException(SQLServerException.java:241)
    at com.microsoft.sqlserver.jdbc.SocketFinder.findSocket(IOBuffer.java:2243)
    at com.microsoft.sqlserver.jdbc.TDSChannel.open(IOBuffer.java:491)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connectHelper(SQLServerConnection.java:1309)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.login(SQLServerConnection.java:991)
    at com.microsoft.sqlserver.jdbc.SQLServerConnection.connect(SQLServerConnection.java:827)
    at com.microsoft.sqlserver.jdbc.SQLServerDriver.connect(SQLServerDriver.java:1012)
    at net.sourceforge.squirrel_sql.fw.sql.SQLDriverManager.getConnection(SQLDriverManager.java:133)
    at net.sourceforge.squirrel_sql.client.mainframe.action.OpenConnectionCommand.executeConnect(OpenConnectionCommand.java:167)
    ... 8 more
2
sql-servermacosjdbcvirtualboxjtds

2 Answers

0
votes

In theory you also need the instance name in the jTDS connection string (e.g. appending ";instance=SQLEXPRESS") -- but I am not familair with jTDS and I might be wrong, but if you are not setting that and you are connecting then perhaps you are connecting to something else?

You showed your jTDS connection string above with the IP address and the Microsoft one with the server name "winny" (jdbc:sqlserver://winny\SQLEXPRESS:1433;databaseName=vha) -- have you tried the IP address? (jdbc:sqlserver://192.168.99.132\SQLEXPRESS:1433;databaseName=vha)

Things I usually try:

  1. Verify the port -- can you telnet to localhost port 1433 on the host machine
  2. Verify the network -- make sure the host responds to ping -- can you ping it by name from the client? By IP Address?
  3. Verify port 1433 is open -- can you telnet from the client to the host on port 1433?

Because you can connect from the Mac client with jTDS you should be able to do all three of these already but it would be worth double checking.

You could also try hardcoding the port 1433 (change it from blank) on the server -- maybe another instance of SQL exists? See also http://support.microsoft.com/kb/287932

Here are some steps I usually go through, maybe this will help:

  1. (Server) Open SSMS, right-click the SQL Server, Properties; verify that Allow Remote Connections is checked. If not, check and restart SQL Server.
  2. (Server) Open SQL Server Configuration Manager (Start -> SQL Server -> Configuration Tools)
    1. Verify that TCP/IP is Enabled
    2. For default instance verify that static port 1433 is set (Note: a named instance will have a backslash, e.g. something like MYSQLSERVER\MYINSTANCE, whereas a default instance is identified with just the server name.)
    3. For named instance choose another static port, e.g. 1434+, and set it as the static port. Only one per instance – do not re-use them on the same machine.
    4. Restart SQL Server service
  3. (Server) Verify that what you did worked so far; on SQL Server machine:
    1. Open CMD prompt, "telnet localhost 1433" (change 1433 to other port number as appropriate)
      1. If you get a blank screen that is "hung", it worked!
      2. If you get an error, it did not work. Go back to square 1.
    2. Try the same thing but using the machine name instead of localhost
  4. (Server) Configure Windows Firewall to add an exception to TCP port 1433 (or whichever port you chose in step 2)
  5. (Server) If using named instances, add another exception to UDP port 1433 as well (for the SQL Browser service, which uses UDP not TCP – also note SQL Browser Service ALWAYS uses port 1433 even if you chose a different TCP port for your instance in step 2)
  6. (Client) Verify from another machine on the same network
    1. Open CMD prompt, "ping " – if you get responses, the machines can see each other. Even if you do not get a response, try the next step – Windows 7 for example may default to NOT reply to echo requests (ping)
    2. Open CMD prompt, "telnet 1433" (change 1433 to other port number as appropriate)
      1. If you get a blank screen that is "hung", it worked!
      2. If you get an error, it did not work. Go back to square 1.
  7. (Client) Now try and connect to the SQL Server from another application like Excel
  8. (Client) See also http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/01/29/testing-connection-to-sql-server-from-a-service-running-under-local-system-account.aspx for other errors
-1
votes
    Step 1: Download jtds.jar from http://sourceforge.net/projects/jtds/files/

    Step 2: Add the jar into eclipse or any IDE.

    Step 3: 
    String driver="net.sourceforge.jtds.jdbc.Driver";
    Class.forName(driver).newInstance();

    String connString="jdbc:jtds:sqlserver://127.0.0.1:1433/database_name;encrypt=false;user=sa;password=yourpassword;integratedSecurity=true;instance=SQLEXPRESS;";
    String username="sa";
    String password="yourpassword";
OR
String connString="jdbc:jtds:sqlserver://192.168.1.198:1433/database_name;encrypt=false;user=sa;password=yourpassword;";
        String username="sa";
        String password="yourpassword";