Migration to RSA Authentication Manager 8.1 with the option to retain system settings fails with an error about duplicate or already existing pk_ims_certificates

[#1]

Article Number

000029409

Applies To

RSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.1

Issue

The initial migration from Authentication Manager 7.1 to Authentication Manager 8.1 completes successfully but a subsequent migration using the 'retain system settings' option fails with an error about duplicate or already existing pk_ims_certificates entry '36aa51c92ae110ac028c68c0329966fe'

The migration log shows the error: "Database error during inserting migrated certificates to certificate table. The duplicate key value violates unique constraint. PK_IMS_Certificates." 

Cause

If SMS is configured on both the Authentication Manager 7.1 and Authentication Manager 8.1 systems, both have a certificate for communication with the SMS provider.  Choosing to migration with the "retain system settings" option does not handle the duplicate certificates correctly so the second migration failed.

Workaround

To work around this issue, the certificate entry will need to be deleted from the Authentication Manager 8.1 database by running SQL commands on the PostgreSQL database. Use the ID found in the logs.  

Before proceeding with the steps below, take a backup of the Authentication Manager database through the Operations Console (Maintenance > Backup > Backup Now).

1. Logon to the Authentication Manager 8.1 primary via SSH, vSphere client or local console session.  
2. Navigate to /opt/rsa/am/utils.
3. Run the command ./rsautil manage-secrets -a get com.rsa.db.dba.password. This returns the database password that is unique to the deployment.  This password will be copied then pasted at the prompt for the database password.
4. At the database prompt (db=#),run a select statement to find the SMS certificate entry:  SELECT * from rsa_rep.ims_certificates WHERE id LIKE '36aa51c92ae110ac028c68c0329966fe'; where the certificate ID is the value seen in the migration log.  (Note the semicolon at the end of the command.)  The output should be for one row.
5. Now run a delete command to delete this entry.  The command is:  DELETE FROM rsa_rep.ims_certificates WHERE id LIKE '36aa51c92ae110ac028c68c0329966fe';  (Note the semicolon at the end of the command.)  
   A sample of the steps is below:

   login as: rsaadmin
   Using keyboard-interactive authentication.
   Password:  <enter OS password>
   Last login: Fri Oct  2 15:32:51 2015 from jumphost.vcloud.local
   RSA Authentication Manager Installation Directory: /opt/rsa/am
   rsaadmin@am81p:~> cd /opt/rsa/am/utils
   rsaadmin@am81p:~> ./rsautil manage-secrets -a get com.rsa.db.dba.password
   rsaadmin@am81p:/opt/rsa/am/utils> ./rsautil manage-secrets -a get com.rsa.db.dba.password
   Please enter OC Administrator username: <enter OC admin username>
   Please enter OC Administrator password: <enter OC admin password>
   com.rsa.db.dba.password: rSKD5bGguLGNL9uGvFWnJoxIcHJah2
   rsaadmin@am81p:/opt/rsa/am/utils> ../pgsql/bin
   rsaadmin@am81p:/opt/rsa/am/pgsql/bin> ./psql -h localhost -p 7050 -d db -U rsa_dba
   Password for user rsa_dba: rSKD5bGguLGNL9uGvFWnJoxIcHJah2
   psql.bin (9.2.4)
   SSL connection (cipher: DHE-RSA-AES256-SHA, bits: 256)
   Type "help" for help.
   db=# SELECT * from rsa_rep.ims_certificates WHERE id LIKE '36aa51c92ae110ac028c68c0329966fe';
   db=# DELETE from rsa_rep.ims_certificates WHERE id LIKE '36aa51c92ae110ac028c68c0329966fe';

6. Run the migration again.

 

Notes

SELECT * from ims_config_value WHERE name LIKE 'ims.certmanager.cert.%';