000029409 - 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

Document created by RSA Customer Support Employee on Jun 14, 2016Last modified by RSA Customer Support on Dec 10, 2019
Version 3Show Document
  • View in full screen mode

Article Content

Article Number000029409
Applies ToRSA Product Set: SecurID
RSA Product/Service Type: Authentication Manager
RSA Version/Condition: 8.1
IssueThe 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." 
CauseIf 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.
WorkaroundTo 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.

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