DevTest Registry schema validation: Invalid column name 'user_unique_id'.

Products

• CA Application Test
• Service Virtualization
• CA Continuous Application Insight (PathFinder)

Issue/Introduction

When using the portal, it is not possible to save configuration preferences - when next logging in the preferences are no longer set 

In the portal log, entries similar to the following can be found 

2018-05-14 06:53:57,359Z (09:53) [qtp832196726-109] ERROR com.itko.lisa.invoke.resource.PreferenceResource - Invalid column name 'user_unique_id'. 
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid column name 'user_unique_id'. 

Using an SQL client, it is noted that the column USER_UNIQUE_ID  is present in tables ACLUSERS and ACL_AUDIT_LOG.

Also, seeing  missing column in table LISA_AGENT

 Error creating bean with name 'hibernateDatastoreServiceRegistry': Cannot resolve reference to bean 'hibernateDatastore' while setting constructor argument; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'hibernateDatastore': Bean instantiation via constructor failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [org.grails.orm.hibernate.HibernateDatastore]: Constructor threw exception; nested exception is org.hibernate.tool.schema.spi.SchemaManagementException: Schema-validation: missing column [machine] in table [LISA_AGENT]

Cause

SQL server, by default, has case-independent name comparison - this may be overridden by using a non-default collation sequence 

Environment

DevTest using SQL Server

Resolution

Use collation sequence sql_latin1_general_cp1_ci_ai in order to resolve this issue