Cannot create duplicate entry due to orphan record in database

Products

• CA Privileged Access Manager - Cloakware Password Authority (PA)
• CA Privileged Access Manager (PAM)

Issue/Introduction

When a user adds  a target alias or a target account, and gets the error:  Cannot create duplicate entry:

First, use the filters on the UI to search for the duplicate entry.   If there is not one visible, then probably there is a discrepancy in the database that has to be fixed.

Environment

Release:  PAM 3.x or Password Authority 4.5.3

Cause

Various causes:

Password Authority, prior to 4.5.3.10 Release, did not validate the HostName when saving a Target Application.  If the HostName was not valid, the Application would be saved with a Server ID of 0, and the application and account records would exist in the database, but not be visible to the user.  

Custom script could run and delete a Target Application and leave the associated accounts orphaned in the database.

Resolution

When adding a target alias, and get 'Cannot create duplicate entry'.

Password Authority: 

search the database for the target alias:

select * from CSPMADMIN.targetalias where targetaliasname='yourAliasName';

This will lead to the account id for an orphaned account.

To find orphan account records in the database:

Query for orphaned accounts (when target application is deleted but accounts are not):

SELECT distinct targetapplicationid,name from targetapplication join account using (targetapplicationid) where targetapplication.deleted = 1 and account.deleted = 0; 

Make a note of the ID of the orphaned account.  This is the TargetAccount.ID

IF you are allowed  to run an UPDATE command against your database, then the following command will fix this, and we suggest running it after a database backup in case of error: 

UPDATE account,targetapplication set account.deleted = 1 where targetapplication.targetapplicationid = account.targetapplicationid and targetapplication.deleted = 1 and account.deleted = 0 ; 

IF you are NOT allowed to make any write commands to the database due to company policy, follow these steps.

1. Create a target application of type Generic, give it a name like GENXXX

2. Use the CLI to find the ID of the new target application:  cmdName-searchTargetApplication TargetApplication.name=GENXXX     - This is the TargetApplication.ID

3. Use the CLI to update the orphaned account to change the Target Application to the new GENXXX application:  cmdName=updateTargetAccount TargetAccount.ID=XXXX  TargetAccount.userName=nameofAccount  TargetApplication.ID=YYYY

You have now fixed the database so that the account is no longer orphaned, and it can be deleted and re-entered. 

CASE 2:  Query for orphaned target applications (where targetserverid was set to 0)

select * from targetapplication where targetserverid=0 and deleted=0;     get the TargetApplication. ID = XXXX

select * from account where targetapplicationid=XXXX and deleted=0;

Recover in a similar way to Case 1, with assistance from Support. 

PAM

PAM has less chance of having orphaned accounts, but sometimes a faulty script will cause one.

Support can assist through an SSH session to run the queries to clean up the database.