Connecting with JDBC to SQL Server

My tale of woe that has a happy ending.

I wanted to connect to SQL Server 2005 using JDBC. I verified the connection string and used standard code to test the connection.

try {
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String connectionUrl = "jdbc:sqlserver://localhost:1433;" +
          "databaseName=Northwind;user=x;password=x;";
Connection con = DriverManager.getConnection(connectionUrl);
} catch (SQLException e) {
System.out.println("SQL Exception: " + e.toString());
} catch (ClassNotFoundException cE) {
System.out.println("Class Not Found Exception: " + cE.toString());
}

When I ran the code, I received this error

com.microsoft.sqlserver.jdbc.SQLServerConnection Prelogin
WARNING: ConnectionID:1 Prelogin error: host localhost port 1433 Unexpected response status:0

Actually, I received that error multiple times and eventually received a stack trace of

SQL Exception: com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host localhost, port 1433 has failed. Error: "The driver received an unexpected pre-login response. Verify the connection properties and check that an instance of SQL Server is running on the host and accepting TCP/IP connections at the port. This driver can be used only with SQL Server 2000 or later.

I then checked many sites for this message, but none had the exact message.

I tried logging into SQL Server directly, using the SQL Server Management Express program. I was able to log in with my user account, but could not access the Northwind or pubs databases. I conferred with some colleagues and needed to set the server with mixed access: Windows and SQL. There was an old account that I had created, named sa. I reactivated it and changed its password. I logged on with it and then updated my Windows login account with sysadmin rights. After this, I was able to connect to the Northwind and pubs databases; however, I still received the original error when I tried to connect to the database using JDBC.

After more searching, I found the Code Ranch site which talked about removing dynamic ports and setting a static port in the SQL Server Configuration Manager. I tried to follow the instructions, but my server would not restart with a static port of 1433. I then tried to access the server from JDBC using the dynamic port and was able to connect.

---

Next, I tried to connect to the database using Hibernate. I used the JDBC driver from microsoft, the package for the driver is com.microsoft.sqlserver.jdbc.SQLServerDriver. The Hibernate dialect for SQL Server is org.hibernate.dialect.SQLServerDialect. I was unable to connect. I used the same URL as I used when connecting with Java, jdbc:sqlserver://localhost:3346/Northwind. The dynamic port is 3346, the name of the database is Northwind.

There was a helpful post about clients and servers that guided me to the SQL Server Configuration Manager again. This time, I started to understand what I was seeing. There is the SQL server and the SQL client. The client was using port 1433, the server was using port 3346. I changed the client to 3346, but I still had an error.

The error was an SSL error. I found a helpful link about certificates, but I was unclear about exporting my certificates and lost them. Since this link referred to installing SQL Server after deleting the certificates, I am downloading Server 2008. I am hoping that a new installation will miraculously have no problems!

The referring page also had some information about not installing the old certificates, but creating a new one. I am investigating. It refers to a program named SelfSSL.exe.

I did not pursue the creation of a certificate, since the reference was for IIS. I did complete the new installation of SQL Server 2008. I am able to access sqlcmd from the command line and am able to connect to the server from Java. I did not have to tweak any parameters, both the client and the server were using 1433 and TCP/IP was enabled.

I did have one confusion, that may have been the basis of a lot of my problems: I didn't know what a schema was in SQL Server. According to MicroSoft, a schema is like a namespace for tables that can be accessed. If you don't have the correct schema, then you can't see all the tables. The default schema is dbo. Once I changed all my tables to that schema, I was able to connect and see them in NetBeans database services.

I still could not connect using Hibernate. I decided to reinstall MySQL, since I was missing the GUI admin app. After installing, I followed an excellent tutorial on using MySQL in NetBeans. I also found an excellent tutorial for using NetBeans to reverse engineer tables to create POJOs. Once I had these tutorials running, I tackled SQL Server 2008.

I modified the process from the tutorial to create the POJO for two tables from the Northwind database. I found an excellent tutorial for installing Northwind and pubs databases, which for some reason are not included in the install of SQL Server. Once I had the table, I changed the database to the SQL Server instead of MySQL. I have included the important properties below.

   <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property>
<property name="hibernate.connection.url">jdbc:sqlserver://localhost:1433;databaseName=mydb</property>
<property name="hibernate.connection.username">name</property>
<property name="hibernate.connection.password">pass</property>
<property name="hibernate.current_session_context_class">thread</property>
<property name="hibernate.dialect">org.hibernate.dialect.SQLServerDialect</property>

The only difference that I could see from what I had used before was the current session context class; however, in my last iteration of testing before the reinstall, I omitted the dialect. That was probably the big error.

It finally works. In review, I had a problem with SQL Server 2005 that I never resolved; the new installation works without a hitch; I probably had a problem with schemas in the 2005 tests.