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 XML in the application .xml file:

<entry key="pool.disablePooling">
    <value>
        <Boolean>true</Boolean>
    </value>
</entry>

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 disableOrderingCheck attribute as follows to the application debug page:

<entry key="disableOrderingCheck">
    <value> 
        <Boolean>true</Boolean>
    </value> 
</entry> 

The JDBC connector fails to discover schema with correct alias names in case of MYSQL Database.

Resolution: Add the useOldAliasMetadataBehavior parameter at the end of the database URL (after the SID (Database Name)) and set it to true.

For example, if the database URL in the UI is URL = "jdbc:mysql://localhost:3306/mydb" then, append a question mark (?) to the useOldAliasMetadataBehavior parameter. Then, add this parameter at the end after setting it to true.

The resulting URL should be as follows:

jdbc:mysql://localhost:3306/mydb?useOldAliasMetadataBehavior=true

During aggregation for large datasets the following error message appears:

Out of memory

Resolution: For large datasets add the following entries to the application debug page:

<entry key="resultSetFetchSize" value="1000"/>
<entry key="statementFetchSize" value="1000"/>
<entry key="resultSetType" value="TYPE_FORWARD_ONLY"/>

SailPoint recommends that the values of the resultSetFetchSize and statementFetchSize keys are same.

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

Test Connection fails with the following error message for Windows authentication when the Apache Tomcat service is running under Local System Account instead of Local Service Account which is a SPN (Service Principal Name) account:

Login failed for user 'NT AUTHORITY ANONYMOUS LOGON

The SPNs are set up to run from a specific user on the domain. If the correct SPN is not running, windows authentication would default to incorrect user (that is Local System Account user) and displays the Anonymous Login error.

Resolution: Verify and update the Username for Apache service by performing the following steps:

1. Go to Windows > Run > services.msc.

2. Right-click the Apache Tomcat service in the list of services displayed.

3. Go to the Properties > Log On tab and enter the credentials for the SPN registered user.

SPN accounts must be registered in Active Directory on a computer account or a user account. If SQL Server is running for a domain user account and if SPN is not registered for an SQL instance in Active Directory, then contact the Domain Administrator to manually register the SPN account.

When merging is enabled, the JDBC connector skips unstructured data when aggregating from the table or displays the following error message even after using order by clause:

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

Resolution: Perform the following:

1. Add the following entry in the application XML file:

   Copy

   <entry key="supportUnstructuredData"> 
      <value>
        <Boolean>true</Boolean>
      </value>
   </entry>

2. Order by clause is required in the query for aggregation as follows:

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

[Error details ] The server encountered an unexpected error while contacting target system. Please check the logs. SQL compilation error:

Database 'SAILPOINT_DB' does not exist or not authorized.

Resolution: Verify if the service user has required permission to access the database and provide the connection parameters in the connection URL along with specific roles if required.

The Requestable attribute in entitlement standard properties toggles in UI if schemaObjectType="Group" field is missing in the account schema.

Resolution: Add schemaObjectType="Group" to the account schema.

For example:

<Schema created="" displayAttribute="" id="" identityAttribute="identityName" instanceAttribute="" modified="" nativeObjectType="account" objectType="account" permissionsRemediationModificationType="None">

<AttributeDefinition schemaObjectType="Group" entitlement="true" managed="true" multi="true" name="entitlementValue" type="string">
<Description></Description>
</AttributeDefinition>

</Schema>

While using JDBC source with AAD authentication and MSAL4J java library, the following error is displayed:

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

Resolution: Upload the latest version of MSSQL JDBC jar to JDBC connector. All previous MSSQL JDBC jars before version 9.2.1 version are incompatible and cause dependent library issue errors. Along with the latest MSSQL JDBC jar, place the dependent MSAL4J lib jars in the connectors classpath for the JDBC connector to resolve them. Place all the required jars in a custom folder, and add an entry key to the application using the following:

<entry key="connector-classpath">
    <value>
        <List>
            <String>JDBCCustom</String>
        </List>
    </value>
</entry>

JDBCCustom is the folder name where the required MSSQL JDBC and dependent MSAL4J jars are placed.

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

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. In the application Debug page for the JDBC application, add the entry:

   <entry key="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 entry key in the application Debug page:

   <entry key="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 entry to the JDBC application Debug page and then try and test the connection:

<entry key="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 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 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;