Unable to update data connection password of a data source in imam by using the command-line interface in Cloud Pak for Data

Troubleshooting

Problem

Running the imam command with the updateDCPwd action in Cloud Pak for Data doesn't update the data connection password or renders inconsistent results.

Symptom

Run the imam command with the updateDCPwd action:

cd /opt/IBM/InformationServer/ASBServer/bin
./imam.sh -action updateDCPwd -u <<username>> -w <<password>> -fp /opt/dc1.csv -s <<host_name>>

The command fails with the following error:

<list id="1">
  <com.ibm.mmi.rest.handler.impl.UpdateDCPasswordHandler_-UpdateDCPasswordResultFormatter id="2">
    <importAreaName>test1</importAreaName>
    <isPasswordUpdateSuccessful>false</isPasswordUpdateSuccessful>
    <failureMessage>DataConnection password has been updated in staging but not in active repository.</failureMessage>
    <outer-class id="3"/>
  </com.ibm.mmi.rest.handler.impl.UpdateDCPasswordHandler_-UpdateDCPasswordResultFormatter>
</list>

Environment

Cloud Pak for Data with the base configuration of Watson Knowledge Catalog installed.

Diagnosing The Problem

Go to Catalogs > Metadata import, create a connector-based import area, and perform a metadata import. In the following steps, test1 is used as import area name.
Log in to the Cloud Pak for Data cluster as a user with the appropriate permissions and get the name of the iis-services pod:
```
oc get pods | grep iis-services
```
Log in to the pod. Use the pod name obtained in the previous step.
```
oc rsh <iis-services-pod-name> bash
```

Update the data connection with a CSV file with an allowed format. For example, create a dc1.csv file with the following content:

DataConnectionName, DataConnectionConnectionType, DataConnectionDatabase, AssociatedImportAreas, DataConnectionUserName, DataConnectionPassword
dc1,JDBCConnector,XMETA,test1,db2inst1,Admin123

Run the imam command with the updateDCPwd action:

cd /opt/IBM/InformationServer/ASBServer/bin
./imam.sh -action updateDCPwd -u <<username>> -w <<password>> -fp /opt/dc1.csv -s <<host_name>>

A success message is expected but an error message similar to the following is returned, which indicates that the password is not updated for the data connection.

<list id="1">
  <com.ibm.mmi.rest.handler.impl.UpdateDCPasswordHandler_-UpdateDCPasswordResultFormatter id="2">
    <importAreaName>test1</importAreaName>
    <isPasswordUpdateSuccessful>false</isPasswordUpdateSuccessful>
    <failureMessage>DataConnection password has been updated in staging but not in active repository.</failureMessage>
    <outer-class id="3"/>
  </com.ibm.mmi.rest.handler.impl.UpdateDCPasswordHandler_-UpdateDCPasswordResultFormatter>
</list>

Resolving The Problem

Use the workaround to update password for data connections used in import areas in Cloud Pak for Data environments, where running the imam command with the updateDCPwd action doesn't work.

Workaround:

Encrypt the new password that needs to be updated by using the following command:

/opt/IBM/InformationServer/ASBNode/bin/encrypt.sh

For example, for the plain text password "Admin123":

./encrypt.sh
Enter the text to encrypt:
Enter the text again to confirm:
{iisenc}7e/7P+uVarzzXBjhdqwtDQ==

