Using SQL Server with JDBC

First word of advice

Don’t.

Ok, seriously

If you find yourself in the unfortunate position of having to use Microsoft’s SQLServer in a Java application, keep these things in mind:

  1. Microsoft hates Java.
  2. Microsoft hates Java programmers.
  3. Microsoft hates you most of all.

Background

As part of a project that supported multiple database dialects, I had to ensure that our system was compatible with SQLServer. So I spent a frustrating day installing the database on a development machine and getting it to play nicely with Java. Hopefully I can save others the pain that I went through.

Installation

Note: I’ve put the install files for all of these on the Intelliware apps server. It might be easier for anyone behind our firewall to get the apps from there instead of grabbing them from MS.

Part one: .NET framework

Before you can can install SQLServer, you need to have Microsoft’s .NET framework installed. For SQLServer 2005, version 2.0 of .NET is required.

When you install .NET, you’ll be prompted to close down IE and Outlook. I just want to emphasize this: in order to install a database server, you need to shut down your mail program.

Part two: SQLServer Express

Microsoft’s SQLServer comes in a variety of packages and licensing options. It appears that SQLServer Express is the developer-level product: it claims to be light-weight and is free. It can be downloaded here.

Take all of the default selections except for the choice of security models. The product really pushes you towards using “Windows security”, but it turns out that the default security mode cannot be used with JDBC. Pick “mixed mode” and assign a password to the sa user. The 2005 version of SQLServer forces you to pick a strong password, which is MS-speak for “it can’t be blank or ‘password'”.

Once the software is installed, you have to, uh, turn on TCP/IP support. This is apparently a feature and not a bug (for perceived security reasons – but this is a lightweight, developer-level version of the product. So we don’t really care about security).

Anyway. Start the SQL Server Configuration Manager. Go to SQL Server Network Configuration, and select Protocols for SQLEXPRESS (or whatever your database server instance name is). Enable TCP/IP, then select TCP/IP and right-click to set the properties for it. On the “IP Addresses” tab, in the IPAll section, set the port number to 1433.

Part three: Download JDBC driver

The driver is here. Here’s the fun part: it’s a self-extracting WinZip executable. That contains a JAR file. Which is all that you’re interested in.

So unzip it to your desktop, get the JAR file, and move it to your project directory or wherever you want to use it.

Now you should be ready to connect. Your JDBC connection URL will look like this:

jdbc:sqlserver://localhost:1433

Your userid will be sa, and the password is whatever you specified when you installed the product.

Other tools

Microsoft SQL Server Management Studio Express is a handy graphical tool for managing your database.

It's only fair to share...
Share on FacebookGoogle+Tweet about this on TwitterShare on LinkedIn

Leave a Reply