Transitioning from jTDS to Microsoft's JDBC driver

Summary

Crowd is bundled with the jTDS Driver for SQL Server but Crowd is also supported with the Microsoft JDBC Driver for SQL Server.

This page describes how to update Crowd from using the bundled jTDS Driver to using the Microsoft JDBC Driver to communicate with Microsoft SQL Server. Refer to the Crowd Supported Platforms for the supported versions of Microsoft SQL Server.

Solution

What do I have to do?

Follow these steps to switch an existing installation of Crowd from using the jTDS Driver to the Microsoft JDBC Driver.

If you are installing Crowd for the first time, install Crowd with the jTDS Driver and then follow these steps to switch over to the Microsoft JDBC Driver.

💡 Note that these steps have been verified with Crowd 3.4.x using the Microsoft JDBC Driver 6.3 JRE8 Preview bundled with Confluence.

Why change drivers?

• If you see all the C3P0PooledConnectionPoolManager JTDS driver threads "block" trying to get a connection when SQL Server looks readily available for new connections:

  1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 "C3P0PooledConnectionPoolManager[identityToken->1abcyea999ohuy3abcdef|123aca5e]-HelperThread-#2" #21 daemon prio=5 os_prio=0 tid=0x000000001b72a000 nid=0x2858 runnable [0x000000001eeee000] java.lang.Thread.State: RUNNABLE at java.net.DualStackPlainDatagramSocketImpl.socketReceiveOrPeekData(Native Method) at java.net.DualStackPlainDatagramSocketImpl.receive0(DualStackPlainDatagramSocketImpl.java:124) - eliminated <0x00000000ebf67698> (a java.net.DualStackPlainDatagramSocketImpl) at java.net.AbstractPlainDatagramSocketImpl.receive(AbstractPlainDatagramSocketImpl.java:143) - locked <0x00000000ebf67698> (a java.net.DualStackPlainDatagramSocketImpl) at java.net.DatagramSocket.receive(DatagramSocket.java:812) - locked <0x00000000ebf687e8> (a java.net.DatagramPacket) - locked <0x00000000ebf67640> (a java.net.DatagramSocket) at net.sourceforge.jtds.jdbc.MSSqlServerInfo.<init>(MSSqlServerInfo.java:78) at net.sourceforge.jtds.jdbc.JtdsConnection.<init>(JtdsConnection.java:290) at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:184) at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:146) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:195) at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:184) at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:200) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1086) at com.mchange.v2.resourcepool.BasicResourcePool.doAcquireAndDecrementPendingAcquiresWithinLockOnSuccess(BasicResourcePool.java:1073) at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:44) at com.mchange.v2.resourcepool.BasicResourcePool$ScatteredAcquireTask.run(BasicResourcePool.java:1810) at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:648)

How to proceed

1. Shutdown Crowd

2. Download the Microsoft JDBC Driver 6.4 for SQL Server

   1. Extract the mssql-jdbc-6.4.x.jre8.jar from the downloaded file

   2. Copy the mssql-jdbc-6.4.x.jre8.jar into <crowd-install>/apache-tomcat/lib

3. Backup <crowd-home>/shared/crowd.cfg.xml

4. Edit <crowd-home>/shared/crowd.cfg.xml and update the JDBC driver string and URL.

   1. The existing jTDS configuration driver and connection string syntax should look similar to this:

      1 2 <property name="hibernate.connection.driver_class">net.sourceforge.jtds.jdbc.Driver</property> <property name="hibernate.connection.url">jdbc:jtds:sqlserver://localhost:1433/crowd</property

   2. Update the configuration to the Microsoft SQL Server Driver syntax:

      1 2 <property name="hibernate.connection.driver_class">com.microsoft.sqlserver.jdbc.SQLServerDriver</property> <property name="hibernate.connection.url">jdbc:sqlserver://;serverName=localhost;portNumber=1433;databaseName=crowd</property>

5. Start Crowd

💡 Additional parameter values to use in the new URL are beyond the scope of this documentation; they should be chosen based on the jTDS settings they are replacing.

Additional Information for the curious

• The JDBC URL format for the jTDS driver is documented on SourceForge at http://jtds.sourceforge.net/faq.html#urlFormat.

• The JDBC URL format for Microsoft's SQL Server driver is documented on MSDN at http://msdn.microsoft.com/en-us/library/ms378428.aspx, with documentation for additional properties at http://msdn.microsoft.com/en-us/library/ms378988.aspx.