Installing Service Catalog 17.1 on a restored MDB fails to create the mdbadmin user

Products

• CA Service Catalog
• CA Service Management - Asset Portfolio Management
• CA Service Management - Service Desk Manager

Issue/Introduction

When a Service Catalog MDB is restored and then upgraded to 17.1, the installation fails with an error:

"Creation of mdbadmin user failed".

The installation procedure should test whether the mdbadmin user already exists and should not try to create it if it does. In this case the mdbadmin user already exists, so the upgrade procedure should not try to create it.

Cause

The installation procedure runs a temporary SQL script to detect if the mdbadmin user already exists and create it if neccessary:
 

2018/06/05 19.34.11.054 DEBUG [DeployThread: Configuring CA Service Management common tables] [InstallCDB] Setting up MS SQL USER >> dbServerNode (<hostname>\MSSQLSERVER,1433)  (mdbadmin) in dbName (mdb) DBAUser (sa).
2018/06/05 19.34.11.054 DEBUG [DeployThread: Configuring CA Service Management common tables] [InstallCDB] Loading Resource...
2018/06/05 19.34.11.054 DEBUG [DeployThread: Configuring CA Service Management common tables] [InstallCDB] Invoking sqlcmd to setup user : sqlcmd  -S <hostname>\MSSQLSERVER,1433 -U sa -P <PASSWORD> -i  tmp_sql.sql

This fails with an error:

2018/06/05 19.34.11.163 DEBUG [DeployThread: Configuring CA Service Management common tables] [InstallCDB] Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_CI_AS" in add operator.

Environment

Release:
Component: USVCT

Resolution

The SQL error indicates that a comparision is being done and there is a collation mismatch in the fields being compared. Running these SQL statements will tell you the collation of the SQL Server and the MDB

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));
SELECT name, collation_name FROM sys.databases;

These need to be the same and in this case they were not.

SELECT CONVERT (varchar, SERVERPROPERTY('collation'));

This returned "SQL_Latin1_General_CP1_CI_AS".

SELECT name, collation_name FROM sys.databases; 

This returned "Finnish_Swedish_CI_AS".

The database was taken from a SQL Server whose collation was "Finnish_Swedish_CI_AS" and restored onto a system where the collation was "SQL_Latin1_General_CP1_CI_AS" and that difference was the cause of this problem. The solution is to change the collation of the SQL Server to be the same as the collation of the MDB. There are many articles on how to do this, one can be found here:

https://www.mssqltips.com/sqlservertip/2901/how-to-change-server-level-collation-for-a-sql-server-instance/