Installing Service Catalog 17.1 on a restored MDB fails to create the mdbadmin user
Article ID: 102331
Updated On:
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.
"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:
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.
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
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/
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/
Feedback
Powered by