Update the data connection password by using database SQL queries:
1. Get the name of the c-db2oltp-iis-db2u pod:
```
oc get pods | grep db2u
```
2. Log in to the pod. Use the name obtained in the previous step:
```
oc rsh <c-db2oltp-iis-db2u-pod-name> bash
```
3. Update the password for the data connection in the active repository by updating the table XMETA.ASCLMODEL_DATACONNECTION:
```
update XMETA.ASCLMODEL_DATACONNECTION
set PASSWORD_XMETA='{iisenc}7e/7P+uVarzzXBjhdqwtDQ=='
where NAME_XMETA='dc1'
```
4. In the XMETA database, ASCLMODEL_DATACONNECTION contains information about data connections. The columns NAME_XMETA and PASSWORD_XMETA contain the name and the password for a data connection. Only encrypted values should be used in PASSWORD_XMETA.
5. Update table XMETASR.MMI_DATACONNECTIONPARAMETER. Update MMI_DATACONNECTIONPARAMETER with a query based on the XMETASR.MMI_IMPORTAREA and XMETASR.MMI_DATACONNECTIONDESCRIPTOR tables. In detail, update the VALUE_XMETA with the newly encrypted password of the data connection for the IDENTIFIER_XMETA, which is "Password". CONTAINER_RID is XMETA_REPOS_OBJECT_ID_XMETA of the XMETASR.MMI_DATACONNECTIONDESCRIPTOR.
```
update
XMETASR.MMI_DATACONNECTIONPARAMETER
set VALUE_XMETA='{iisenc}7e/7P+uVarzzXBjhdqwtDQ=='
where CONTAINER_RID=(select
XMETA_REPOS_OBJECT_ID_XMETA
from XMETASR.MMI_DATACONNECTIONDESCRIPTOR
where CONTAINER_RID=(select
XMETA_REPOS_OBJECT_ID_XMETA
from XMETASR.MMI_IMPORTAREA
where NAME_XMETA='test1'))
and IDENTIFIER_XMETA='Password';
```

Verify if the data connection password has been updated successfully:

The updated password can be verified using the getConn REST API call.

https://<CP4D server>/ibm/iis/imam/dcm/getConn/<dc rid>?maskPassword=false

Get the RID of the data connection. Open the following URL in any browser replacing <CP4D server> with the hostname of your deployment:
```
https://<CP4D server>/ibm/iis/imam/dcm/getAllDCs
```
Example:

https://cpd-wkc.example.com/ibm/iis/imam/dcm/getAllDCs
Get the appropriate data connection RID from the getAllDCs REST CALL response and submit the getConn REST API call for this RID.

Sample RID:
```
"dataConnectionId_": {
		"rid_": "b1c508ce.8e4c0a48.2hq8ds9l0.4qcp6m2.am9tc9.fjcsar7tuaqurd826mbpn",
		"repositoryType_": "ACTIVE"
	}
```
Open the following URL in any browser replacing <CP4D server> with the hostname of your deployment and <dc rid> with the RID obtained from the getAllDCs REST CALL response:
```
https://<CP4D server>/ibm/iis/imam/dcm/getConn/<dc rid>?maskPassword=false
```
Example:
https://cpd-wkc.example.com/ibm/iis/imam/dcm/getConn/b1c508ce.8e4c0a48.2hq8ds9l0.4qcp6m2.am9tc9.fjcsar7tuaqurd826mbpn?maskPassword=false

The getConn REST API call response contains the data connection details and should show the updated password.

Example:
```
{
	"channelId": "CAS/JDBCConnector__1.0",
	"parameters": {
		"dataStoreName_": "XMETA",
		"Username": "db2inst1",
		"dcName": "dc1",
		"id": "b1c508ce.8e4c0a48.2hq8ds9l0.4qcp6m2.am9tc9.fjcsar7tuaqurd826mbpn",
		"URL": "jdbc:db2://abc.example.com:50000/XMETA",
		"Password": "Admin123"
	}
}
```

Document Location

Worldwide

[{"Type":"MASTER","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS7VFN","label":"IBM Cloud Pak for Data Information Server"},"ARM Category":[{"code":"a8m0z0000001ir7AAA","label":"Cloud Pak for Data"}],"ARM Case Number":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"All Versions"}]

Product Synonym

IBM Cloud Pak for Data Information Server

Was this topic helpful?

Document Information

Modified date:
21 April 2023

UID

ibm16982081

Tips