search cancel

SQLException in ARRFDbOperations insertSysAuditLog DB Sql Error

book

Article ID: 113611

calendar_today

Updated On:

Products

CA Advanced Authentication CA Risk Authentication CA Advanced Authentication - Risk Authentication (RiskMinder / RiskFort)

Issue/Introduction

Installation of CA Strong Authentication releases prior to 9.0 required DB scripts to be run manually, but the 9.0 installer automatically executes the sql DB scripts then bootstraps and brings up the AA server. At times on a Windows DB server during installation an issue leads to failure of execution of a sql DB command (in this case Create Sequence command). Currently, this failure during installation is not captured in the installer logs and the only way this issue is detected via the following footprints in the arcotriskfort.log. 

SQLException in ARRFDbOperations::insertSysAuditLog : [[DB: Sql Error]] 

Addressing this intermittent installation issue at the installer level is being worked with CA Engineering.
 

Environment

CA Risk Authentication and CA Strong Authentication Version 9.0 (Windows ONLY)

SQL server

Cause

On a CA Strong Authentication 9.0 Windows installation only we have observed that one of installation DB scripts commands may fail to execute leading to this issue. 

Resolution

On a Windows DB server, please update the GETNEXTSEQID stored procedure. Then restart the DB and DB  services. 

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

Here is the procedure with related screenshots:
  1. Login to your MS SQL Server. Navigate to the Stored Procedure as shown below

<Please see attached file for image>

User-added image


2. Modify the dbo.GETNEXTSEQID as shown below. Note a restart of DB server and DB services will be required for completing the required update as the DB server caches these stored procedures.

<Please see attached file for image>

User-added image






 

Additional Information

None

Attachments

1558695928749000113611_sktwi1f5rjvs16ice.jpeg get_app
1558695926584000113611_sktwi1f5rjvs16icd.jpeg get_app