SQLException in ARRFDbOperations insertSysAuditLog DB Sql Error

Document ID : KB000113611
Last Modified Date : 12/09/2018
Show Technical Document Details
Issue:
Installation of CA Strong Authentication releases prior to 9.0 required DB scipts 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). Currrently, 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)
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
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.

User-added image






 
Additional Information:
None