error AMM003004 appears after changing SQL instance port

Products

• CA Client Automation - IT Client Manager
• CA Client Automation

Issue/Introduction

In order to change the database port configured, the following steps can be done:

How do you Change a SQL Port from Dynamic to Static ?

After this is done, CAF services start, but DSM Explorer shows constantly error AMM003004 while loading and also when performing any action.

Environment

Client Automation 14.5 and above

Cause

Running the command "ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsinstance" shows the right port and server instance.

When checking the logs, the following database error appears:

|NOTIFY | evalSQLInfo description: Login timeout expired source: Microsoft SQL Server Native Client 11.0

|NOTIFY | evalSQLInfo description: A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. source: Microsoft SQL Server Native Client 11.0
|NOTIFY | evalSQLInfo sqlstate: 08001 native error: 10061 0x274d
|NOTIFY | evalSQLInfo description: TCP Provider: No connection could be made because the target machine actively refused it.

|NOTIFY | evalSQLInfo class: 16 state: 1 server: 
|NOTIFY | Error Message: ADO Version 6.3 - COM Error: ErrorCode:-2147467259,WordErrorCode:0, Unspecified error, TCP Provider: No connection could be made because the target machine actively refused it.

|ERROR  | *** error trying to connect with:<server_ip> result:-3...

This shows that there is a connection issue with the database, even though the port was changed correctly.

Reviewing more logs, the following message is found on Engine log:

|INFO   | DBStore configuration for 'default'
|INFO   |       Server = [server.domain.net]
|INFO   |      SQLType = [SQLServer]
|INFO   |         Name = [mdb]
|INFO   |     Instance = [MSSQLSERVER,1433]
|INFO   |     Username = [ca_itrm]
|INFO   |     Password = [****]

This means that somewhere the old port is still found.

Resolution

The table "ca_n_tier" stores more server information, in which the instance and port can be found. In order to verify if here is the right value, the following query can be executed:

SELECT * from ca_n_tier

If here is also shown the default port (MSSQLSERVER,1433), this has to be updated too.

In order to update this line, the following query can be executed (CAF services have to be stopped on the Domain Manager first):

update ca_n_tier
set db_instance = 'MSSQLSERVER,1434'
where domain_uuid in (select set_val_uuid from ca_settings where set_id = '1')

NOTE: On this example, the port is changed from 1433 top 1434
NOTE2: Before running any update on the database, it's mandatory to take a full backup of the MDB.