Connecting to SQL Server via JDBC

Every few years I find myself backed into working with SQL Server for something or other. And I can never remember how to wire it up to the various Java-based tools I have. So this time I’m going to stuff some notes into my trusty old backup brain here such that I will be ready to go two years from now.

Version

As of the time of this post, SQL Server 2012 is out.  And it is completely useless to me since the Java world is blissfully unaware that Microsoft, too, has moved on from 2008. Soooo.. if you want to find a free version of SQL Server that actually works on Windows 7, see here: http://www.microsoft.com/en-us/download/details.aspx?id=29846.  Get one with “WT” (for “With Tools” of course) in the title. WT versions include the SQL Server Management Studio via which you will configure SQL Server to not ignore the non-Microsoft world.

Drivers

Lots of JDBC drivers out there.  Here’s a bunch of themjTBS is an open source pure Java type 4 JDBC 3.0 driver that seems to be the best: It supports both Windows and SQL server authentication; even Microsoft’s own drivers only support SQL Server username password auth.  Note that you may need to perform a DLL rain dance as outlined here to get jTBS to play nicely with Windows’ baked-in security.  Peruse the README.SSO packed away inside the jTBS archive.  If you only need username/pass authentication, then read on guilt-free.

Convince SQL Server to Listen

  1. Turn on TCP/IP.  Do this form the “SQL Server Configuration Manager” app.  Select “Network Configuration”, click on your instance, change TCP/IP option to “Enabled”.  Restart SQL Server from Services.
  2. But wait, there’s more.  Now allow username/password authentication in addition to Windows SSO authentication.  (Many Java apps don’t seem to like Windows authentication even if your driver is set up to support it.)  Launch the “SQL Server Management Studio”, right click the root node, select “Properties” and then the “Security” tab.  Under “Server authentication” select “SQL Server and Windows Authentication mode”:

  3. Finally, make sure that at least one user is granted access to the server.  For the “sa” (sys admin) user, fire up SQL Server Configuration Manager again and unfold your way to Security -> Logins -> sa.  Right click, “Properties”, “Status”.  Permission to connect to the database engine:  Grant.  Login:  Enabled.  Okay.Don’t forget to re-restart SQL Server after all of this.

Whew!  Should you have followed the above steps and thrown a pinch of salt over your shoulder then please prepare for a jig-induced euphoric hangover of joy because you, my friend, have successfully connected to SQL Server via JDBC.

If not, find some more salt.