Troubleshooting

By default the JDBC Connector works in pooling connection mode. The connection gets locked up for configured application accounts when their password changes dynamically from the managed system without displaying any warning message.

Resolution: Add the following to the source XML using the Identity Security Cloud REST API:

 {
        "op": "add",
        "path": "/connectorAttributes/pool.disablePooling",
        "value": true
 }

The following error is displayed:

java.lang.RuntimeException: sailpoint.connector.ConnectorException: Data out of order exception. Data should be sorted in ascending order. Last identifier '7' current '15'.

Resolution: Add the following to the source XML using the Identity Security Cloud REST API:

{
    "op": "add",
    "path": "/connectorAttributes/disableOrderingCheck",
    "value": true
}

The following error message displays:

Exception during aggregation. Reason: java.lang.RuntimeException: sailpoint.connector.ConnectorException: Data out of order exception. Data should be sorted in ascending order.

The JDBC source skips the unstructured data when aggregating from the table when merging is enabled. The error is displayed even after using the order by clause.

Resolution: Add the following to the source XML using the Identity Security Cloud REST API:

{
    "op": "add",
    "path": "/connectorAttributes/supportUnstructuredData",
    "value": true
}

The Order by clause is required in the query. The query for aggregation must be:

select * from <table name> order by NLSSORT (upper(index_column), "NLS_SORT=generic_m")

Exception in test connection sailpoint.connector.ConnectorException: Failed to load ADAL4J Java library for performing ActiveDirectoryPassword authentication.

To resolve this issue, now you must upload the latest version of MSSQL JDBC jar to JDBC source. Along with the latest MSSQL JDBC jar, also ensure that you upload the dependent MSAL4J lib jar file. The following jar files are required:

• accessors-smart-1.2.jar

• asm-5.0.4.jar

• content-type-2.1.jar

• jackson-annotations-2.12.1.jar

• jackson-core-2.12.1.jar

• jackson-databind-2.12.1.jar

• jcip-annotations-1.0-1.jar

• json-smart-2.3.jar

• lang-tag-1.4.4.jar

• msal4j-1.9.1.jar

• nimbus-jose-jwt-8.18.jar

• oauth2-oidc-sdk-8.23.1.jar

• slf4j-api-1.7.28.jar

Database 'SAILPOINT_DB' does not exist or not authorized.

The server encountered an unexpected error while contacting target system. Please check the logs.

Resolution: Ensure that the service account has the required permissions to access the database. Also, provide the connection parameters in the connection URL along with the specific roles, if required.

When the index column contains special characters, during merging the following error is displayed:

[ InvalidConfigurationException ] [ Possible suggestions ] For data merging, data should be ordered. Please add order by clause in aggregation query or set disableOrderingCheck as true. [ Error details ] java.lang.Exception: Data out of order exception. Data should be sorted in ascending order.

Resoultion:SailPoint uses the java.text.Collator class to check for ascending (or descending) order. The JVM instance uses the environment locale setting of language, country, and variant when comparing the records.

If the database ResultSet does not comply with the collator instance, then the exception occurs. The recommended resolution is to align the locale settings between SailPoint and your database instance. As an alternative, adjusting the dataset also resolves the ordering constraint.

Method 1

1. In order to add a collator class, you need to write one and add the compiled class to your library path for the web application and add it to your JDBC app definition.

2. Add the following entry to the source XML using the Identity Security Cloud REST API:

   Note
   For more information on SailPoint's REST APIs, refer to Best Practices: REST API Authentication and REST API - Update Source (Partial) in the SailPoint Developer Community.

   Copy

   {
       "op": "add",
       "path": "/connectorAttributes/collatorClassName",
       "value": "<example>.collators.MyCollator"
   }

3. Drop the MyCollator.class in WEB-INF\classes\something\collators

   For more information, refer to RuleBasedCollator (Java Platform SE 7).

   You will want to extend your custom collator off of this class.

Method 2

1. Remove the check for sorting and make sure that the ordering in the SQL query is correct.

2. Add the following entry to the source XML using the Identity Security Cloud REST API:

   Note
   For more information on SailPoint's REST APIs, refer to Best Practices: REST API Authentication and REST API - Update Source (Partial) in the SailPoint Developer Community.

   Copy

   {
       "op": "add",
       "path": "/connectorAttributes/disableOrderingCheck",
       "value": true
   }

You should know that if your data is out of order in your query, then there is the possibility of the aggregation missing information with this option, but if you are sure your data is in order, then this workaround should work for you.

While configuring Databricks or Amazon Athena the following errors are displayed:

• [ ConnectorException ] [ Error details ] The server encountered an unexpected error while contacting target system. Please check the logs. [Databricks]JDBC Driver does not support this optional feature.

• [ ConnectorException ] [ Error details ] The server encountered an unexpected error while contacting target system. Please check the logs. [Simba]JDBC Driver does not support this optional feature.

