Unable to open DSM Explorer, with a hang in AM, SD or RC portals.

Products

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

Issue/Introduction

When attempting to open DSM Explorer, the progress bar hangs at any of the following:

"Retrieving AM portal data"
"Retrieving SD portal data"
"Retrieving RC portal data"

The database-facing CAF plugin logs contain errors like this:

"Communication link failure"
"An existing connection was forcibly closed by the remote host."

Example (TRC_AMMGR_0.log -- AM portal):
NOTIFY | evalSQLInfo sqlstate: 08S01 native error: 10054 0x2746
NOTIFY | evalSQLInfo description: Communication link failure source: Microsoft SQL Server Native Client 10.0
NOTIFY | evalSQLInfo sqlstate: 08S01 native error: 10054 0x2746
NOTIFY | evalSQLInfo description: TCP Provider: An existing connection was forcibly closed by the remote host.
 source: Microsoft SQL Server Native Client 10.0
NOTIFY | evalSQLInfo class: 16 state: 1 server: 
NOTIFY | Error Message: ADO Version 6.1 - ExecuteCommandPointer []  -COM Error: ErrorCode:-2147467259,WordErrorCode:0, Unspecified error, TCP Provider: An existing connection was forcibly closed by the remote host.
, Microsoft SQL Server Native Client 10.0, (null)

Example (TRC_USD_DIALOGM_0.log -- SD portal):
NOTIFY | evalSQLInfo sqlstate: 08S01 native error: 10054 0x2746
NOTIFY | evalSQLInfo description: Communication link failure source: Microsoft SQL Server Native Client 10.0
NOTIFY | evalSQLInfo sqlstate: 08S01 native error: 10054 0x2746
NOTIFY | evalSQLInfo description: TCP Provider: An existing connection was forcibly closed by the remote host.
 source: Microsoft SQL Server Native Client 10.0
NOTIFY | evalSQLInfo class: 16 state: 1 server: 
NOTIFY | Error Message: ADO Version 6.1 - COM Error: ErrorCode:-2147467259,WordErrorCode:0, Unspecified error, TCP Provider: An existing connection was forcibly closed by the remote host.
, Microsoft SQL Server Native Client 10.0, (null)
ERROR  | cannot create record set of resources
ERROR  | The pool encountered an error during creation of a record set
NOTIFY | The following SQL Statement couldn't be executed: SELECT instance, state, last_state_change FROM ca_pool_resource WHERE pool_name = 'SD_API_SERVER_POOL'.
NOTIFY | The db api returned error message: .
NOTIFY | The db api returned error code: -1 and error category: 0.

Environment

Client Automation - 14.x

Cause

Client Automation requires an active connection to the SQL database in order to function.  DSM Explorer is essentially a live browser for SQL, to represent data from the database in a friendly manner.  If these errors are being observed in the database-facing logs on the manager, it means Client Automation has lost connectivity with the database.

Resolution

Database connectivity needs to be recovered first, then DSM Explorer will be able to function properly.

1- Start by killing the CAF service:

# caf kill all

2- For troubleshooting purposes, it will be helpful to delete all the logs from this folder:

D:\Program Files (x86)\CA\DSM\logs\*.*

3- Also, delete the notification server cache:

D:\Program Files (x86)\CA\DSM\appdata\cfnotsrvd.dat

4- Identify the SQL database connection info from ITCM comstore:

ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsserver
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbmsinstance
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbname
ccnfcmda -cmd GetParameterValue -ps itrm/database/default -pn dbuser​

5- Here are some suggested items to check for troubleshooting the SQL connectivity outside of the ITCM product:

- Is the SQL database installed locally or remotely from the ITCM application server?
- Try using SQL Server Management Studio to connect to the database server and instance.
- On the SQL server, is the database instance running in SQL Server Configuration Manager?
- Try using 'ping' or 'nslookup' to verify DNS is resolving the correct IP address of the SQL server, from the ITCM application server.
- Is there a firewall blocking the database port usage?  The default instance is TCP 1433, however, ITCM can be configured to use any instance name or port.  Refer to "dbmsintance" in step 4, to understand what port and instance name is configured in ITCM.
- Is the ca_itrm user locked out?
- Involve a DBA as necessary.

6- Once connectivity is restored, restart the CAF service:

# caf start

Additional Information

If the issue with DSM Explorer still persists, open a case with Support, and provide a ZIP of the DSM\logs folder.