Hello,
This may be a continuation of Provisioning Plan Request Not Including Attributes From Create Account –
We moved from an old connector that was used to create the userid. The old connector database was migrated, with some unnecessary columns removed, but the existing data intact.
When an identity is introduced (new or existing), we have some source accounts to be created/enabled via the identity profile provisioning; this source is not a part of the selected provisioning off this identity profile. We have the source configured to be ran if the identity has a specific role and access profile.
When a new identity is created and the record does not already exist in the connector database, life is seemingly fine. It goes through the CREATE operation and the userID is returned to sailpoint for other downstream connectors.
When an existing identity is re-activated, the EEID(nativeIdentity) is correlated, and a MODIFY operation is done. It seems to repeatedly re-issue the MODIFY operation, for around 4 times.
The first will update it correctly, but then will not exit the IF and repeat the initial loop at line 24 if ( plan instanceof ProvisioningPlan ) {. Instead of the EEID(nativeIdentity) being referenced again, it returns ??? for the attributeValue. It repeats this until it exits and issues a single identity lookup using the single account query that is configured for the connector. It says object not found, so I assume it is attempting to pass the ???, rather than the real nativeIdentity. If I manually execute the sql with the assumed nativeIdentity, the expected record is returned.
I believe this may be due to new lines; however, I am not strong in Java and may be wrong? If anyone could please confirm that, it’d be great. I’ve attached the code:
import java.sql.Types;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.Date;
import java.util.HashMap;
import java.util.Map;
import sailpoint.object.ProvisioningPlan;
import sailpoint.object.ProvisioningPlan.AccountRequest;
import sailpoint.object.ProvisioningPlan.AttributeRequest;
import sailpoint.object.ProvisioningResult;
import sailpoint.tools.Util;
// Start of JDBC UserID Provisioning rule
log.error(\"Enter UserID Provisioning\");
ProvisioningResult result = new ProvisioningResult();
CallableStatement cStatement = null;
String defaultRole = \"RolesAllUsers\";
Map attributeMap = new HashMap();
// Check if plan is null
if ( plan instanceof ProvisioningPlan ) {
// Get all account requests from plan
List accounts = plan.getAccountRequests();
// If the plan contains one or more account requests, we'll iterate through them
if ((accounts != null) && (accounts.size() > 0)) {
for (AccountRequest account : accounts) {
String nativeId = (String) account.getNativeIdentity();
log.error(\"native identity: \" + nativeId);
log.error(\"application: \" + account.getApplicationName());
log.error(\"operation: \" + account.getOperation());
List attributeRequests = account.getAttributeRequests();
if (attributeRequests != null) {
log.error(\"Num of AttributeRequests: \" + attributeRequests.size());
// iterate through each attribute request
for (AttributeRequest attributeRequest : attributeRequests) {
String attributeName = attributeRequest.getName();
Object attributeValue = attributeRequest.getValue();
attributeMap.put(attributeName,attributeValue);
}
log.error(\"map: \" + attributeMap.toString());
}
try {
// CREATE procedure
if ( AccountRequest.Operation.Create == account.getOperation()) {
log.error(\"Entering Create Operation\");
cStatement = connection.prepareCall(\"EXEC dbo.LoadUniqSAM ?, ?, ?, ?, ?, ?, ?, ?\");
// Add attributes from account create profile
log.error(\"setting value for index 1\");
cStatement.setString(1, attributeMap.get(\"empID\"));
log.error(\"map attr-empID: \" + attributeMap.get(\"empID\"));
log.error(\"setting value for index 2\");
cStatement.setString(2, attributeMap.get(\"firstname\"));
log.error(\"map attr-firstname: \" + attributeMap.get(\"firstname\"));
log.error(\"setting value for index 3\");
cStatement.setString(3, attributeMap.get(\"lastname\"));
log.error(\"map attr-lastname: \" + attributeMap.get(\"lastname\"));
log.error(\"setting value for index 4\");
cStatement.setString(4, attributeMap.get(\"empStatusCode\"));
log.error(\"map attr-empStatusCode: \" + attributeMap.get(\"empStatusCode\"));
log.error(\"setting value for index 5\");
cStatement.setString(5, attributeMap.get(\"empLastHireDate\"));
log.error(\"map attr-empLastHireDate: \" + attributeMap.get(\"empLastHireDate\"));
log.error(\"setting value for index 6\");
cStatement.setString(6, attributeMap.get(\"empEndDate\"));
log.error(\"map attr-empEndDate: \" + attributeMap.get(\"empEndDate\"));
log.error(\"setting value for index 7\");
cStatement.setString(7, (String) account.getNativeIdentity());
log.error(\"account attr-EEID: \" + account.getNativeIdentity());
cStatement.setString(8, attributeMap.get(\"Roles\"));
log.error(\"map attr-Roles: \" + attributeMap.get(\"Roles\"));
log.error(\"LoadUniqSAM payload: \" + cStatement.toString());
cStatement.executeQuery();
cStatement.close();
// Successful Create, so mark result as COMMITTED
log.error(\"Create Account Execution successfully completed\");
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
log.error(\"Exiting Create Operation\");
}
else if (AccountRequest.Operation.Modify == account.getOperation()) {
log.error(\"Entering Modify Operation\");
cStatement = connection.prepareCall(\"EXEC dbo.LoadUniqSAM ?, ?, ?, ?, ?, ?, ?, ?\");
// Add attributes from account modify profile
log.error(\"setting value for index 1\");
cStatement.setString(1, attributeMap.get(\"empID\"));
log.error(\"map attr-empID: \" + attributeMap.get(\"empID\"));
log.error(\"setting value for index 2\");
cStatement.setString(2, attributeMap.get(\"firstname\"));
log.error(\"map attr-firstname: \" + attributeMap.get(\"firstname\"));
log.error(\"setting value for index 3\");
cStatement.setString(3, attributeMap.get(\"lastname\"));
log.error(\"map attr-lastname: \" + attributeMap.get(\"lastname\"));
log.error(\"setting value for index 4\");
cStatement.setString(4, attributeMap.get(\"empStatusCode\"));
log.error(\"map attr-empStatusCode: \" + attributeMap.get(\"empStatusCode\"));
log.error(\"setting value for index 5\");
cStatement.setString(5, attributeMap.get(\"empLastHireDate\"));
log.error(\"map attr-empLastHireDate: \" + attributeMap.get(\"empLastHireDate\"));
log.error(\"setting value for index 6\");
cStatement.setString(6, attributeMap.get(\"empEndDate\"));
log.error(\"map attr-empEndDate: \" + attributeMap.get(\"empEndDate\"));
log.error(\"setting value for index 7\");
cStatement.setString(7, (String) account.getNativeIdentity());
log.error(\"account attr-EEID: \" + account.getNativeIdentity());
cStatement.setString(8, attributeMap.get(\"Roles\"));
log.error(\"map attr-Roles: \" + attributeMap.get(\"Roles\"));
log.error(\"LoadUniqSAM payload: \" + cStatement.toString());
cStatement.executeQuery();
cStatement.close();
// Successful Modify, so mark result as COMMITTED
log.error(\"Modify Account Execution successfully completed\");
result.setStatus(ProvisioningResult.STATUS_COMMITTED);
log.error(\"Exiting Modify Operation\");
}
} catch(Exception e) {
result.setStatus(ProvisioningResult.STATUS_FAILED);
result.addError(e);
log.error(\"UserID Provisioning SQL Error: \" + e.getMessage());
} finally {
cStatement.close();
}
}
}
}
log.error(\"Exit UserID Provisioning\");
cStatement.close();
return result;
I’m hoping if I strip out the new lines, that will cause the if to properly exit once the last statement fires.
This may be related as a result, but the provisioning rule does not return the UserID from that is generated during these situations for MODIFY operation (possibly due to the nativeIdentity being referenced as ???). The sproc attempts to correlate the record on nativeIdentity, and enters the update block if found. If the record doesn’t exist based on nativeIdentity, the sproc inserts the attributes and generates the UserID if it doesn’t exist.
The sproc should return the generated userID regardless of an UPDATE or INSERT into the table as I use an output variable that holds the needed UserID value.
SPROC:
USE [SailPoint-UserID]
GO
/****** Object: StoredProcedure [dbo].[LoadUniqSAM] Script Date: 12/9/2024 4:16:41 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[LoadUniqSAM]
(
@empID NVARCHAR(50) = NULL,
@firstname NVARCHAR(50) = NULL,
@lastname NVARCHAR(50) = NULL,
@empStatusCode NVARCHAR(1) = NULL,
@empLastHireDate DATETIME = NULL,
@empEndDate DATETIME = NULL,
@EEID NVARCHAR(255) = NULL,
@Roles NVARCHAR(255) = NULL,
@SearchSAM NVARCHAR(10) = NULL OUTPUT
)
AS
DECLARE @rowcnt INT, @rowcntEEID INT, @rowcntSearchSAM INT = 0, @seqValue INT, @SearchSAMSQL NVARCHAR(500),@SearchSAMBase NVARCHAR(10), @errorCode INT, @rowcntSearchSAMSeq INT = 0,@seqMax INT, @origLastName NVARCHAR(50), @origFirstName NVARCHAR(50);
SET @rowcntEEID = 0;
SET NOCOUNT ON;
SELECT @EEID AS 'EEID'
IF ISNULL(@EEID, '') <> ''
BEGIN
SELECT @rowcnt = COUNT(*)
FROM dbo.UniqSAM
WHERE EEID = @EEID
SELECT @rowcnt AS 'rowcount'
END;
-- Enter MODIFY operation.
IF @rowcnt > 0
BEGIN TRY
BEGIN
BEGIN TRANSACTION
SELECT @empStatusCode
UPDATE dbo.UniqSAM
SET empStatusCode = COALESCE(@empStatusCode, empStatusCode),
empLastHireDate = COALESCE(@empLastHireDate, empLastHireDate),
empEndDate = @empEndDate
WHERE EEID = @EEID
COMMIT
SELECT @SearchSAM = SearchSAM FROM dbo.UniqSAM WHERE EEID = @EEID
RETURN
END
END TRY
BEGIN CATCH
THROW 18,'Update Failure', 1;
END CATCH;
ELSE
-- Enter CREATE operation.
/*
Build the concatenated base userid from agreed upon standard convention -- (firstname (4 letters),lastname (2 letters))
The sequence is addressed after the base userid has been generated appropriately, padded with zeroes to meet 6 characters if needed.
example base userid for John Smith: johnsm and Bo B: bob000
*/
-- Preserve First and Last Name as is for appropriate columns.
SELECT @origFirstName = @firstname
SELECT @origLastName = @lastname
GOTO SearchSAMBase
SearchSAMBase:
-- strip any non alpha characters from the firstname and lastname.
SELECT @firstname = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@firstname, ' ', ''), '-', ''), '~', ''), '''', ''), '`', ''), '’', '');
SELECT @lastname = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@lastname, ' ', ''), '-', ''), '~', ''), '''', ''), '`', ''), '’', '');
-- generate the base userid from firstname and lastname with collation to remove diacritical marks.
SELECT @SearchSAMBase = CAST(LOWER(SUBSTRING(@firstname,1,4)) AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_Cp1251_CS_AS + CAST(LOWER(SUBSTRING(@lastname,1,2)) AS VARCHAR(MAX)) COLLATE SQL_Latin1_General_Cp1251_CS_AS;
SELECT @SearchSAMBase AS SearchSAMBase
-- after generated base, go to sequence check to get correct sequence to append.
GOTO SearchSAMCheck
SearchSAMCheck:
-- use CTE to query existing userids that follow the same base userid generated, return the highest sequence only.
WITH SearchSAM_CTE (SearchSAM)
AS
(
SELECT MAX(CAST(SUBSTRING(dbo.udf_GetNumeric(SearchSAM), PATINDEX('%[^0 ]%',SearchSAM + ' '), LEN(searchSAM)) AS INT))
FROM dbo.UniqSAM WHERE SearchSAM LIKE @SearchSAMBase + '%'
)
-- set the seqMax variable to the highest value.
SELECT @seqMax = SearchSAM
FROM SearchSAM_CTE a;
-- set the sequence value for the current max sequence + 1 to increment the next sequence to be used in the userid base.
IF ISNUMERIC(@seqMax) <> 1
SET @seqValue = 1
ELSE
SET @seqValue = @seqMax + 1
SELECT @seqValue AS CurrentSeqValue
-- once seqValue is set, go to the SearchSAMTrim to set the correct number of zeros for userid base and the seqValue.
GOTO SearchSAMTrim
SearchSAMTrim:
-- checks the seqValue, and pads zeroes as needed to the SearchSAM base variable and concatenates the current sequence value to use.
IF @seqValue < 10
BEGIN
SELECT @SearchSAM = @SearchSAMBase + REPLICATE('0',7-LEN(@SearchSAMBase)) + cast((@seqValue) as NVARCHAR)
SELECT @SearchSAM AS SEARCHSAM
SELECT @rowcntSearchSAMSeq = COUNT(*) FROM dbo.UniqSAM WHERE SearchSAM = @SearchSAM
SELECT @rowcntSearchSAMSeq
GOTO SearchSAMGen
END;
ELSE IF @seqValue > 9
BEGIN
SELECT @SearchSAM = @SearchSAMBase + REPLICATE('0',6-LEN(@SearchSAMBase)) + cast((@seqValue) as NVARCHAR)
SELECT @SearchSAM AS SEARCHSAM
GOTO SearchSAMGen
END;
ELSE IF @seqValue > 99
BEGIN
SELECT @SearchSAM = @SearchSAMBase + REPLICATE('0',5-LEN(@SearchSAMBase)) + cast((@seqValue) as NVARCHAR)
SELECT @SearchSAM AS SEARCHSAM
GOTO SearchSAMGen
END;
ELSE
BEGIN
RAISERROR('Sequence Over 999', 18, 1)
END;
SearchSAMGen:
-- insert newly formed SearchSAM concatenated value.
BEGIN
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO dbo.UniqSAM( empID
, firstname
, lastname
, SearchSAM
, EEID
, Roles
, empStatusCode
, empLastHireDate
, empEndDate)
VALUES( @empID
, @origFirstName
, @origLastName
, @SearchSAM
, @EEID
, @Roles
, @empStatusCode
, @empLastHireDate
, @empEndDate)
-- check for error code, ensure 0 is returned and finalize transaction with COMMIT or ROLLBACK.
SELECT @errorCode = @@ERROR
IF @errorCode = 0
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
END TRY
-- finalize the try for either MODIFY or CREATE operations with a CATCH to indicate full stored procedure failure.
BEGIN CATCH
THROW 18,'SPROC Failure', 1;
END CATCH;
END;
RETURN;
GO
I feel that the incorrect MODIFY operation loop in the provisioning rule is throwing off the ability to correctly get the right nativeIdentity to correlate the correct UserID to return. I’m unable to confirm it though.
This is occuring in production, and am currently investigating if this is occurring in our sandbox/test tenant. The databases used for these should be the same, as we copy down the production environment from time to time for this connector.