Using encryption with SQL Server connections

If you are using SQL Server with an encrypted connection from the Asset Bank database to SQL Server there is an issue to be aware of. There is a bug in Java from Java 6 update 29 onwards affecting encrypted connections, please see this link for more information:

http://sourceforge.net/p/jtds/bugs/662/

So if you are using an affected Java version, and can't connect to the database with the following error appearing in the Tomcat logs:

2014/07/30 11:33:37:565 EDT [ERROR] AssetBank-Standard.com.bright.assetbank.job.service.JobManagerImpl - Error trying update Job com.bn2web.common.exception.Bn2Exception: Could not get connection from pool in DBTransaction.getNewTransaction 	at com.bright.framework.database.service.DBTransactionManager.getNewTransaction(DBTransactionManager.java:85) 	at com.bright.framework.database.service.DBTransactionManager.getCurrentOrNewTransaction(DBTransactionManager.java:128) 	at com.bright.framework.database.service.DBTransactionManager.execute(DBTransactionManager.java:160) 	at com.bright.framework.database.service.DBTransactionManager.execute(DBTransactionManager.java:276) 	at com.bright.assetbank.job.service.JobManagerImpl.claimNextPendingJob(JobManagerImpl.java:399) 	at com.bright.assetbank.job.service.JobManagerImpl.access$0(JobManagerImpl.java:397) 	at com.bright.assetbank.job.service.JobManagerImpl$PendingJobsRunner.run(JobManagerImpl.java:174) 	at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) 	at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) 	at java.lang.Thread.run(Unknown Source) Caused by: java.sql.SQLException: I/O Error: DB server closed connection. 	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2307) 	at net.sourceforge.jtds.jdbc.TdsCore.login(TdsCore.java:602) 	at net.sourceforge.jtds.jdbc.ConnectionJDBC2.(ConnectionJDBC2.java:344) 	at net.sourceforge.jtds.jdbc.ConnectionJDBC3.(ConnectionJDBC3.java:50) 	at net.sourceforge.jtds.jdbc.Driver.connect(Driver.java:182) 	at java.sql.DriverManager.getConnection(Unknown Source) 	at java.sql.DriverManager.getConnection(Unknown Source) 	at org.apache.avalon.excalibur.datasource.JdbcConnectionFactory.getConnection(JdbcConnectionFactory.java:246) 	at org.apache.avalon.excalibur.datasource.JdbcConnectionFactory.newInstance(JdbcConnectionFactory.java:190) 	at org.apache.avalon.excalibur.pool.InstrumentedResourceLimitingPool.newPoolable(InstrumentedResourceLimitingPool.java:641) 	at org.apache.avalon.excalibur.pool.ValidatedResourceLimitingPool.newPoolable(ValidatedResourceLimitingPool.java:145) 	at org.apache.avalon.excalibur.datasource.ResourceLimitingJdbcConnectionPool.newPoolable(ResourceLimitingJdbcConnectionPool.java:91) 	at org.apache.avalon.excalibur.pool.InstrumentedResourceLimitingPool.get(InstrumentedResourceLimitingPool.java:370) 	at org.apache.avalon.excalibur.pool.ValidatedResourceLimitingPool.get(ValidatedResourceLimitingPool.java:97) 	at com.bn2web.common.database.ResourceLimitingJdbcDataSource.getConnection(ResourceLimitingJdbcDataSource.java:240) 	at com.bright.framework.database.service.DBTransactionManager.getNewTransaction(DBTransactionManager.java:80) 	... 9 more Caused by: java.io.IOException: DB server closed connection. 	at net.sourceforge.jtds.jdbc.SharedSocket.readPacket(SharedSocket.java:843) 	at net.sourceforge.jtds.jdbc.SharedSocket.getNetPacket(SharedSocket.java:722) 	at net.sourceforge.jtds.jdbc.ResponseStream.getPacket(ResponseStream.java:466) 	at net.sourceforge.jtds.jdbc.ResponseStream.read(ResponseStream.java:103) 	at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2202) 	... 24 more

then there are two possible solutions. Apply SQL Server updates as per this article:

http://support.microsoft.com/kb/2653857

Or add a line to Tomcat's Java options by doing the following:

First, ensure that you have the SSL parameter in the database URL in database-local.properties or components.xconf. So the database URL should have a format similar to the following:

jdbc:jtds:sqlserver://localhost:1433/assetbank;instance=MSSQLSERVER;ssl=require

Then you need to add a line to Tomcat's Java options. You can load the Tomcat configuration applet by navigating to the \*Tomcat*\bin directory and then double clicking on Tomcat(*version number*)w.exe - on the 'Java' tab add in the following option:

-Djsse.enableCBCProtection=false

Then apply the change, restart Tomcat and try browsing to Asset Bank to test if the application is now connecting to the database.

IMPORTANT: If you needed to use the above method to overcome SQL Server connection issues with encryption then when upgrading Asset Bank you will need to add the option above into the batch file used to start the upgrade process. To do this:

After downloading and unpacking the updater.zip file browse to the startUpdate.bat file and edit it in a text editor. You will need to add the option to the last line in the file, as follows:

java -Djsse.enableCBCProtection=false -jar %APPNAME% %ASSET_BANK% %2 %3 %4 %5

Then save and after performing any other necessary upgrade preparation run the upgrade.


Was this article helpful?

Yes No

Thanks for your feedback!