Connecting to Microsoft SQL server from Clojure

Posted on February 16, 2014

For some reason I battled to find a good reference on using MSSQL from Clojure. Here is how I got is working.

There is a choice between the proprietary MS driver and an open source jTDS one. I opted for jTDS. see http://jtds.sourceforge.net/faq.html

jTDS JAR

Get the jTDS-n.n.n.JAR from the zip file on SourceForge and place it on your class path.

To get your class path you can run

   lein classpath

Project dependencies

Add the jTDS dependency to your project.clr

   [org.clojure/java.jdbc "0.3.5"]

Authentication

Getting the JDBC connection string just right was where I had issues. This is what I ended up with

  (let [sql-db {:subprotocol "jtds:sqlserver",  
               :subname (str "//" sqlServer "//" sqlDb ";useNTLMv2=true;domain=" domain),  
               :user userName,  
               :password password}]

Where

sqlServer is the SQL server machine name / IP
sqlDb is the default SQL database
domain is the domain for your user account
userName is the userName
password is the password

SSO

If you want to use single sign on (SSO) / integrated security then you need the ntlmauth.dll from the jTDS download zip. Its in the /x64/SSO folder. The DLL must be placed in the same folder as the jtds JAR. This only works on a windows** **host

If you use SSO remove the :user and :password from the map above

Thats it. Good luck