# SailPoint SQL Loader Connector > SQL Loader Connector Documentation # Documentation # Aggregation After you create and test the connection, you can load accounts and entitlements, but you must first discover the schema and identify attributes to use for required identity data before loading accounts or entitlements. To learn more about Aggregation, refer to [Aggregation Overview](). ## Discovering the Schema You must discover the schema and identify the attributes to use for the required identity data before doing anything else. > [!CAUTION] > > If this source is going to be used as an authoritative source for an identity profile, the **Account Name** and **Account ID** both need to be unique. If they are not unique, the source accounts could correlate to the first Identity that includes that ID/Name instead of creating an additional Identity. **Prerequisite**: Ensure you entered an Account SQL query into the source connection. For more information, refer to [Account Query Settings](). 1. From your connector page, go to **Import Data \> Account Schema**. 2. Select **Discover**. The Account SQL Query is executed and the discovered attribute names are presented. 3. Set the appropriate attributes as Account ID and Account Name. ## Account Aggregation After you discover the schema and identify the required attributes, you can load accounts. > [!NOTE] > > When aggregating accounts or entitlements, you must have the appropriate details of the account SQL query and group SQL query for the test connection. This helps prevent aggregation failures while the test connection is successful. **Prerequisite**: Ensure you entered an account SQL query into the source connection. For more information, refer to [Account Query Settings](). 1. Go to the **Import Data** tab and start a **Manual Aggregation**. For more information, refer to [Starting a Manual Aggregation](). 2. Check the results in the Aggregation Activity log. ## Entitlement Aggregation You must add the entitlement schema using the REST API for entitlement aggregation in the SQL Loader connector. For example, you can use the following request body: ```json { "id": "string", "name": "string", "nativeObjectType": "string", "identityAttribute": "string", "displayAttribute": "string", "hierarchyAttribute": "string", "includePermissions": true, "features": [ "string" ], "configuration": {}, "attributes": [ { "name": "string", "type": "STRING", "schema": { "type": "ACCOUNT_CORRELATION_CONFIG", "id": "string", "name": "string" }, "description": "string", "isMultiValued": true, "isEntitlement": true, "isGroup": true } ], "created": "string (date-time)", "modified": "string (date-time)" } ``` ## Data Merge Support in Aggregation If the data for a single account spans multiple rows of a database table, you can merge the data for a successful aggregation. Add the `mergeRows`, `mergeColumns`, and `indexColumns` attributes using the REST API. For the information on using the REST APIs for the attributes and keys, refer to: - [API to get source details - Lists all sources in SailPoint]() - [API to get specific Source's current schema details - Lists the Schemas that exist on the specified Source in SailPoint]() - [API to add new schema Attribute to source - Creates a new Schema on the specified Source in SailPoint]() ## Direct Permissions The SQL Loader connector supports the following direct permissions: - Permissions directly assigned to accounts and groups as direct permissions during account and entitlement aggregation - Revocation of the aggregated permissions for an account through certification ### Configure Direct Permissions Perform the following steps to configure direct permissions and their aggregation, for the account or the group schema of your SQL Loader connector. > [!NOTE] > > **Screenshot** > > [![Configure Direct Permissions]()]() > > [![Configure Direct Permissions]()]() 1. In SailPoint, go to **Connections \> Sources** and select your SQL Loader connector. 2. In the connector page, go to **Edit Configuration \> Query Settings**. 3. Select the edit button for the account or group schema on which you want to enable direct permissions. 4. Select **Additional Query Settings**. 5. Toggle **Direct Permissions** on. A text field for **Direct Permission SQL** displays. 6. Enter the **Direct Permission SQL** query. For more information, refer to [Direct Permission SQL](<#direct-permission-sql>). 7. Select **Save**. ### Direct Permission SQL Direct Permission SQL is used to pull the direct permission data from a permission table. The permission table must contain at least an `Identity` attribute column. SailPoint pulls permission data using an SQL query to refer to the Identity attribute during the aggregation process. The SQL query must be written in such a way that `ResultSet` data contains `Target` as a first column and `Permission` as a second column. Optionally, you can add a third column and set it as `Annotation`. For example, if you reference a permission table named Permission and you use `$(identity)` as the identity attribute, you could use the following SQL query: ```sql SELECT column4 AS TARGET,column5 AS PERMISSION FROM Permission p WHERE CONCAT(TRIM(CONCAT(p.column1,'\ ')), TRIM(p.column2)) = '$(identity)'; ``` ## Multiple Group Objects The SQL Loader connector supports multiple group objects for Entitlement Aggregation. You can set up attributes such as entitlements and roles. You must add a new group schema and update the existing account schema, if required, using the REST APIs. For new multiple group objects, the `new_object.mergeRows` attribute must be present with `null`/`false` as a value. For example, if the new group schema object type is `role`, ensure the `role.mergeRows` attribute is present for the source. ### Group Hierarchy If the group schema has any attribute that holds the hierarchy of that group type, then you can define `hierarchyAttribute` and use `childHierarchy` to set the correct parent-child relationship. For example, you can refer the following request body for the REST API to add new schema with hierarchy attribute. Refer to the following for more information: - [API to get source details - Lists all sources in SailPoint]() - [API to add new schema Attribute to source - Creates a new Schema on the specified Source in SailPoint]() ```json { "nativeObjectType": "ROLE", "identityAttribute": "NAME", "displayAttribute": "DISPLAY_NAME", "hierarchyAttribute": "ROLES", "includePermissions": false, "features": [], "configuration": { "childHierarchy": "true" }, "attributes": [ { "name": "NAME", "type": "STRING", "schema": null, "description": "An internal name for the role", "isMulti": false, "isEntitlement": false, "isGroup": false }, { "name": "DISPLAY_NAME", "type": "STRING", "schema": null, "description": null, "isMulti": false, "isEntitlement": false, "isGroup": false }, { "name": "STATUS", "type": "STRING", "schema": null, "description": null, "isMulti": false, "isEntitlement": false, "isGroup": false }, { "name": "ROLES", "type": "STRING", "schema": null, "description": null, "isMulti": true, "isEntitlement": true, "isGroup": false } ], "id": "2c91808f79180d49017918643cb60153", "name": "ROLE", "created": "2021-01-25T12:07:25.596Z", "modified": null } ``` ## Loading a Single Account or Group Loading a single account or group is helpful when you want to check the status of an account or entitlement. The normal SQL syntax is: `Select * from where NativeIdentity = '$(identity)'` For example: `Select * from Users where UserID = '$(identity)'` - You configure the SQL query for accounts in the **Single Account SQL Query** field in [Account Query Settings](). - You configure the SQL query for groups in the **Single Group SQL Query** field in [Group Query Settings](). ### Account Status For the SQL Loader source, to set an account as enabled or disabled based on the values inside a specific column, you can add the `IIQDisabled` (string) attribute in the source account schema. For more information, refer to [Adding Attributes to a Source Schema](). The following SQL query can be used to set up the state for the account: ```sql select ... case when Active=1 then 'false' else 'true' end as IIQDisabled from ... ``` # Connecting SailPoint and SQL Loader To connect SailPoint with SQL Loader, perform the following tasks: ## Adding the Source in SailPoint To connect to SailPoint, perform the following steps. Once the connection is made, you can configure it to meet your company's needs. > [!IMPORTANT] > > By default, the provisioning feature is **disabled**, and the connector only supports read-only operations. To support provisioning operations, you must manually enable the feature in the source configuration. **To configure a new source:** 1. Sign in to SailPoint. 2. Go to **Admin \> Connections \> Sources**. 3. Select **Create New**. 4. Search for the connector by entering the name in the search box, and then select **Configure**. 5. In the **Source Name** field, enter a unique and descriptive name for your source. Source names cannot exceed 128 characters. 6. In the **Description** field, provide additional details about the source to help distinguish it from similar sources. This field allows a maximum of 2,000 characters. 7. In the **Source Owner** field, begin typing the name of an owner. Matches appear after you type two or more letters. For more information, refer to [Assigning a Source Owner](). 8. Under **Connection Type**, select **Direct Connection** or **Flat File**. - **Direct Connection** - A direct connection fetches data from the **Virtual Appliance Cluster** you choose as the application connector for the source. A direct connection configuration allows for real-time updates. For more information about configuring and deploying virtual appliances, refer to the [Virtual Appliance Reference Guide](). - **Flat File** - Select the flat file if no connector is available, or if another system restricts the connection. This option allows only reading and aggregating data as a generic/delimited application. Provisioning activity is unsupported, and additional configuration pages like connection settings and aggregation settings are unavailable. 9. If you selected **Direct Connection**, in **Virtual Appliance Cluster**, select the virtual appliance cluster that will be used as the application connector for the source. 10. (Optional) Select a governance group to grant its members [source]() or [role]() sub-admin level oversight of the source and its access. For more information, refer to [Creating and Managing Governance Groups](). 11. If this source represents a primary system containing your organization's personnel records, select the **Authoritative Source** checkbox. For more information, refer to [Creating Identity Profiles](). 12. Select **Continue**. The source is created, and you are navigated to the **Source Setup \> Base Configuration** page. Continue to configure your connected source there. ## Base Configuration Provide the base configuration parameters. Information you entered in the Create New Source dialog is automatically entered into the fields in the Base Configuration page. You can edit this information if necessary. 1. Enter a unique name and description for the source. 2. In the **Source Owner** dropdown list, select the identity that owns this source. - To find a source owner, begin entering their name. 3. In the **Virtual Appliance Cluster** dropdown list, select the virtual appliance to use when communicating with your organization's systems. For more information, refer to [SailPoint Virtual Appliances](). 4. (Optional) If enabled for your organization, select the **Use a Credential Provider** checkbox to manage the source's authentication using a credential provider. > **Note** > > To add a credential provider, go to **Admin \> Connections \> Credential Providers**. For more information about credential providers, refer to [Credential Providers](). 5. Configure the authentication to allow SailPoint to connect to the source. Refer to [Connection Settings](<#connection-settings>) for more information. 6. (Optional) In the **Governance Group for Source Management** dropdown list, select a governance group to associate with this source. For more information about governance groups, refer to [Associating Governance Groups with a Source](). 7. (Optional) Choose whether to enable or disable provisioning for the source. Provisioning is disabled by default. - **Enable Provisioning** - Set the toggle to **Enabled** to allow the source to create, update, and deactivate accounts in the connected source system. - **Disable Provisioning** - Set the toggle to **Disabled** to disable provisioning for the source. Disabling provisioning will prevent the creation, updating, or deactivating of accounts on this source. 8. Select **Save**. ## Query Settings Set up how the connector queries the SQL Loader source for account and group schema information. > [!NOTE] > > **Screenshot** > > [![Query Settings]()]() Select the edit button for the schema you want to configure to launch additional settings. ### Account Schema Query Setup Perform the following tasks to configure the queries for SQL Loader accounts. #### Connection Settings Enter the connection information to allow SailPoint to locate and connect to the SQL Loader server to load account information, as well as for any account-related functions. > [!NOTE] > > **Screenshot** > > [![Account Connection Settings]()]() 1. Enter the service account user name making the connection to the data files into the **User Name** field. 2. Enter the service account password into the **Password** field. 3. Enter the URL to the data files into the **Data Files URL** field. For more information, refer to [Data Files URL Formatting](<#data-files-url-formatting>). The address should be in the following formats, depending on the data file type: - CSV files - `jdbc:csv:ftp:///` - Text files - `jdbc:text:ftp:///` CSV file example: `jdbc:csv:ftp://myhost/home/testdata`, `jdbc:csv:ftp://000.00.00.00/home/testdata` 4. Enter the HXTT text driver used to communicate to the CSV or text file into the **Driver Class** field. For example: `com.hxtt.sql.text.TextDriver` 5. Add any additional parameters into the **Additional Connection Parameters** field in the form of a key value pair. If multiple parameters need to be specified, they need to be passed in a new line. For example: `_CSV_Header=true` `_CSV_Separator=,` > **Note** > > If you set the `_CSV_Header=` parameter to `true`, the first row of the CSV or text file is treated as a column header. If you set it to `false`, then the first row is treated as the account record. > > The value you set for the `_CSV_Separator=` parameter is used to separate the values of the fields in the CSV and text files. 6. Select **Save**. #### Account Query Settings Configure the SQL queries SailPoint uses to retrieve account data from your SQL Loader server. > [!NOTE] > > **Screenshot** > > [![Account Query Settings]()]() 1. Enter a simple SQL query to test the connection to the database in the **Test Connection SQL Query** field. 2. Enter a SQL query to load accounts into the **Account SQL Query** field. 3. Enter a SQL query to load a single account into the **Single Account SQL Query** field. 4. Select **Save**. #### Additional Account Query Settings Set additional query settings to support the SQL queries. > [!NOTE] > > **Screenshot** > > [![Additional Account Query Settings]()]() 1. Select the **Use executeQuery** checkbox to execute an SQL query for additional settings. Use `Statement.executeQuery()` instead of the default `Statement.execute()`. 2. Enable **Direct Permission** and add the SQL query. For more information, refer to [Direct Permissions](). 3. Select **Save**. ### Group Schema Query Setup Perform the following tasks to configure the queries for groups, which help with SQL Loader entitlements. > [!NOTE] > > Groups are used for entitlements. #### Group Connection Settings Enter the connection information to allow SailPoint to locate and connect to the SQL Loader server to load group information, as well as for any group-related functions. > [!NOTE] > > If these fields are left blank, the connector uses the account [Connection Settings](<#connection-settings>). > [!NOTE] > > **Screenshot** > > [![Group Connection Settings]()]() 1. Enter the **User Name** required to access the data files server. 2. Enter the **Password** associated with the user name to access the server. 3. Enter the server's address into the **Data Files URL** field. For more information, refer to [Data Files URL Formatting](<#data-files-url-formatting>). The address should be in the following format: `jdbc:dbtype://ipaddress/databasename` For example: `jdbc:oracle:thin:@172.16.23.155:1521:orcl` 4. Enter the **Driver Class**. For example: `oracle.jdbc.driver.OracleDriver` 5. Select **Save**. #### Group Query Settings Configure the SQL queries SailPoint uses to retrieve group data from your SQL Loader server. > [!NOTE] > > **Screenshot** > > [![Group Query Settings]()]() 1. Enter a SQL query to load groups into the **Group SQL Query** field. 2. Enter a SQL query to load a single group into the **Single Group SQL Query** field. 3. Select **Save**. #### Additional Group Query Settings Set additional query settings to support the SQL queries. > [!NOTE] > > **Screenshot** > > [![Additional Group Query Settings]()]() 1. Select the **Use executeQuery** checkbox to execute an SQL query for additional settings. Use `Statement.executeQuery()` instead of the default `Statement.execute()`. 2. Enable **Direct Permission** and add the SQL query. For more information, refer to [Direct Permissions](). 3. Select **Save**. ## Data Files URL Formatting Use the following table to determine the URL format you need to enter into the **Data Files URL** field in the SQL Loader **Account Connection** and **Group Connection**, based on your server protocol and file type. | Protocol Server | File Type | Example | | --- | --- | --- | | FTP server | .csv | `jdbc:csv:ftp:///` | | FTP server | .txt | `jdbc:text:ftp:///` | | SFTP server | .csv | `jdbc:csv:sftp:///` | | SFTP server | .txt | `jdbc:text:sftp:///` | | HTTP server | .csv | `jdbc:csv:http://www.` | | HTTP server | .txt | `jdbc:text:http://www.` | | HTTPS server | .csv | `jdbc:csv:https://www.` | | HTTPS server | .txt | `jdbc:text:https://www.` | ## SQL Query Example For the **Account SQL Query** and **Group SQL Query** fields, see the following join query example: The Employee.csv file has the following columns: - ID - FullName - Email - ContactNo The EmpDept.csv file has the following columns: - ID - DeptNam - Role To aggregate data from Employee.csv and EmpDept.csv, the JOIN query would be as follows: ```sql select e.ID, e.FullName, e.Email, e.ContactNo, d.DeptName, d.Role from Employee e , EmpDept d where e.ID=d.ID ``` > [!NOTE] > > When the file name is Employee.csv in the SQL query, you must mention only "Employee"; the ".csv" file extension is not required. ## Review and Test Perform a test to confirm the connection to SailPoint. 1. Go to **Review and Test**. 2. Confirm that the entries in each field are correct. If you note any mistakes, return to the section and make corrections. 3. Select **Test Connection**. ## SailPoint Application Onboarding SailPoint application onboarding enables you to easily onboard new applications and create sources following the successful onboarding of a discovery source. To use this feature, continue to **Discovered Applications** in the SailPoint user interface. For more information, refer to [Application Onboarding]() in the SailPoint SaaS documentation. # Integrating SailPoint with SQL Loader This document is designed to give specific information about the requirements and field definitions needed to get a working instance of a SQL Loader connector. The SailPoint SQL Loader connector supports read/write operations on flat file data. The flat file can be either a text file (.txt) or a CSV file (.csv). The data in these files are indexed with delimiters. This connector can handle aggregation for multiple files by defining a complex SQL query. > [!NOTE] > > The SQL Loader connector may cause intermittent timeouts during aggregation and provisioning operations for all supported protocols. ## Supported Features > [!NOTE] > > Before you can use any of the following items marked with an asterisk (\*), SailPoint must activate the feature for your site. The SailPoint connector for SQL Loader supports the following features: - Load accounts - \*Provision accounts - \*Access Certifications (certification of entitlements connected to accounts) - \*Password management - Delete accounts > [!NOTE] > > The SQL Loader source may cause intermittent timeouts during aggregation and provisioning operations for all supported protocols. For more information on features, refer to [Source Features](). > [!CAUTION] > > When configuring aggregation or provisioning queries that include a condition or a JOIN action, the column specified in the condition or JOIN cannot contain more than 19 characters. If the column has more than 19 characters, the query may return incomplete results. > > For example: > > - `SELECT t.id, t.name, u.group FROM ProvisioningUser t,ProvisioningUserGroupAssignment u where t.id=u.id` > > Where the column for calculating the condition is `id`. If this column contains a value with a length of more than 19 characters, the queries may return incomplete results. > - `SELECT u.MAIL_ADDRESS as MAIL_ADDRESS, u.ROLE_SPECIALISM as ROLE_SPECIALISM, ug.GROUP_NAME as groups FROM Users u LEFT OUTER JOIN UsersGroups ug ON u.MAIL_ADDRESS = ug.MAIL_ADDRESS ORDER BY MAIL_ADDRESS` > > Where the JOIN column is `MAIL_ADDRESS`. If this column contains a value with a length of more than 19 characters, the queries may return incomplete results. > > Concurrent provisioning operations may fail in any situation where multiple concurrent processes are trying to edit it at the same time. This is a limitation of the end repository being a flat file that does not have active processing on its end. It lacks the capability to multithread and resolve write access conflicts. ### Password Management If Password Management is enabled for your organization, you can change the password for a user of the source. After enabling password management on the source, be aware of the following: - The password policy defined in SailPoint applies to accounts on this source. - If you change the password for an application connected to this source, the change will automatically be made to the source itself. For more information on configuring password management, refer to [Password Management Overview](). ## Prerequisites - Configure at least one virtual appliance cluster and successfully test the connection. For more information, refer to the [Virtual Appliance Reference Guide](). - Identify or create a service account to access the data files. ## Supported Protocols The SQL Loader connector reads and writes data from flat file data, which uses a delimiter character to index the information. The flat file can be either a text file (.txt) or a CSV file (.csv). The SQL Loader connector supports the file transfer protocols listed in the following table when a file is in a shared or remote location and is accessible to the configured Virtual Appliance cluster. The file sharing protocol supported varies depending on the server platform in use: | File Transfer Protocol | Supported Platform | Supported Operations | | --- | --- | --- | | FTP | Linux | Read/Write | | SFTP | Linux | Read/Write | | HTTP | Linux | Read | | HTTPS | Windows and Linux | Read | | FTPS | Linux | Read/Write | > [!NOTE] > > - When using FTP, SFTP, or SFTP protocols, the connector may intermittently fail with timeout or login failed errors for aggregation and provisioning operations. > - The connector can provision values to all attributes with a length less than or equal to 19 characters. ## Required Permissions The service account (acting as an admin account) created on the following servers must have read and write permissions on the files in the given directory path: - FTP - SFTP - HTTP - HTTPS # Provisioning The SQL Loader connector supports provisioning, allowing you to create and manage accounts and entitlements in SQL Loader directly from SailPoint. **Prerequisite:** Your system is configured for provisioning. When SailPoint provisions new accounts to the source, it uses the attributes on the **Create Account** page as instructions or a template for what to include in the account. This page is also referred to as the *provisioning policy*. When new access is granted on a source where a user does not already have an account, SailPoint automatically includes account creation in the provisioning. This applies whether provisioning is started from an access request or from an automated role or lifecycle state assignment. For direct-connect sources, SailPoint automatically creates the account from this configuration. If the source is not configured as a direct-connect source, SailPoint creates and assigns a provisioning task to the source owner and includes the values for the source owner to use in manually creating the account. > [!IMPORTANT] > > This section describes the default **Create Account** configuration. SailPoint recommends that you work with Services to define a provisioning configuration specific to your company's needs. Be sure to verify large changes to the provisioning policy before implementation. Failure to do so might cause provisioning to fail. ## Insert Query Example for Provisioning For example, the Employee.CSV file has columns: `userid`, `email`, `Fullname`, and `department`. In the example, EMPLOYEE is the .csv file (EMPLOYEE.CSV) that the SQL Loader source accesses. ```sql insert into EMPLOYEE (userid,email,Fullname,department) values ('01','testjohn@testmail.com','John','HR') ``` ### Best Practices - To insert a record in a file having only the headers but no record, add a new line character after the header. - For Provisioning Rules template and examples, refer to the [Identity Security Cloud Rule Guide - JDBC Provisioning Rule]() document. # Retry Configurations > [!IMPORTANT] > > Add a list of retryable error messages to the attributes map of the source configuration. The value must be specified as a list or array even when only a single error string is provided. ## Provisioning Retry Errors > [!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. SailPoint connectors that support provisioning operations have the ability to retry a failed operation based on configurable error messages. The `retryableErrors` entry in the connector configuration is a list of strings that the connector searches for in the error message received from the managed source. If any of the configured error strings are found in the error message, the connector will attempt to retry the provisioning operation. Add a list of retryable error messages to the attributes map of the source configuration. The value must be specified as a list or array even when only a single error string is provided. If the error message contains one of the specified strings, the connector will initiate a retry attempt. However, if the configured error string is not present in the error message, the connector will consider the operation as failed and will not attempt a retry. Add the following entry in the source XML using the SailPoint REST APIs: For example, Use the SailPoint API by sending a PATCH request to the /v3/sources/{id} endpoint: `PATCH https://{orgName}.api.identitynow.com/v3/sources/:id` In the body of the PATCH, enter the retryable errors as follows: ```json [ { "op": "add", "path": "/connectorAttributes/retryableErrors", "value": [ "", "" ] } ] ``` > [!NOTE] > > Error messages containing very specific information about date/time, sequence ID and so on must be avoided. Error codes or error message substrings would be good candidates for inclusion. ## Aggregation Retry Errors Aggregation retries can be set up for each source by configuring the `aggregationRetryErrors` attribute under the `connectorAttributes` section. Send a PATCH request to the `/v3/sources/{id}` endpoint: `PATCH https://{orgName}.api.identitynow.com/v3/sources/:id` In the body of the PATCH, enter the `aggregationRetryErrors` as follows: ```json { "op": "add", "path": "/connectorAttributes/aggregationRetryErrors", "value": ["Connection reset"] } ``` In addition to specifying the aggregation retry errors, you can also configure the following options: > [!NOTE] > > **retryWaitTime** > > A string specifying the amount of time (in milliseconds) to wait before attempting a retry. The default value is 5000 milliseconds (5 seconds). > > In the body of the PATCH, enter the `retryWaitTime` as follows: > > ```json > { > "op": "add", > "path": "/connectorAttributes/retryWaitTime", > "value": "5000" > } > ``` > [!NOTE] > > **maxRetryCount** > > A string defining the maximum number of retry attempts that will be performed. The default value is 3. > > In the body of the PATCH, enter the `maxRetryCount` as follows: > > ```json > { > "op": "add", > "path": "/connectorAttributes/maxRetryCount", > "value": "3" > } > ``` # Enable Connector Logging This topic provides the steps needed to enable logging on SailPoint using REST APIs. > [!NOTE] > > This topic only applies to VA-based integrations. > [!NOTE] > > The examples provided might not match your configuration. Use the logging levels and logging classes reference at the bottom of the topic to configure your API responses to match your connector. This process is broken up into four steps: 1. Identify the Virtual Appliance (VA) cluster on which you want to enable logging. 2. Get the logging configuration for the existing VA cluster. 3. Update the VA cluster to enable logging levels. 4. Observe logging changes to the logging levels. ### Identify the Virtual Appliance Cluster Select the VA on which you need to enable logging using REST API. If you already know the VA application's `id`, proceed to [Get the Logging Configuration](<#get-the-logging-configuration>). 1. Make the following call to the REST API to return a list of available VA clusters: GET /v3/managed-clusters The REST API returns a response that looks similar to the following: ```json [ { "id": "2c9180866166b5b0016167c32ef31a66", "name": "Development Cluster", "description": "Development Cluster", "clientType": "CCG", "ccgVersion": "373_535_70.2.0", "pinnedConfig": true, "logConfiguration": null }, { "id": "2c9180846a93ce60016ab29f039944de", "name": "Cloud Cluster", "description": "Cloud Cluster", "clientType": "CCG", "ccgVersion": "373_535_70.2.0", "pinnedConfig": true, "logConfiguration": { "clientId": null, "durationMinutes": 60, "expiration": "2020-12-15T19:13:36.079Z", "rootLevel": "DEBUG", "logLevels": { "sailpoint.connector.ADLDAPConnector": "DEBUG" } } } ] ``` > **Note** > > The previous example code shows two VA clusters. One VA cluster has a log configuration and the other does not (refer to the `logConfiguration` value in each returned VA cluster). This is because one of the clusters already has logging enabled. This is just an example configuration, and your environment may not have logging enabled on a cluster. 2. Identify the VA cluster that you want to enable logging on by its name or description and record its `id` attribute. ### Get the Logging Configuration Get your selected VA's cluster logging configuration and identify the values you need to update. 1. Make the following call to the REST API to return the VA cluster's logging configuration. Replace `{id}` with the VA cluster's `id` attribute value recorded earlier. GET /v3/managed-clusters/{id}/log-config - If the VA doesn't have a logging configuration currently set, it returns `204 No Content` as a response. - If the VA has an existing logging configuration, it returns something similar to the following: ```json { "clientId": null, "durationMinutes": 60, "expiration": "2020-12-15T19:13:36.079Z", "rootLevel": "DEBUG", "logLevels": { "sailpoint.connector.ADLDAPConnector": "DEBUG" } } ``` You'll need to configure the following information in the response: - `durationMinutes` is the number of minutes logging should remain enabled. - `rootLevel` is the default level for all logs. For more information, refer to [Logging Levels](<#logging-levels>). - `logLevels` is a map object with logging classes as keys and logging levels as values. - For keys, refer to [Logging Classes](<#logging-classes>) for commonly used logging classes. - For a list of available values, refer to [Logging Levels](<#logging-levels>). ### Update Logging Configuration Update the VA cluster configuration returned by the REST API to update the logging settings as needed. 1. Make the following call to the REST API to update the VA cluster's logging configuration. Replace `{id}` with the VA cluster's `id` attribute value recorded earlier. PUT /v3/managed-clusters/{id}/log-config 2. Submit your configuration changes to the REST API in a JSON body. - If the VA cluster did not already have a logging configuration and you received a `204 No Content` response, you can create a new one. - If the VA returned a configuration, you can edit it to include the changes. The JSON body should be as follows: ```json { "durationMinutes": 60, "rootLevel": "DEBUG", "logLevels": { "sailpoint.connector.ADLDAPConnector": "DEBUG" } } ``` Consider the following as you edit the JSON: - `durationMinutes` is the number of minutes logging should remain enabled. - `rootLevel` is the default level for all logs. For more information, refer to [Logging Levels](<#logging-levels>). - `logLevels` is a map object with logging classes as keys and logging levels as values. - For keys, refer to [Logging Classes](<#logging-classes>) for commonly used logging classes. - For a list of available values, refer to [Logging Levels](<#logging-levels>). If the update was successful, the REST API should return a response similar to the following: ```json { "clientId": null, "durationMinutes": 60, "expiration": "2020-12-15T19:13:36.079Z", "rootLevel": "DEBUG", "logLevels": { "sailpoint.connector.ADLDAPConnector": "DEBUG" } } ``` ### Observe Logging Changes Approximately five minutes after you update the configuration, the VA cluster starts generating log files you can review. You can view the log files in **ccg.log**. ### References The tables in this reference section list information that can be helpful when creating the JSON body you'll use to configure the VA cluster's configuration file. #### Logging Levels | Logging Level | Description | | --- | --- | | ERROR | Designates error events that might still allow the connector to continue. | | WARN | Designates potentially harmful situations. | | INFO | Designates information messages that highlight process at a coarse-grain level. | | DEBUG | Very verbose. Fine grain logging levels used for development and debugging. | | TRACE | Most verbose. Finer grain logging levels than debugging. | #### Logging Classes | Connector | Logging Class | | --- | --- | | Active Directory | sailpoint.connector.ADLDAPConnector sailpoint.connector.LDAPConnector | | AIX | openconnector.connector.unix.AIXConnector openconnector.connector.unix.UnixConnector | | Amazon Web Services | logger.aws.name=openconnector.connector.aws logger.aws.level=trace logger.aws.additivity = false logger.aws.appenderRef.rolling.ref = STDOUT | | Atlassian Suite Cloud | sailpoint.connector.OpenConnectorAdapter openconnector.connector.atlassian.AtlassianConnector | | Atlassian Data Center | sailpoint.connector.OpenConnectorAdapter openconnector.connector.atlassian.AtlassianDataCenterConnector | | Microsoft Entra ID | sailpoint.connector.AzureADConnector | | Box | openconnector.connector.BoxNetConnector sailpoint.connector.OpenConnectorAdapter | | Cerner | openconnector.connector.CernerConnector sailpoint.connector.OpenConnectorAdapter | | Delimited File | logger.DelimitedFileConnector.name = sailpoint.connector.DelimitedFileConnector logger.DelimitedFileConnector.level = debug logger.DelimitedFileConnector.additivity = false logger.DelimitedFileConnector.appenderRef.rolling.ref = STDOUT | | Dropbox | openconnector.connector.DropBoxConnector sailpoint.connector.OpenConnectorAdapter | | Epic | openconnector.connector.EPICConnector sailpoint.connector.OpenConnectorAdapter | | GE Centricity | openconnector.connector.GECentricity sailpoint.connector.OpenConnectorAdapter | | Google Apps / G Suite | openconnector.connector.GoogleAppsDirect sailpoint.connector.OpenConnectorAdapter | | IBM i | openconnector.connector.IBMiConnector | | Lotus Domino | sailpoint.connector.LotusDomino | | JDBC | sailpoint.connector.JDBCConnector | | Sun One LDAP | sailpoint.connector.LDAPConnector | | Linux | openconnector.connector.unix.LinuxConnector sailpoint.connector.OpenConnectorAdapter sailpoint.connector.UnixConnector | | Microsoft Office365 | sailpoint.connector.Office365Connector | | Microsoft SQL Server | sailpoint.connector.mssql.serviceimpl.MSSQLServerConnectorV2 | | Okta | logger.openconnector.name = openconnector.connector.okta.OktaConnector logger.openconnector.level = debug logger.openconnector.additivity = false logger.openconnector.appenderRef.rolling.ref = STDOUT | | Oracle Database | sailpoint.connector.OracleDBConnector sailpoint.connector.JDBCConnector | | Oracle E-Business | sailpoint.connector.OracleEBSConnector sailpoint.connector.JDBCConnector | | Oracle Fusion HCM | openconnector.connector.oraclefusionhcm.OracleFusionHCMConnector sailpoint.connector.OpenConnectorAdapter | | Oracle HCM Cloud | sailpoint.connector.oraclehcmcloud.OracleHCMCloudConnector | | Oracle HRMS | sailpoint.connector.OracleAppsHRMSConnector sailpoint.connector.JDBCConnector | | Oracle Netsuite | openconnector.connector.netsuite.NetsuiteConnector | | PeopleSoft ERP | openconnector.connector.PeopleSoftConnector sailpoint.connector.OpenConnectorAdapter | | PeopleSoft HRMS | sailpoint.connector.PeopleSoftHRMSConnector | | RemedyForce | sailpoint.connector.ForceConnector | | RSA Authentication Manager | openconnector.connector.RSAConnector openconnector.connector.rsa.RSAHTTPClient sailpoint.connector.OpenConnectorAdapter | | Salesforce | sailpoint.connector.ForceConnector | | SAP Concur | openconnector.connector.sapconcur.SAPConcurConnector | | SAP - Direct | sailpoint.connector.SAPConnector sailpoint.connector.SAPInternalConnector | | SAP GRC | logger.SAPGRCConnector.name = sailpoint.connector.sapgrc logger.SAPGRCConnector.level = debug logger.SAPGRCConnector.additivity = false logger.SAPGRCConnector.appenderRef.rolling.ref = STDOUT | | SAP HR/HCM | sailpoint.connector.SAPHRInternalConnector | | SCIM 1.1 | openconnector.connector.SCIMConnector sailpoint.connector.OpenConnectorAdapter | | SCIM 2.0 | openconnector.connector.scim2.SCIM2Connector sailpoint.connector.OpenConnectorAdapter | | ServiceNow | openconnector.connector.servicenow.ServiceNowConnector openconnector.connector.servicenow.ServiceNowRestClient sailpoint.connector.OpenConnectorAdapter | | ServiceNow Service Desk Integration | openconnector.connector.servicedesk.ServiceNowServiceDeskConnector sailpoint.connector.OpenConnectorAdapter | | Siebel | openconnector.connector.SiebelConnector sailpoint.connector.OpenConnectorAdapter | | Snowflake | openconnector.connector.snowflake.SnowflakeConnector sailpoint.connector.OpenConnectorAdapter | | Solaris | openconnector.connector.unix.SolarisConnector openconnector.connector.unix.UnixConnector sailpoint.connector.OpenConnectorAdapter | | SuccessFactors | sailpoint.connector.SuccessFactorsConnector | | Sybase | sailpoint.connector.SybaseDirectConnector sailpoint.connector.JDBCConnector | | Webex | openconnector.connector.Webex sailpoint.connector.OpenConnectorAdapter | | Workday | openconnector.connector.WorkDay sailpoint.connector.OpenConnectorAdapter | | Workday Accounts | openconnector.connector.workdayaccounts openconnector.connector.workdayaccounts.WorkdayAccountsConnector sailpoint.connector.OpenConnectorAdapter | | Web Services | sailpoint.connector.WebServices | | XML | openconnector.connector.XMLConnector sailpoint.connector.OpenConnectorAdapter | | Yammer | openconnector.connector.YammerConnector sailpoint.connector.OpenConnectorAdapter | | Zoom | openconnector.connector.zoom.ZoomConnector sailpoint.connector.OpenConnectorAdapter | # Troubleshooting If you encounter any of the following issues or errors, SailPoint recommends that you follow the guidance provided below to resolve the error before contacting SailPoint Support. For issues or errors that are not addressed in this connector guide, refer to the following resources for troubleshooting information: - [SailPoint Identity Services Documentation]() - [SailPoint Support Knowledge Base]() - [SailPoint Developer Community]() ## During Aggregation, New Columns Are Added, but the Value Doesn't Update **Resolution**: Update the aggregation query with the newly added column name. ## Spaces Are Added to CSV and Text Files During Provisioning **Resolution**: To avoid adding spaces into the CSV or text files while performing provisioning operations, add the following query in the corresponding rule: ```java String query1 ="pack table table_name"; stmt.execute(query1); ``` ## Table Error The following error message displays: `Failed to load table EMP: java.io.FileNotFoundException: /home/sailpoint/data/EMP.csv (Read-only file system)` **Resolution**: Ensure that the file has the required permissions. ## Provisioning Error The disabled account was deleted from the managed accounts after running performance maintenance task. **Resolution**: You must add the `getObjectSQL` query because the source fetches the provisioned account again from the CSV file using the `getObjectSQL` query after the provisioning operation. ## Provisioning Rule Error In the absence of a provisioning rule, when you start the Create Account operation, the following error displays: `Rule: jdbcProvisionRule not found` **Resolution**: Configure a provisioning rule for the source. ## Aggregation Error The following message displays during aggregation: `Error - Exception during aggregation. Reason: Missing group schema for application SQLLoaderCSV [source-ID]` **Resolution**: Check if a group schema is present. Contact the SailPoint services for validation. ## Test Connection Fails for an Incorrect Text File The following error message displays for an incorrect text file: `Driver is not valid The table structure of Text Table Directory_path\test_file.txt is unknown. Please use CREATE TABLE to define a table structure once.` **Resolution**: Ensure that the following additional properties are used: - `csvfileExtension=txt` - Text in the `jdbc:text/ftp://` string. ## Timeout Error During Aggregation or Test Connection During aggregation processes or test connections, the connector returns the following error, or something similar: `Error: Timeout Interrupted Exception: beyond 1000 ms to open a table` **Resolution**: Add the `lockTimeout` attribute to the SQL Loader source configuration as an Additional Connection Parameter. Refer to [Connection Settings](). 1. Go to **Source Setup \> Query Settings \> account \> Connection Settings**. 2. In the **Additional Connection Parameter** field, enter the following: `lockTimeout=30000` > **Note** > > SailPoint recommends a setting of at least 30000 ms for remote connections. For more information, refer to the [HXTT document]() regarding the `lockTimeout` attribute. 3. Select **Save**. ## SFTP Server Test Connection Fails with Incorrect Data Files URL Error Error message: `[ ConnectionFailedException ] [ Possible suggestions ] Verify that location provided in Data Files URL is correct` **Resolution**: Confirm that the data files URL entry is correct. If the data files are in the default folder, be sure to provide the complete path. If it is a relative folder, specify the path as a relative path. The URL is in the following format: `jdbc:csv:/sftp://>//` For example: `jdbc:csv:/sftp://awsnedcidm091.na.corp.ipgnetwork.com/home/applications/test/` `jdbc:csv:/sftp://awsnedcidm091.na.corp.ipgnetwork.com/../../opt/isc/applications/test/` ## Account Aggregation Fails for Connection Parameters Being Cached in Pool **Resolution**: By default, pooling is disabled for test connections. For all other operations add the `pool.disablePooling` attribute and set the value to `true` using REST APIs. ## When Merging Is Enabled, the SQL Loader Connector Skips Unstructured Data When Aggregating from the Source File When merging is enabled, the SQL Loader connector skips unstructured data when aggregating from the source file or displays the following error message even after data is sorted in the CSV file: `Exception during aggregation. Reason: java.lang.RuntimeException: sailpoint.connector.ConnectorException: Data out of order exception. Data should be sorted in ascending order` **Resolution**: Do one of the following: - Add the following entry in the SQL Loader source XML file: ```xml true ``` - The CSV file must be sorted. - Order by clause is required in the query for aggregation as follows: `"select index_column,* from order by COLLATE(index_column,'GERMAN')"` ## Special Characters Like Ü, €, ö, ä, 管理 Don't Display Properly After Aggregation **Resolution**: Ensure the encoding type of the CSV file is set to UTF-8. If not, change the encoding type to UTF-8, save it, and retry aggregation. # Timeout Errors and Settings Timeout errors might occur when SailPoint is waiting for a response from the Virtual Appliance (VA). These errors are applicable to all connectors and tend to occur during the setup of sources or when sources are down. This document is designed to give specific information that can be used to resolve these errors. Each timeout attribute has a default time value that is listed in seconds or minutes. Increasing the time value may resolve the error as it will allow for longer communication time between the two systems, thus avoiding the timeout error. > [!IMPORTANT] > > Add the necessary attributes or values before performing the "replace" operation, as missing attributes will cause the operation to fail. To increase the communication time, increase the value of the appropriate timeout attribute. Then submit the change to the VA using the SailPoint 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. ### aggregateTimeout **Description:** This parameter is set for the aggregation timeout, measured in seconds. **Resolution:** Increase the `aggregateTimeout` value gradually to optimize aggregation. Start with 180, then increase to 600, 900, and finally 1800. The maximum value is 3600 seconds. For example: If your current time value is 180 seconds, update the `aggregateTimeout` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/aggregateTimeout", "value": 600 } ] ``` ### healthCheckTimeout **Description:** This parameter is set for the test connection and health check timeout. **Resolution:** The default value is 30 seconds. If the timeout error continues, increase the value in 15 second increments and try again. For example: If your current time value is 30 seconds, update the `healthCheckTimeout` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/healthCheckTimeout", "value": 45 } ] ``` ### provisioningTimeout **Description:** This parameter is set for provisioning timeout. **Resolution:** Increase the `provisioningTimeout` value gradually to optimize provisioning. Start with 180, then increase to 360, and finally 600. The maximum value is 600 seconds. For example: If your current time value is 180 seconds, update the `provisioningTimeout` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/provisioningTimeout", "value": 360 } ] ``` ### timeout **Description:** This parameter is set for pass-through authentication timeout. **Resolution:** The default value is 15 seconds. If the timeout error continues, increase this value in 15 second increments and try again. For example: If your current time value is 15 seconds, update the `timeout` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/timeout", "value": 30 } ] ``` ### apiTimeout **Description:** This parameter is set for API time tracking; customers can check it using an external tool. **Resolution:** The default value is 60 seconds. If the error continues, increase the value by 15 second increments and try again. For example: If your current time value is 60 seconds, update the `apiTimeout` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/apiTimeout", "value": "75" } ] ``` ### timeoutInSeconds **Description:** This parameter helps prevent the VA from waiting indefinitely for an API response, ensuring efficient handling of API requests. For example: If your current time value is 30 seconds, update the `timeoutInSeconds` attribute as follows: ```json [ { "op": "replace", "path": "/connectorAttributes/timeoutInSeconds", "value": "15" } ] ```