Get Up and Running With SQL Server Express, Clojure, SQL Korma and Local Jars

Just a sweet and short little post to help others get up to speed accessing SQL Server Express 2008 with Clojure’s SQL Korma library.

Set Up SQL Server Express

I’m using SQL Server 2008 Express. To configure your DB server go to Start > All Programs > Microsoft SQL Server 2008 > Configuration Tools > SQL Server Configuration Manager. Under SQL Server Network Configuration select Protocols for SQLEXPRESS. On the panel on the right size of the screen, make sure TCP/IP is Enabled, then right click it and select Properties. Select the IP Addresses tab, and make sure you have the following settings:

  1. For IP Address
    • Active: Yes
    • Enabled: Yes
    • TCP Dynamic Ports: Make sure this entry is empty.
    • TCP Port: Make sure this entry is empty.
  2. For IPAll:
    1. TCP Dynamic Ports: Make sure this entry is empty.
    2. TCP Port: 1433

Enable SQL Server mixed mode authentication (SQL Korma doesn’t do integrated/Windows authentication). Run regedit.exe and go to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQLServer. Change the LoginMode to 2, and restart the SQL Server Express service.

Okay, so we can now create SQL accounts on SQL Server Express, so let’s enable the SA account with SQLCMD:

  • sqlcmd .\SQLEXPRESS -E
  • GO
  • ALTER LOGIN sa with password=’aPassword’
  • GO
  • exit

Maybe you don’t want to enable the SA account, but rather create a new non-sysadmin account, as it’s a security risk to use SA for your apps. I just used it here, as it was the shortest way to get a SQL account ;-)

Load The SQL Server JDBC Driver Into a Local Artifactory Repository

Download JFrog’s Artifactory. Go to Artifactory’s bin folder and run InstallService.bat. Then launch Artifactory with  artifactory.bat, and browse to http://localhost:8081/artifactory/webappGrab Microsoft SQL Server JDBC Driver 3.0 and load it into a local Artifactory repository with the following settings:

  • GroupId: sqljdbc4
  • ArtifactId: sqljdbc4
  • Version: 3.0

The reason we’re using Artifactory, is that Leiningen demands that all your dependencies come from a repository somewhere. Since Microsoft’s JDBC driver isn’t available on a public repository like Clojars, is make it available from a local repository. You’re other option is to directly load it into a Maven repository, but trust me, this is time consuming and something you want to avoid.

Now add your local repository to Lein’s defproject, :repositories {“ext-release-local” “http://localhost:8081/artifactory/ext-release-local/”}. You can see I chose to load sqljdbc4.jar into the ext-release-local repository. Also add the SQL Server JDBC driver as a dependency: [sqljdbc4/sqljdbc4 “3.0”]

Make a Nice Clojure SQL Korma

And finally drop SQL Korma as a dependency into your Leiningen project, and do the rest of its configuration as specified on Git Hub:  [korma “0.3.0-RC4”]. If you’re new to SQL Korma, like I am, you might think that (defentity …) will also create your database objects for you. Not so! SQL Korma doesn’t have this feature currently (version 0.3.0) – hopefully they’ll add it in future. So make sure you’re DB objects exist and that the defentity statements correctly map to them.

Sounds like a few simple steps, but I took me unnecessarily long to piece all the above together, and get to the point of accessing SQL Server Express 2008 with SQL Korma. Hopefully the above will help others to reach SQL Server and Korma Nirvana, one time!