SQL Exception Inserting Transactions into ARRFSYSAUDITLOG

Products

• CA Rapid App Security
• CA Advanced Authentication
• CA API Gateway

Issue/Introduction

We are getting some SQL Exceptions in arcotriskfort.log and because of this Transactions are not logged in table ARRFSYSAUDITLOG.

Tue Jul 10 06:23:09.012 2018 INFO:    pid 2436 tid 5380: 8: 3:608: CallerId[], OrgName[ORGNAME], RelatedInstId[1], RelatedTxnId[865]
Tue Jul 10 06:23:09.012 2018 INFO:    pid 2436 tid 5380: 2: 3:608: UDS Log : Successfully retrieved the user [[email protected]] for organization [ORGNAME]
Tue Jul 10 06:23:09.075 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]] 
Tue Jul 10 06:23:10.091 2018 WARNING: pid 2436 tid 5380: 8: 3:608: SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]] 

Environment

AA 9.x
MS SQL Server

Cause

In Previous releases before 9.0, we used to run the SQL scripts manually and it was working fine for all. 
In 9.0 release, we came up with simplified installer which executes the SQL scripts, bootstraps and made the server UP and running. We eliminated the manual intervention in this release. But here in this, we found that one of the SQL scripts ( create sequence ) failed to execute from Installer which resulted in this error. However we missed capturing this error in the installer log too as we execute the complete SQL script file at once.

Resolution

Follow the steps outlined to resolve the issue --

1. Please Stop the CA AA services ( Risk + Case Management + Strong Auth) 
2. Stop the application Application server 
3. Please make sure you have backup of DB and then run the SQL

DROP PROCEDURE GETNEXTSEQID; 
GO 

CREATE PROCEDURE GETNEXTSEQID 
@pServer_ID ARADMIN_TXID_SERVER_ID_TYPE, 
@pInstance_ID ARADMIN_TXID_INSTANCE_ID_TYPE, 
@pIP_Address ARADMIN_TXID_IP_ADDRESS_TYPE, 
@pBlock_Size INT, 
@pReturn_Seq_ID ARADMIN_TXID_USED_SEQ_NUM_TYPE OUTPUT 
AS 
DECLARE @lnewseq INT; 
DECLARE @errorvar INT SET @errorvar = -1 ; 

SET NOCOUNT ON; 
SELECT @errorvar = USEDSEQNUM FROM ARADMINTXID 
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID; 

IF @errorvar = -1 
BEGIN 
SET @pReturn_Seq_ID = 0 
INSERT INTO ARADMINTXID 
(SERVERID, INSTANCEID, IPADDRESS, USEDSEQNUM) 
VALUES (@pServer_ID, @pInstance_ID, @pIP_Address, @pBlock_Size); 

RETURN 
END 
ELSE 
BEGIN 
SET @pReturn_Seq_ID = @errorvar; 
SET @lnewseq = @pReturn_Seq_ID + @pBlock_Size; 

UPDATE ARADMINTXID SET USEDSEQNUM = @lnewseq, IPADDRESS = @pIP_Address 
WHERE SERVERID = @pServer_ID AND INSTANCEID = @pInstance_ID; 
END 
RETURN

4. Recompile the Procedure as SQL server may have it cached, a Restart of SQL server can also be done if possible.
5. Start the CA AA services ( Risk + Case Management + Strong Auth) 
6. Start the Application server