How to Enable the Attribute Sync from SailPoint ISC to an JDBC Authoritative Source?

Identity Security Cloud (ISC) ISC Community Knowledge Base
, , ,
1

Problem

We had an requirement that there was an IDBC Authoritative Source which was connected to SailPoint ISC using OOTB JDBC Connector. When the Microsoft AD provisioning is completed for the user, the SamAccountName of the user from MS AD has to be replaced inside the Username/Uid field of SailPoint ISC and also, same the attribute has to be updated synced back and updated in the JDBC authoritative source as well automatically without any manual interventions.

Diagnosis

This was something which was possible but if you have any source that is connected to SailPoint ISC as authoritative source, then, you cannot directly sync any attribute from SailPoint ISC to that source but you need to right JDBC Provisioning Rule as well. Hence, the solution is possible but there are few tweaks and turns you need to do.

Solution

In order to achieve this solution, you need to perform the following steps.

  1. Note that connection from SAILPOINT ISC to that respective JDBC Authoritative Source Should be Open.
  2. The Service Account which you are using for connecting to JDBC source should have read and write permission on the respective database and schemas.
  3. Add the “Username” attribute in the Create Profile of respective JDBC source and map it to UID identity attribute.
  4. Go to Attribute Sync Page and Enable the attribute sync for “Username” attribute which was just added to Create Profile page.
  5. Create a JDBC Provisioning rule. Refer the below sample rule.
import java.sql.Connection;
  import java.sql.DriverManager;
  import java.sql.PreparedStatement;
  import java.sql.SQLException;
  import java.sql.Types;
  import java.util.List;
  import sailpoint.api.SailPointContext;
  import sailpoint.connector.JDBCConnector;
  import sailpoint.object.Application;
  import sailpoint.object.ProvisioningPlan;
  import sailpoint.object.ProvisioningPlan.AccountRequest;
  import sailpoint.object.ProvisioningPlan.AttributeRequest;
  import sailpoint.object.ProvisioningPlan.PermissionRequest;
  import sailpoint.object.ProvisioningResult;
  import sailpoint.object.Schema;
  import sailpoint.object.Identity;
  import sailpoint.object.*;

  public String getAttributeRequestValue(AccountRequest acctReq, String attribute) {

    if ( acctReq != null ) {
      AttributeRequest attrReq = acctReq.getAttributeRequest(attribute);
      if ( attrReq != null ) {
        return attrReq.getValue();
      }
    }
    return null;
  }
  public String getSamAccountName(String attribute) {
	  String samAccountName = null;
	  String[] parts = null;
	  if ( attribute != null ) {
		  parts = attribute.split("__");
		  samAccountName = parts[0];
		  return samAccountName;
	  }
	  return null;
  }
  
public String getFileNumber(String attribute) {
	  String ihaFileNumber = null;
	  String[] parts = null;
	  if ( attribute != null ) {
		  parts = attribute.split("__");
		  ihaFileNumber = parts[1];
		  return ihaFileNumber;
	  }
	  return null;
  }
  
  ProvisioningResult result = new ProvisioningResult();
  PreparedStatement statement = null;
  Identity identity = null;
  String samAccountNameFromAD = null;
  String fileNumberFromAD = null;
  String tempStringDetails = null;

  if ( plan != null ) {

    List accounts = plan.getAccountRequests();
    if ( ( accounts != null ) && ( accounts.size() > 0 ) ) {
      for ( AccountRequest account : accounts ) {
        try {
           if ( AccountRequest.Operation.Modify.equals( account.getOperation() ) ) {

			tempStringDetails = getAttributeRequestValue(account,"employeeID");
			
			samAccountNameFromAD = getSamAccountName(tempStringDetails);
			fileNumberFromAD = getFileNumber(tempStringDetails);
			
			if ( samAccountNameFromAD != null && fileNumberFromAD != null ) {
				
				statement = connection.prepareStatement( "Insert into vt.users (FileNumber,UserLogin) Values (?,?)" );
				statement.setString ( 1, fileNumberFromAD);
				statement.setString ( 2, samAccountNameFromAD);
				statement.executeUpdate();
				result.setStatus( ProvisioningResult.STATUS_COMMITTED );
			
			}
	
          }
        }
        catch( SQLException e ) {
          result.setStatus( ProvisioningResult.STATUS_FAILED );
          result.addError( e );
        }
        finally {
          if(statement != null) {
            statement.close();
          }
        }
      }
    }
  }
  return result;
  1. Create a FirstValid Transform and map it to Username/uid attribute in respective JDBC Source Identity Profile. Refer the below transform as example.
{
    "name": "Test Set Uid",
    "type": "firstValid",
    "attributes": {
        "values": [
            {
                "attributes": {
                    "attributeName": "sAMAccountName",
                    "sourceName": "MS AD"
                },
                "type": "accountAttribute"
            },
            {
                "attributes": {
                    "attributeName": "UserName",
                    "sourceName": "HR DB"
                },
                "type": "accountAttribute"
            }
        ]
    }
}
  1. Deploy the JDBCProvisioning Rule which is Connector Rule on the SailPoint ISC tenant.
  2. Attach the JDBC Provisioning rule to the respective JDBC Authoritative Source using POSTMAN APIs of SailPoint ISC. Refer the below SailPoint ISC documentations.
  1. Test the Flow and Validate whether updates are working as expected in respective JDBC Authoritative Source.
3 Likes