000029409 - Migration to 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 Employee on Apr 21, 2017
Version 2Show 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. 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.
ResolutionThere should be a patch to prevent this in future releases of Authentication Manager 8.1 SP1 or later.
WorkaroundTo work around this issue, the certificate entry will need to be deleted from the Authentication Manager 8.1 database by runnign 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 semi colon 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 semi colon 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.%';