JDBC Account Provisioning is happening but verifying is failing

AhmedWaleed · April 30, 2024, 8:57am

Which IIQ version are you inquiring about?

Version 8.3

Share all details related to your problem, including any error messages you may have received.

I have issue with a JDBC account request not getting verfied.

the sql statement:
SELECT Users.*, Roles.roleName AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
ORDER BY Users.UserId;

the get objectsql:
SELECT Users.*, Roles.roleName AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘$(identity)’
order by Users.UserId;

the logs:

2024-04-30T10:57:25,069 DEBUG QuartzScheduler_Worker-4 sailpoint.connector.JDBCConnector:1826 - SQL statement[SELECT Users.*, Roles.roleName AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘246’
order by Users.UserId;].
2024-04-30T10:57:25,069 DEBUG QuartzScheduler_Worker-4 sailpoint.connector.JDBCConnector:603 - Boolean variable _bStoreNextResultInMap is false
2024-04-30T10:57:25,069 DEBUG QuartzScheduler_Worker-4 sailpoint.connector.JDBCConnector:2343 - Returning : <?xml version='1.0' encoding='UTF-8'?>

Lebara 360 Read Only

provisioning is happening and verification is failing after running the aggregation and preform identity request maintenance

iamksatish · April 30, 2024, 9:53am

@AhmedWaleed
Is the account getting properly created at target?

AhmedWaleed · April 30, 2024, 10:04am

Yes it is, we are not provisioning all attributes yet but what we have in the code its provisioning.

AroraA3 · April 30, 2024, 10:16am

Can you share a screenshot of the access request the the request details and the provisioning engine part specifically?

Abhisinha89 · April 30, 2024, 11:04am

Please make sure that the get account query returns all the attributes that are mentioned in the Access request. If there will be any attribute that IIQ is trying to provision but not able to fetch (via get account query), it will always remain in verifying state

vishal_kejriwal1 · April 30, 2024, 2:54pm

Check Access Request , Provisioning transaction it will give to detail which attribute is not finished / completed . Some time the format / case of the particular attribute mismatch .

Also you have written query for single account aggregation ?

AhmedWaleed · May 2, 2024, 8:29am

Hello,

Please find below

There is another page but community is not allowing me to add more than 1

AhmedWaleed · May 2, 2024, 8:30am

There is a date in the query, should i return the date in a specific format?

AhmedWaleed · May 2, 2024, 8:31am

I checked the query, the query does return all queries:
SELECT Users.*, Roles.roleId AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘$(identity)’
order by Users.UserId;

Abhisinha89 · May 2, 2024, 8:53am

Try the format that is returned by tbe query. The idea is all the attributes provisioned should be returned by the get user operation

AroraA3 · May 6, 2024, 5:24am

Please increase the list of attributes shown here to show all of them at once. Also, as @Abhisinha89 mentioned make sure that the get account query returns all the attributes that are mentioned in the request.

Saket95606 · May 6, 2024, 8:01am

@AhmedWaleed : Let us know if you issue is resolved, happy to assist

AhmedWaleed · May 13, 2024, 9:43am

Hello Sorry for the late reply,

this is the getObjectSqQL:

SELECT Users.UserId As UserId,
Users.Username As Username,
Users.DisplayName As DisplayName,
Users.Email As Email,
Users.Source As Source,
Users.InsertUserId, InsertUserId,
Users.IsActive As Status,
Users.IdNumber As IdNumber,
Roles.roleID AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘$(identity)’
order by Users.UserId;

I can see the following from the logs:
2024-05-13T12:39:18,581 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - User : 255
2024-05-13T12:39:18,581 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - Object Type : account
2024-05-13T12:39:18,583 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:1826 - SQL statement[SELECT Users.UserId As UserId,
Users.Username As Username,
Users.DisplayName As DisplayName,
Users.Email As Email,
Users.Source As Source,
Users.InsertUserId, InsertUserId,
Users.IsActive As Status,
Users.IdNumber As IdNumber,
Roles.roleID AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘255’
order by Users.UserId;].
2024-05-13T12:39:18,584 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:603 - Boolean variable _bStoreNextResultInMap is false
2024-05-13T12:39:18,586 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - Returning : <?xml version='1.0' encoding='UTF-8'?>

11

