fails to create the mdbadmin user when Installing Service Management products on an existing MDB

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, as part of Swing box migration, 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.

Environment

Release:  17.3 or higher
Component: CA Service Management

Cause

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

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.

Resolution

The SQL error indicates that a comparison 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

Additional Information

The above message can vary but it will usually reference two different collation methodologies, ie:

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 "Latin1_General_CI_AS" in add operator.