Resolution: Databricks and Amazon Athena do not support scrollable result sets. Add the following to the source XML using the REST API and then try and test the connection:

{
    "op": "add",
    "path": "/connectorAttributes/resultSetType",
    "value": "TYPE_FORWARD_ONLY"
}

The resultSetType key with the value TYPE_FORWARD_ONLY ensures that the dataset is read in a forward manner only.

When JDBC is configured for an IBM AS/400 database, after discovering the schema, the IIQDisabled field is returned as IIQDISABLED, which is not updating the account status for user. If you edit the schema column to IIQDisabled from IIQDISABLED, then no data flows for that column.

Resolution: Add double quotation marks ("") around the IIQDisabled column to retrieve the exact case as specified in your query. For example:

select column1, column2, case when column2='disable' then 'true' else 'false' end as "IIQDisabled" from test order by column1;

(When performing a test connection, the following I/O error is displayed:

ERROR https-jsse-nio-8443-exec-10 sailpoint.connector.jdbc.JDBCExceptionHandlerService:167 - Error occurred in null. [ ConnectionFailedException ]

[ Possible suggestions ] Check the configuration details and if there is smooth connectivity with managed system.

[ Error details ] Error occurred before establishing the connection with Server. Failure trying to get a pooled connection to [jdbc:oracle:thin:@168.11.11.11:1521/svc_name] java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection sailpoint.connector.ConnectionFailedException: [ ConnectionFailedException ]

[ Possible suggestions ] Check the configuration details and if there is smooth connectivity with managed system.

[ Error details ] Error occurred before establishing the connection with Server. Failure trying to get a pooled connection to [jdbc:oracle:thin:@168.11.11.11:1521/svc_name] java.sql.SQLRecoverableException: IO Error: The Network Adapter could not establish the connection at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[?:1.8.0_208]

Resolution: When entering the database URL, use the correct format. The address should be in the following format:

jdbc:dbtype:@ipaddress:databasename

For example:

jdbc:oracle:thin:@172.16.23.155:1521:orcl

When you insert data into a MySQL table, certain characters (such as the backslash \\) are treated as escape characters. Escape characters are used to represent special characters or to prevent special characters from being interpreted in a special way.

For example, if you use INSERT INTO USER_MASTER VALUES ('mq103\\test', 'MQ Test User 103'); to insert the date. Then after executing this SQL, the value in the column is stored as 'mq103\test'

Now delta table has 'mq103\test' and when the connector tries to search for this value using select * from USER_MASTER where user_id = 'mq103\test'; it does not return a result and the delta will not be aggregated in the record.

Resolution: Use one of the following: 

1. Change the sql_mode globally by adding NO_BACKSLASH_ESCAPES, using the following command: 

   Copy

   SET GLOBAL sql_mode = 'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION,NO_BACKSLASH_ESCAPES';

   After changing sql_mode globally, log in to your MySQL server to verify the updated changes. This mode changes the behavior of the backslash character (\) in string literals. If enabled, it treats the backslash as an ordinary character rather than as an escape character.

2. Create a before insert trigger on the USER_DELTA table so that if the USER_MASTER table has 'mq103\test' then the USER_DELTA table will store it as 'mq103\\test'

   For example: 

   Copy

   DELIMITER //
   CREATE TRIGGER T1_BEFORE_INSERT
   BEFORE INSERT ON USER_DELTA
   FOR EACH ROW
   BEGIN
       SET NEW.USER_ID = REPLACE(NEW.USER_ID, '\\', '\\\\');
   END;
   //

The create account operation fails even though the recommended attribute mappings are used in the Account Policy.

Resolution – Ensure that the managed system is enabled to set the account attributes used by the Account Policy, then try the Create account operation again.

The Provisioning rule is executing multiple times, leading to issues during the provisioning operation.

Resolution:

1. The JDBC connector code already manages the total number of account requests and attribute requests. Therefore, customer do not need to add any for Loop in rule.

2. Remove any for loops present in the Provisioning Rule.

3. After removing the loops, test the provisioning operation to ensure it runs correctly without multiple executions.

   For example :

   Copy

   import java.sql.*;
   import java.sql.Statement;
   import java.sql.Statement;
   import sailpoint.object.ProvisioningResult;
   import sailpoint.object.ProvisioningPlan;
   import sailpoint.object.ProvisioningPlan.AttributeRequest;
   ProvisioningResult result = new ProvisioningResult();
   result.setStatus(ProvisioningResult.STATUS_COMMITTED);
   Statement stmt = null;
   String user = request.getNativeIdentity();
   System.out.println("User = " + user);
   try {
     String query =
         "UPDATE DB.table SET status=\"E\" where id=\"" + user + "\"";
     stmt = connection.createStatement();
     stmt.execute(query);
     System.out.println("query = " + query);
   } catch (Exception e) {
     result.addError(e.getMessage());
       result.setStatus(ProvisioningResult.STATUS_FAILED);
   } finally {
     if (stmt != null) stmt.close();
   }
   return result;