Migrating credential vault data using SQL and direct database operations

Migrating the credential secrets involves moving two tables, VAULT_DATA and VAULT_RESOURCES, from the earlier version database to the current database. The table definition has not changed in WebSphere Portal Express Version 6.1, so the data does not need to be changed. However, because the database is now split into different database domains, the data also has to be split. Use an import and export utility that is provided by the database server.

The following example procedure explains how to import and export the tables when DB2 is the database server. Use this example as a general guide for your environment. Perform this procedure before any users have accessed the current system and potentially added new data to the system.

If you are migrating from V5.0.2.2 or V5.0.2.3, add the following lines to both the SQL export and import commands to avoid a data type mismatch:

CRED_SEGMENT.USER_MAPPED>0=>CRED_SEGMENT.USER_MAPPED='Y'
CRED_SEGMENT.USER_MAPPED=0=>CRED_SEGMENT.USER_MAPPED='N'

Export the tables from the earlier version database.

On the earlier version database server, start the DB2 Command Line Processor (CLP) on Windows or the DB2 shell on UNIX.

Enter the following commands. Type each section of text on one line:

connect to wpsdb user dbuser using dbuserpw export to C:/temp/vault.res.customization.wp5.ixf of ixf messages C:/temp/vault.res.customization.wp5.msgtxt SELECT DISTINCT 
VAULT_RESOURCES.RESOURCE_NAME FROM VAULT_RESOURCES, CRED_SLOT, CRED_SEGMENT WHERE VAULT_RESOURCES.RESOURCE_NAME = 
CRED_SLOT.RESOURCE_NAME AND CRED_SLOT.SEGMENT_OID = CRED_SEGMENT.OID AND CRED_SEGMENT.USER_MAPPED > 0
export to C:/temp/vault.res.release.wp5.ixf of ixf messages C:/temp/vault.res.release.wp5.msgtxt SELECT DISTINCT 
VAULT_RESOURCES.RESOURCE_NAME FROM VAULT_RESOURCES, CRED_SLOT, CRED_SEGMENT WHERE VAULT_RESOURCES.RESOURCE_NAME = 
CRED_SLOT.RESOURCE_NAME AND CRED_SLOT.SEGMENT_OID = CRED_SEGMENT.OID AND CRED_SEGMENT.USER_MAPPED = 0 

export to C:/temp/vault.data.customization.wp5.ixf of ixf messages C:/temp/vault.data.customization.wp5.msgtxt SELECT VAULT_DATA.RESOURCE_NAME, VAULT_DATA.USER_DN, VAULT_DATA.USERID, VAULT_DATA.PWD, VAULT_DATA.BINARY_DATA FROM CRED_SEGMENT, CRED_SLOT, VAULT_DATA  WHERE VAULT_DATA.RESOURCE_NAME = CRED_SLOT.RESOURCE_NAME AND CRED_SLOT.SEGMENT_OID = CRED_SEGMENT.OID AND CRED_SEGMENT.USER_MAPPED > 0 

export to C:/temp/vault.data.release.wp5.ixf of ixf messages C:/temp/vault.data.release.wp5.msgtxt SELECT  VAULT_DATA.RESOURCE_NAME, VAULT_DATA.USER_DN, VAULT_DATA.USERID, VAULT_DATA.PWD, VAULT_DATA.BINARY_DATA FROM CRED_SEGMENT, CRED_SLOT, VAULT_DATA  WHERE VAULT_DATA.RESOURCE_NAME = CRED_SLOT.RESOURCE_NAME AND CRED_SLOT.SEGMENT_OID = CRED_SEGMENT.OID AND CRED_SEGMENT.USER_MAPPED = 0

disconnect wpsdb

where wpsdb is the database name of the earlier version database, dbuser is the database administrator user ID, and dbuserpw is the password for this user ID.

Import the data in the tables to the current database.
1. If the current database server is not on the same system as the earlier version database server, copy the exported IXF files to the current database server system.
2. On the current database server, start the DB2 Command Line Processor (CLP) on Windows or DB2 shell on UNIX.
3. Enter the following commands. Type each section of text on one line:
```
connect to wpsdb user dbuser using dbuserpw 

import from C:/temp/vault.res.customization.wp5.ixf of ixf modified by indexschema=customization messages C:/temp/vault.res.customization.wp6.msgtxt INSERT INTO CUSTOMIZATION.VAULT_RESOURCES 

import from C:/temp/vault.res.release.wp5.ixf of ixf modified by indexschema=release messages C:/temp/vault.res.release.wp6.msgtxt INSERT INTO RELEASE.VAULT_RESOURCES 

import from C:/temp/vault.data.customization.wp5.ixf of ixf modified by indexschema=customization messages C:/temp/vault.data.customization.wp6.msgtxt INSERT INTO CUSTOMIZATION.VAULT_DATA 

import from C:/temp/vault.data.release.wp5.ixf of ixf modified by indexschema=release messages C:/temp/vault.data.release.wp6.msgtxt INSERT INTO RELEASE.VAULT_DATA
```
  where wpsdb is the database name of the current database, dbuser is the database administrator user ID, and dbuserpw is the password for this user ID.
  
  Note: Under certain conditions, the import operation into the VAULT_RESOURCES and VAULT_DATA tables might generate errors indicating that a row with the same resource name already exists. Disregard this error. The VAULT_RESOURCES table only defines resource names for use in the credential vault. If the names already exist, there is no need to redefine them. The same is true for the VAULT_DATA table. Credentials that are already stored in the table must not be redefined. Here is an example of this type of error:
  SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "1" constrains table "SCHEMA_NAME.VAULT_RESOURCES or VAULT_DATA" from having duplicate rows for those columns. SQLSTATE=23505
4. Enter the following commands
```
select USER_DN, RESOURCE_NAME 
from RELEASE.VAULT_DATA vd1
where EXISTS 
   (SELECT * FROM RELEASE.VAULT_DATA vd2 
    WHERE LOWER(vd1.USER_DN) = LOWER(vd2.USER_DN) 
    and vd1.RESOURCE_NAME = vd2.RESOURCE_NAME
    and vd1.USER_DN < vd2.USER_DN)
```
```
select USER_DN, RESOURCE_NAME 
from CUSTOMIZATION.VAULT_DATA vd1
where EXISTS 
   (SELECT * FROM CUSTOMIZATION.VAULT_DATA vd2 
    WHERE LOWER(vd1.USER_DN) = LOWER(vd2.USER_DN) 
    and vd1.RESOURCE_NAME = vd2.RESOURCE_NAME
    and vd1.USER_DN < vd2.USER_DN)
```
  Any value returned by these commands shows a potential user DN conflict where a conversion to lower case would not complete successfully. Review those entries for its matching user DN and manually remove the duplicate rows. Rerun the commands in this step until no values are returned.
5. When the commands in the previous step do not return any values, enter the following commands to update the user DN fields to the lower case string:
```
UPDATE RELEASE.VAULT_DATA
SET USER_DN = LOWER(USER_DN)

UPDATE CUSTOMIZATION.VAULT_DATA
SET USER_DN = LOWER(USER_DN)
```
6. Enter the following command:
```
disconnect wpsdb
```
  where wpsdb is the database name of the current database.
Use the method described in the "Setting configuration properties" topic to make sure that the system.dn value in the Credential Vault service configuration that represents the current version is set to the same value that was used in the earlier version. This DN is used to store system (shared) credentials. If this value is not the same, the vault does not return the credential secrets for shared slots.