FMS initial installation startup failure connecting to the database (4324744)

The Foglight Management Server (FMS) startup fails on a new initial install using Microsoft SQLServer.

Reviewing the FMS log we see some of the errors 

WARN [main] org.jboss.system.ServiceController - Problem starting service com.quest.nitro:service=SpringBootstrap.0

org.springframework.beans.factory.access.BootstrapException:
Unable to initialize group definition. Group resource name [classpath*:com/quest/nitro/service/core-beanref.0.ctx.xml], factory key [foglight-core-services]; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'foglight-core-services' defined in URL [jar:file:/Foglight/server/default/tmp/deploy/tmp47769foglight.sar!/com/quest/nitro/service/core-beanref.0.ctx.xml]:
Instantiation of bean failed; nested exception is org.springframework.beans.BeanInstantiationException:
Could not instantiate bean class [org.springframework.context.support.ClassPathXmlApplicationContext]: Constructor threw exception; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'exporter' defined in class path resource [com/quest/nitro/service/common.ctx.xml]: Invocation of init method failed; nested exception is org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'com.quest.nitro:service=NitroSecurityService' defined in class path resource [com/quest/nitro/service/core-services.0.ctx.xml]: Invocation of init method failed; nested exception is java.lang.RuntimeException: org.hibernate.exception.ConstraintViolationException: could not insert: [com.quest.nitro.service.security.auth.SecWord]

On newer versions of the Foglight, e.g. 5.6.10, the following errors have been listed in the FMS log file in DEBUG mode 1:

Violation of UNIQUE KEY constraint 'UQ__topology__DB2FBE5DC0FC4675'. Cannot insert duplicate key in object 'dbo.topology_property_name'. The duplicate key value is (incidentID).

 at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1160)

ERROR   [main] com.quest.nitro.upgrade.db.validation.CharacterSetCollationValidation - The database is using a case-insensitive character set collation which is not supported.

The DBA altered the %fms_home%\scripts\sql\sqlsvr_create_db.sql script to be case-insensitive (CI) from the original case sensitive (CS).

ie.

From: COLLATE SQL_Latin1_General_CP1_CS_AS'

CHANGED TO: COLLATE SQL_Latin1_General_CP1_CI_AS'

This problem is indicated in the FMS log as a duplicate key:

"Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK__sec_word__756D6ECB'. Cannot insert duplicate key in object 'dbo.sec_word'."

Correct the %fms_home%\scripts\sql\sqlsvr_create_db.sql script so the COLLATE is case-sensitive (CS) and recreate the database.

EXECUTE ('CREATE DATABASE @db.schema.name@

ON

( NAME = @db.schema.name@_dat1,

FILENAME = '''+ @data_path + '@db.schema.name@.mdf'',

SIZE = 250MB,

MAXSIZE = UNLIMITED,

FILEGROWTH = 25MB )

LOG ON

( NAME = @db.schema.name@_log1,

FILENAME = '''+ @data_path + '@db.schema.name@.ldf'',

SIZE = 50MB,

MAXSIZE = 10GB,

FILEGROWTH = 5MB )

COLLATE SQL_Latin1_General_CP1_CS_AS'

Or:

Re-create the MS SQL Database using the db script generated from the Foglight install.