Getting SQL Server to work with Play Framework

Originally published on svbtle on SEPTEMBER 20, 2016

I'm using the jTDS driver with Play 2.5.8 (which uses HikariCP) and kept getting exceptions like the following when trying to create connection pools on startup.

[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-1 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-2 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-3 - Failed to execute isValid() for connection, configure connection test query. (null)
[info] application - Creating Pool for datasource 'default'
[error] c.z.h.p.PoolBase - HikariPool-4 - Failed to execute isValid() for connection, configure connection test query. (null)
[error] application - 

! @71ejcojkn - Internal server error, for (GET) [/] ->

play.api.Configuration$$anon$1: Configuration error[Cannot connect to database [default]]  
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.Configuration.reportError(Configuration.scala:806)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:48)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
    at scala.collection.immutable.List.foreach(List.scala:381)
    at play.api.db.DefaultDBApi.connect(DefaultDBApi.scala:42)
    at play.api.db.DBApiProvider.get$lzycompute(DBModule.scala:72)
    at play.api.db.DBApiProvider.get(DBModule.scala:62)
    at play.api.db.DBApiProvider.get(DBModule.scala:58)
    at com.google.inject.internal.ProviderInternalFactory.provision(ProviderInternalFactory.java:81)
Caused by: play.api.Configuration$$anon$1: Configuration error[Failed to initialize pool: null]  
    at play.api.Configuration$.configError(Configuration.scala:154)
    at play.api.PlayConfig.reportError(Configuration.scala:996)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:70)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:142)
    at play.api.db.DefaultDatabase.getConnection(Databases.scala:138)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:44)
    at play.api.db.DefaultDBApi$$anonfun$connect$1.apply(DefaultDBApi.scala:42)
Caused by: com.zaxxer.hikari.pool.HikariPool$PoolInitializationException: Failed to initialize pool: null  
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:512)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:105)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:58)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:54)
    at scala.util.Try$.apply(Try.scala:192)
    at play.api.db.HikariCPConnectionPool.create(HikariCPModule.scala:54)
    at play.api.db.PooledDatabase.createDataSource(Databases.scala:199)
    at play.api.db.DefaultDatabase.dataSource$lzycompute(Databases.scala:123)
    at play.api.db.DefaultDatabase.dataSource(Databases.scala:121)
Caused by: java.lang.AbstractMethodError: null  
    at net.sourceforge.jtds.jdbc.JtdsConnection.isValid(JtdsConnection.java:2812)
    at com.zaxxer.hikari.pool.PoolBase.checkDriverSupport(PoolBase.java:400)
    at com.zaxxer.hikari.pool.PoolBase.setupConnection(PoolBase.java:375)
    at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:346)
    at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:506)
    at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:105)
    at com.zaxxer.hikari.HikariDataSource.<init>(HikariDataSource.java:71)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:58)
    at play.api.db.HikariCPConnectionPool$$anonfun$1.apply(HikariCPModule.scala:54)
    at scala.util.Try$.apply(Try.scala:192)

Googling it didn’t yield much until I pasted more of the stacktrace than normal. Finally found this in the Hikari-CP mailing list, which I guess I probably should have figured out from the stacktrace itself. This stackoverflow answer is really good, too.

Essentially, they both say that the jTDS library doesn’t implement the isValid method that HikariCP relies on. So the fix is to manually set the validation query in the HikariCP settings. It’s pretty simple.

play.db {  
  prototype {
    hikaricp.connectionTestQuery = "SELECT 1"
  }
}

And that’s the sauce. I’m also going to add some readonly settings + disable ebean evolutions because that’s what I’m doing for my project.

UPDATE 2017-08-15 : Another thing that bit me recently was that the default settings for this driver use NTLM v1 by default, which is insecure. So you need to manually set your connection to use NTLM v2. My impression is that the two versions are basically the equivalent of http and https. See the USENTLMV2=true bit below in the connection string.

You can also see the domain option, which you'll need to add if you want to use a domain account with the database. I usually just use a regular old SQL account, though.

TLDR;

Here are my finished settings:

play.db {  
  prototype {
    hikaricp.connectionTestQuery = "SELECT 1"
  }
}

play.evolutions {  
  db.default.enabled = false
}

db {  
  default.hikaricp.readOnly = true
  default.url = "jdbc:jtds:sqlserver://localhost:1433/myDatabase;domain=MY-DOMAIN;USENTLMV2=true"
  default.driver = net.sourceforge.jtds.jdbc.Driver
}

Menu

TLDR;