AhmedWaleed · May 13, 2024, 9:45am

rest of the logs:
2024-05-13T12:39:18,581 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - User : 255
2024-05-13T12:39:18,581 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - Object Type : account
2024-05-13T12:39:18,583 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:1826 - SQL statement[SELECT Users.UserId As UserId,
Users.Username As Username,
Users.DisplayName As DisplayName,
Users.Email As Email,
Users.Source As Source,
Users.InsertUserId, InsertUserId,
Users.IsActive As Status,
Users.IdNumber As IdNumber,
Roles.roleID AS role
FROM Lebara360.Users
LEFT JOIN Lebara360.UserRoles
ON Users.UserId = UserRoles.UserId
LEFT JOIN Lebara360.Roles
ON Roles.roleid = UserRoles.RoleId
WHERE Users.Username <> ‘admin’
and Users.UserId = ‘255’
order by Users.UserId;].
2024-05-13T12:39:18,584 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:603 - Boolean variable _bStoreNextResultInMap is false
2024-05-13T12:39:18,586 DEBUG QuartzScheduler_Worker-1 sailpoint.connector.JDBCConnector:2343 - Returning : <?xml version='1.0' encoding='UTF-8'?>

11

AdamVentum · May 13, 2024, 3:01pm

Hi and Hello,
Your SQL uses different quote characters for string literals (‘admin’ and ‘255’). Make sure to use standard single quotes (‘admin’, ‘255’) to avoid syntax errors.

WHERE Users.Username <> ‘admin’
and Users.UserId = ‘255’

Regards,
Adam

AhmedWaleed · May 13, 2024, 7:02pm

Hello Adam,

Its the same single quotes used in the aggregation sql and its returning data.

AhmedWaleed · May 13, 2024, 7:13pm

Hello All,

I found out the IdNumber was not getting provisioned and it was failing, corrected this and still not working.


<ResourceObject displayName="Sailpoint test21" identity="256" objectType="account">
  <Attributes>
    <Map>
      <entry key="DisplayName" value="Sailpoint test21"/>
      <entry key="Email" value="sailpoint.test21@lebara.sa"/>
      <entry key="IIQDisabled" value="false"/>
      <entry key="IdNumber" value="1"/>
      <entry key="InsertUserId" value="1"/>
      <entry key="Source" value="ldap"/>
      <entry key="Status" value="1"/>
      <entry key="UserId" value="256"/>
      <entry key="Username" value="sailpoint.test21"/>
      <entry key="role">
        <value>
          <List>
            <String>11</String>
          </List>
        </value>
      </entry>
    </Map>
  </Attributes>
</ResourceObject>

AroraA3 · May 16, 2024, 5:59am

as per this screenshot nothing is provisioning, everything shows as failed. The actual issue was not being able to verify. Are you sure that this request shown in screenshot was provisioned?

AhmedWaleed · May 16, 2024, 6:37am

Hello,

It was provisioning and stuck on the verifying then I run aggregation and then preform request maintenance and it will then fail.

Otherwise, i was able to solve the issue.

The identity attribute for the sql schema of the accounts was the UserId which was generated in the DB, when i switched to username now its working.

Thanks for the help everyone.

vishal_kejriwal1 · May 17, 2024, 1:45am

Yes date should be in same format as exist in target system otherwise that attribute will show as in verified .

Topic		Replies	Views
JDBC Provisioning - Access request Shows shows Provisioning status Pending & Execution status verifying IIQ Discussion and Questions identityiq	5	184	April 11, 2025
For JDBC Application, Requests stuck in provisioning state ISC Discussion and Questions identity-security-cloud , identityiq	3	688	January 23, 2024
JDBC Provisioning Rule showing NULL values for Roles/Entitlements ISC Discussion and Questions rules , identity-security-cloud , provisioning , entitlements , jdbc-connector	6	1122	March 21, 2024
Jdbc rule provisioning IIQ Discussion and Questions identityiq , provisioning	6	1359	October 10, 2023
IIQ (8.1) JDBC Provisioning rule IIQ Discussion and Questions identityiq , provisioning	4	620	August 5, 2024

JDBC Account Provisioning is happening but verifying is failing

Which IIQ version are you inquiring about?

Share all details related to your problem, including any error messages you may have received.

Related topics