Summary:

After installing a copy of Sage CRM onto an instance of Microsoft SQL Server using the Latin1_General_Bin collation, you may find that the CRM webapps cannot connect to the database. This is due to Latin1_General_Bin being a case-sensitive collation, and a setting in the db.properties being in the incorrect case.

Symptoms:

After installing a copy of Sage CRM 7.2 or 7.3, the following error appears in the Tomcat service logs:

org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'cloneReportController': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private com.sage.crm.web.model.report.ReportGetDataModel com.sage.crm.web.controller.CloneReportController.reportDataModel; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'reportGetDataModel': Injection of autowired dependencies failed; nested exception is org.springframework.beans.factory.BeanCreationException: Could not autowire field: private com.sage.crm.core.dao.MetaDataDao com.sage.crm.web.model.report.ReportGetDataModel.metaDataDao; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'metaDataDao' defined in ServletContext resource [/WEB-INF/core-beans.xml]: Invocation of init method failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.GenericJDBCException: Could not open connection

This error indicates a generic issue connecting to the database.

Cause:

As mentioned previously, the Latin1_General_BIN collation is case-sensitive. Since a connection cannot be established with the database, the most likely cause is the JDBC connection string. Here’s an example string from a test CRM install:

db.url=jdbc:log4jdbc:jtds:sqlserver://DB-SERVER:1433/CRM73;language=english;
db.username=sa
db.password=&DMOGELFHOLJCCAAANKBNIHGLLLKDFJPB

In this string, the only relevant parts are the hostname (DB-SERVER), the database name (CRM73), the language parameter and the username and password. Checking in Management Studio, we can see that the hostname should be in upper case, and the database name has the correct case. The username is also correct, and the password always has to be in the correct case. This leaves the language parameter as a candidate.

This can be tested by removing the language parameter, so that the first line of the above looks like this:

db.url=jdbc:log4jdbc:jtds:sqlserver://DB-SERVER:1433/CRM73;

If the CRM webapps deploy correctly after making this change, then the case-sensitive collation was the cause of the issue.

Resolution:

Either remove the language parameter from the connection string, or capitalise the "E" in "English".

Status:

The connection string is capitalised correctly in Sage CRM 7.3c and later.