Configuring Policy Express (PX) Actions to Fail on SQL Server Update Failure

Products

• CA Identity Manager
• CA Identity Suite

Issue/Introduction

To ensure transactional integrity, you may require Identity Manager to roll back or abort an event if an external SQL Server update fails. This configuration ensures that Identity Manager actions and SQL database operations are synchronized, preventing partial updates. This is achieved by implementing a dual-policy approach using Policy Express (PX).

Environment

Identity Manager 14.x & 15

Resolution

To ensure that Identity Manager actions fail when a SQL Server update fails, your SQL Stored Procedure must be designed to return a specific error code or exception upon failure. Once the Stored Procedure is configured to return an error status, configure the following two PX policies:

1. Validation Policy (UI Level):

   • Create a PX policy triggered on Submit (UI validation).
   • This policy performs preliminary checks to ensure that the required parameters are passed correctly to the Stored Procedure. If data validation fails here, the action is blocked before it reaches the SQL Server.

2. Event-Based Policy (Execution Level):

   • Create a PX policy triggered on the specific Event associated with the task (e.g., Create/Modify User).
   • Configure this policy to execute the Stored Procedure.
   • Set the PX Action to evaluate the return code of the Stored Procedure.
   • If the Stored Procedure returns an error status, configure the PX policy to execute an Abort action. This effectively stops the Identity Manager event from completing, ensuring the Identity Manager action is rolled back alongside the failed SQL update.

Note: Ensure your Stored Procedure includes structured error handling (e.g., TRY...CATCH blocks) and returns a non-zero exit code or explicit error message that the PX policy is configured to identify as a failure.