# SailPoint JDBC Connector Documentation

> JDBC Connector Documentation

# Documentation

# Account Schema Query Setup

Set up how the connector queries the JDBC source for account and/or group schema information.

> [!NOTE]
>
> **Screenshot**
>
> [![Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_settings.png>)

Select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_configure_button.png>) for the schema you want to configure to launch additional settings.

Perform the following tasks to configure the queries for JDBC accounts.

## Account Connection Settings

Configure how SailPoint connects to the JDBC server to load account information.

### Account Connection Settings for Other Database

Enter the connection information to allow SailPoint to locate and connect to the JDBC server to load account information.

> [!NOTE]
>
> **Screenshot**
>
> [![Account Connection Settings for Other Database](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_other.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_other.png>)

1. Select **Other** from the **Database Name** dropdown.

   > **Note**
   >
   > Other is selected by default. If another value is selected, select **Other**.
2. Enter the **User Name** required to access the data files server.
3. Enter the **Password** associated with the user name to access the server.
4. Enter the server's address into the **Database URL** field. The generic format is as follows:

   ```text
   jdbc:<database_type>://<host>:<port>/<database_name>
   ```

   Substitute the values for your database in the bolded text. For example:

   ```text
   jdbc:snowflake://sailpoint-conn.snowflakecomputing.com
   ```

   > **Note**
   >
   > The JDBC connector is a generic connector that supports connecting to a database, provided that the database has a JDBC driver available.

   ### For Windows Authentication

   To properly support Windows authentication, the virtual appliance must be able to resolve the SQL server host, the key distribution center (KDC), and the default Kerberos realm. For more information about configuring Windows authentication, refer to [Windows Authentication and Kerberos](<https://documentation.sailpoint.com/connectors/jdbc/va/help/connecting.html#windows-authentication-and-kerberos>).

   > **Important**
   >
   > To use Windows authentication, you must upload SQLJDBCDriver.config and krb5.config files. For more information, refer to [Connector Files](<https://documentation.sailpoint.com/connectors/jdbc/va/help/connecting.html#connector-files>).

   Connections using Windows authentication are supported for JDBC drivers only.

   ```text
   jdbc:sqlserver://><[serverName[\instanceName][:portNumber]];integratedSecurity=true;authenticationScheme=JavaKerberos;trustServerCertificate=true;
   ```

   Consider the following for this URL:

   - `jdbc:sqlserver` - (Required) This is known as the sub-protocol and is constant.
   - `serverName` - The fully qualified domain name of the SQL server host machine. This is typically in the following format:

     ```text
     <hostname>.<domain_name>
     ```

     For example, if the SQL server host is MYSQLSERVER and the domain is EXAMPLE.COM, your entry would be as follows:

     ```text
     MYSQLSERVER.EXAMPLE.COM
     ```
   - `instanceName` - The instance used to connect to `serverName`.
   - `portNumber` - The port used to connect to `serverName`.
   - `authenticationScheme` - Used for integrated authentication using Kerberos in addition to the `integratedSecurity` connection property.

   For example:

   ```text
   jdbc:sqlserver://PUWSQLSERVER.SQLKERB.LAB;databaseName=master;trustServerCertificate=true;integratedSecurity=true;authenticationScheme=JavaKerberos;
   ```

   Alternatively, you can establish a connection without specifying the `databaseName` in the URL. If you do so, the connection is made to the default database. For example:

   ```text
   jdbc:sqlserver://PUWSQLSERVER.SQLKERB.LAB;trustServerCertificate=true;integratedSecurity=true;authentication
   ```
5. Enter the **Driver Class** path according to database you want to connect. For example:

   ```text
   net.snowflake.client.jdbc.SnowflakeDriver
   ```
6. (Optional) In the **Additional Connection Parameters** field, enter the parameters to be passed in the form of key-value pair. If you need to specify multiple parameters, press the Enter key after adding each value. For example:

   ```text
   resultSetType= TYPE_FORWARD_ONLY
   db=TEST
   schema=PUBLIC
   ```
7. Select **Save**.

### Account Connection Settings for Snowflake

Enter the connection information to allow SailPoint to locate and connect to the JDBC server to load account information from a Snowflake database.

> [!NOTE]
>
> Upload the Snowflake JDBC driver before you configure connection settings. For more information, refer to [Connector Files](<https://documentation.sailpoint.com/connectors/jdbc/va/help/connecting.html#connector-files>). SailPoint requires Snowflake JDBC driver version 3.24.0 or later.

**To configure account connection settings, complete the following steps:**

1. Select **Snowflake** from the **Database Name** dropdown.
2. Enter the **User Name** required to access the data files server. Enter the user name that matches the authentication type you select. For OAuth 2.0, enter the `LOGIN_NAME` value from Snowflake.
3. In the **Database URL** field, provide the JDBC connection string corresponding to the database with which to connect. The generic format is as follows:

   ```text
   jdbc:dbtype:@ipaddress:databasename
   ```

   For example:

   ```text
   jdbc:snowflake://test.snowflakecomputing.com
   ```
4. Enter the **Driver Class** path according to the database you want to connect. For example:

   ```text
   net.snowflake.client.jdbc.SnowflakeDriver
   ```
5. Select an **Authentication Type**.

   1. OAuth 2.0 Authentication

      > **Screenshot**
      >
      > [![Snowflake OAuth 2.0 Authentication](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_snowflake_auth.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_snowflake_auth.png>)

      Complete the following steps when you select **OAuth 2.0** as the authentication type. For more information about configuring the external identity provider, refer to [Configuring an External IdP for OAuth 2.0](<https://documentation.sailpoint.com/connectors/jdbc/va/help/index.html#configuring-an-external-idp-for-oauth-20>).

      1. Select **Client Credentials** as the **Grant Type**.

         > **Note**
         >
         > The JDBC connector supports only the Client Credentials grant type for Snowflake OAuth 2.0.
      2. Enter the **Token URL** required to obtain the access token.
      3. Enter the **Client ID** required for authentication.
      4. Enter the **Client Secret** required for authentication.
      5. Enter the **OAuth2 Scope** required to access the server.
   2. Key Pair

      > **Screenshot**
      >
      > [![Snowflake Key Pair Authentication](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_snowflake_keypair.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_conn_snowflake_keypair.png>)

      Complete the following steps when you select **Key Pair** as the authentication type.

      1. Enter the **Private Key** used to authenticate with Snowflake. For more information about generating private keys, refer to [Generating Keys for Snowflake](<https://documentation.sailpoint.com/connectors/jdbc/va/help/index.html#generating-keys-for-snowflake>).
      2. Enter the **Passphrase** to validate the private key. Enter the passphrase you created when you generated the public and private keys.
6. (Optional) In the **Additional Connection Parameters** field, enter the parameters to be passed in the form of key-value pair. If you need to specify multiple parameters, press the Enter key after adding each value. For example:

   ```text
   <truststorefileparameter>=<pathoftruststore>
   <truststoretypeparameter>=<truststoretype>
   ```
7. Select **Save**.

## Account Query Settings

Configure the SQL queries SailPoint uses to retrieve account data from your JDBC server.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query1_1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query1_1.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query2.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query3.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_query3.png>)

1. (Optional) Enter a simple SQL query to test the connection to the database in the **Test Connection SQL Query** field. Select the **Use Stored Procedure for Test Connection SQL Query** checkbox to execute a stored procedure on the database.
2. Enter an SQL query to load all accounts into the **Account SQL Query** field. For example, you can use the following:

   ```sql
   SELECT userid, fname, lname FROM users
   ```

   Select the **Use Stored Procedure for Account SQL Query** checkbox to execute a stored procedure on the database.
3. (Optional) Enter an SQL query to load a single account into the **Single Account SQL Query** field. This query may match the Account SQL Query, but the single account query includes a `WHERE` clause to filter results for one specific user. To accomplish this, the query uses the `$(identity)` placeholder which the connector dynamically replaces with the actual NativeIdentity of the user at runtime. For example, if your NativeIdentity is `userid`, you can use the following:

   ```sql
   SELECT userid, fname, lname FROM users WHERE userid = '$(identity)'
   ```

   Select the **Use Stored Procedure for Single Account SQL Query** checkbox to execute a stored procedure on the database.
4. Select **Save**.

## Additional Query Settings

Set additional query settings to support the SQL queries.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Additional Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_addquery_va.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_addquery_va.png>)

1. (Optional) Select the **Use executeQuery** checkbox to return a single ResultSet object when it queries the JDBC database.
2. (Optional) Enable **Direct Permission** and add the **Direct Permission SQL** query. For more information on supported direct permissions, refer to [Direct Permissions](<#direct-permissions>).

   The **Direct Permission Execute Query** 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 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)'
   ```

   Another example:

   ```sql
   select TABLENAME,PRIVILEGES from JDBC_SNOWFLAKE.PUBLIC.PERM where grantee='$(identity)';
   ```

   Select the **Use Stored Procedure for Single Account SQL Query** checkbox to execute a stored procedure on the database.
3. Select **Save**.

### Direct Permissions

The JDBC source supports:

- 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.

## Account Delta Aggregation

Enable Delta Aggregation when SailPoint queries the JDBC database. Delta Aggregation enables SailPoint to only update new information since the previous data query.

> [!IMPORTANT]
>
> You must configure a delta aggregation table for delta aggregation to work. For more information, refer to [Delta Aggregation Tables](<https://documentation.sailpoint.com/connectors/jdbc/va/help/aggregation.html#delta-aggregation-tables>).

> [!NOTE]
>
> **Screenshot**
>
> [![Delta Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_deltaagg.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_deltaagg.png>)

1. In the JDBC source configuration, go to **Query Settings**, select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_configure_button.png>) for the schema you want to configure, and then select **Delta Aggregation**.
2. Select **Enable Delta Aggregation** to enable delta aggregation.
3. (This field is only available when delta aggregation is enabled) In the **Database Table Name that Contains the Delta Changes** field, provide the table name in which delta changes are captured.
4. Use the **Delta Aggregation SQL** field when the alias is used in the main SQL. Example of delta aggregation SQL query:

   ```sql
   Select * from [masterTable] where IdentityAttribute = '$(identity)'
   ```

   Where `masterTable` is the table name that is being used in the main SQL, and `IdentityAttribute` is the identity attribute defined in the masterTable schema. For more information, refer to [Delta Aggregation Tables](<https://documentation.sailpoint.com/connectors/jdbc/va/help/aggregation.html#delta-aggregation-tables>).
5. Select the **Use Stored Procedure** checkbox to execute a stored procedure on the database.
6. Select **Save**.

## Provisioning Query Settings

Provisioning can be carried out in two ways:

- Using provisioning rules. For more information, refer to [Provisioning Rules](<https://documentation.sailpoint.com/connectors/jdbc/va/help/provisioning.html#provisioning-rules>).
- Create SQL queries or stored procedures to configure provisioning operations.

This topic describes how to configure provisioning operations using SQL queries or stored procedures for JDBC sources.

### Prerequisites

To support this feature, be sure to enable **Configure Provisioning Operations in the Connector** in the source configuration pages.

### Supported Functions

This feature supports the following functionality.

- Single SQL query execution
- Multiple SQL query execution
- Stored procedures

  For example:

  Call `procedure_name($plan.attribute1$, $plan.attribute2$)`
- Inline variables for defined queries

  Defined queries support inline variables from the provisioning plan using the `$plan.fieldname.fieldtype$` variable syntax.
- Response items from priorly executed queries in the same SQL query set

  Queries can use response items from the result set of prior queries, via the `$response.fieldname.fieldtype$` variable syntax.
- Type safety

  The provisioning plan and prior-query response objects may be restricted to specific data types.

  If a data type isn't defined, the field is treated as a string. Define a data type using the following entry:

  ```text
  $plan.fieldname.type$
  ```

  For example:

  - `$plan.firstName.string$`
  - `$response.id.int$`

  The connector supports the following data types:

  - String - `Str`, `Varchar`
  - Integer - `Int`
  - Boolean - `Bool`
  - Date - `Date`
  - Double - `Dbl`
  - Float - `Flt`

  > **Note**
  >
  > Data type definitions are case-insensitive

### Configure Provisioning Operations as Part of the Connector Configuration

Create SQL queries and stored procedures to handle provisioning in the JDBC connector's configuration interface.

### Create Account

Select **Create Account** to provide an SQL query or stored procedure to create an account. Enter the SQL query or stored procedure into the **SQL Query or Stored Procedure** field as appropriate.

#### SQL Query

The SQL query format is as follows:

```sql
insert into databasename.tablename (column1, column2) values ($plan.attribute1$, $plan.attribute2$)
```

For example:

```sql
insert into Employee (PersonID, FirstName) values ($plan.PersonID$, $plan.FirstName$)
```

If the native identity is included in the provisioning plan:

```sql
insert into Employee (PersonID, FirstName) values ($plan.nativeIdentity$, $plan.FirstName$)
```

If the native identifier is not included in the Provisioning Plan, enter an SQL query to pull the native identifier into the **Get Native Identifier SQL Query** field.

For example, `PersonID` is returned as the native identifier in the following SQL query:

```sql
select PersonID from Persons where firstName = $plan.firstName$
```

#### Stored Procedure

The stored procedure format is as follows:

```sql
EXEC JDBCSWAP.dbo.InsertEmployee @Name = $plan.name$, @City = $plan.city$, @Salary = $plan.salary$
```

#### Prerequisites

To support this feature, you must set up a provisioning policy and define the account attribute mappings as required. When SailPoint provisions new accounts to a directly connected JDBC source, it uses the attributes in the provisioning policy as a set of instructions or a template to create a new account.

#### Add Entitlement

Select **Add Entitlement** to add SQL queries which add entitlements during create operations.

1. Under **Add Entitlement**, select **Add**.
2. In the **Entitlement Type** field, add the entitlement key in the following format:

   ```text
   <entitlement attribute name>.addEntitlementSQL
   ```

   For example, if the entitlement attribute in the account schema is `Roles`, add the following:

   ```text
   Roles.addEntitlementSQL
   ```
3. In the **Query** field, add an SQL query or a stored procedure to add the `Roles` entitlement to an account.

   - SQL Query

     - If the attribute is included in the Provisioning Plan:

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.attribute1$, $plan.Roles$)
       ```

       Or

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.nativeIdentity$, $plan.Roles$)
       ```
     - If the attribute is not included in the Provisioning Plan, you can fetch it using a `SELECT` query, then insert the response of the first query into the second query:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       insert into databasename.tablename (attribute1,attribute2) values ($response.attribute$, $plan.Roles$)
       ```
   - Store Procedure

     ```sql
     EXEC HR.dbo.InsertEmployeeRole @EmpId = $plan.nativeIdentity$, @RoleId = $plan.Roles$
     ```
4. Repeat this process for multiple entitlement types.

### Update Account

Select **Update Account** to enter the name of a table and its database to update according to the Provisioning Plan. The connector forms a full SQL query using the attributes defined in the Provisioning Plan.

The query is as follows:

```sql
Update <database_name.table_name>
```

For example, to update `sales_accounts` table in the `human_resources` database with an email attribute and an associated value defined in the Provisioning Plan, enter:

```sql
Update human_resources.sales_accounts
```

The connector generates the following SQL query based on the Provisioning Plan:

```sql
Update human_resources.sales_accounts set Email=<value_defined_in_provisioning_plan>
```

> [!IMPORTANT]
>
> You only need to enter the name of the table and its database for this query. Changes to the information you want to update should be made in the Provisioning Plan.

If the schema attribute name is different than the database column name, map the schema attribute name to its associated database column name in the **Attribute Database Mappings** field. Select **Add** to create a new mapping by adding the schema attribute name and its associated database column name.

#### Prerequisites

To support this feature, you must ensure that **Attribute Sync** is enabled in the source for the attributes you need to change.

#### Add Entitlements

Select **Add Entitlement** to add SQL queries to add entitlements during update operations.

1. Under **Add Entitlement**, select **Add**.
2. In the **Entitlement Type** field, add the entitlement key in the following format:

   ```text
   <entitlement attribute name>.addEntitlementSQL
   ```

   For example, if the entitlement attribute in the account schema is `Roles`, add the following:

   ```text
   Roles.addEntitlementSQL
   ```
3. In the **Query** field, add an SQL query or a stored procedure to add the `Roles` entitlement to an account.

   - SQL Query

     - If the attribute is included in the Provisioning Plan:

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.attribute1$, $plan.Roles$)
       ```

       Or

       ```sql
       insert into databasename.tablename (attribute1,attribute2) values ($plan.nativeIdentity$, $plan.Roles$)
       ```
     - If the attribute is not included in the Provisioning Plan, you can fetch it using a `SELECT` query, then insert the response of the first query into the second query:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       insert into databasename.tablename (attribute1,attribute2) values ($response.attribute$, $plan.Roles$)
       ```
   - Store Procedure

     ```sql
     EXEC HR.dbo.InsertEmployeeRole @EmpId = $plan.nativeIdentity$, @RoleId = $plan.Roles$
     ```
4. Repeat this process for multiple entitlement types.

#### Remove Entitlements

Select **Remove Entitlement** to add SQL queries to remove entitlements during updated account operations.

1. Under **Remove Entitlement**, select **Add**.
2. In the **Entitlement Type** field, add the entitlement key in the following format:

   ```text
   <entitlement attribute name>.removeEntitlementSQL
   ```

   For example, if the entitlement attribute in the account schema is `Roles`, add the following:

   ```text
   Roles.removeEntitlementSQL
   ```
3. In the **Query** field, add an SQL query to remove the `Roles` entitlement from an account.

   - SQL Query

     - If the attribute is included in the Provisioning Plan:

       ```sql
       delete from databasename.tablename where attribute1 = $plan.nativeIdentity$ and attribute2 = $plan.Roles$
       ```

       Or

       ```sql
       delete from databasename.tablename where attribute1 = $plan.attribute1$ and attribute2 = $plan.Roles$
       ```
     - If the attribute is not present in the Provisioning Plan, you can fetch it using a `SELECT` query, then insert the response of the first query into the second query:

       ```sql
       SELECT attribute FROM databasename.tablename WHERE fname = $plan.attribute1$;
       delete from databasename.tablename where attribute1 = $response.attribute.int$ and attribute2 = $plan.Roles$
       ```
   - Stored Procedure

     ```sql
     EXEC DeleteEmployeeRole @EmpId = $plan.nativeIdentity$, @RoleId = $plan.Roles$
     ```
4. Repeat this process for multiple entitlement types.

### Enable, Disable, and Unlock Account

Select **Enable Account**, **Disable Account**, or **Unlock Account** to provide SQL queries or stored procedures to enable or disable accounts. Provide the query or stored procedure according to the column which defines the account Enable, Disabled, and Locked statuses.

For example:

Query:

```sql
update DatabaseName.dbo.[TableName] set AttributeName='value' where AccountID Attribute Name=$plan.nativeIdentity$
```

Stored Procedure:

```sql
EXEC StoredPrecedureName @VariableName = $plan.nativeIdentity$
```

#### Use Case Examples

This section lists examples the SQL queries and stored procedures to enable, disable, and unlock accounts.

##### Enable Account Examples

Query:

```sql
update HR.dbo.[Employee] set isactive='1'where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
EXEC HR.dbo.UpdateEmployeeStatusEnable @EmpID = $plan.nativeIdentity$
```

##### Disable Account Examples

Query:

```sql
update HR.dbo.[Employee] set isactive='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
EXEC HR.dbo.UpdateEmployeeStatusDisable @EmpID =$plan.nativeIdentity$
```

##### Unlock Account Examples

Query:

```sql
update HR.dbo.[Employee] set islock='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
EXEC HR.dbo.UpdateEmployeeLockStatus @EmpID =$plan.nativeIdentity$
```

### Delete Account

Select **Delete Account** to provide an SQL query or stored procedure to delete an account. Enter the SQL query or stored procedure into the **SQL Query or Stored Procedure** field as appropriate.

#### SQL Query

The SQL query format is as follows:

```sql
DELETE FROM DatabaseName.dbo.[TableName]
WHERE AccountID = $plan.nativeIdentity$;
```

#### Stored Procedure

The stored procedure format is as follows:

```sql
EXEC StoredProcedureName @VariableName =
$plan.nativeIdentity$;
```

#### Use Case Examples

##### SQL Query

```sql
DELETE FROM HR.dbo.[Employee] WHERE empid =
$plan.nativeIdentity$;
```

##### Stored Procedure

```sql
EXEC HR.dbo.DeleteEmployee @EmpID =
$plan.nativeIdentity$;
```

### Multiple Query Execution

Multiple SQL query execution is supported for create, add/remove entitlement, enable, disable, and unlock operations.

Queries can use response items from the result set of prior queries, via the `$response.fieldname.fieldtype$` variable syntax.

For example, if the response of the first SQL query is used in the execution of the second entry, use the following SQL query:

```sql
SELECT id FROM databasename.tablename WHERE fname = $plan.firstname$;
INSERT INTO databasename.tablename (fname, lname, email) VALUES ($response.id.Int$, $plan.lastname$, $plan.mail.string$);
```

# Aggregation

For more information about aggregation, refer to [Loading Account Data](<https://documentation.sailpoint.com/saas/help/accounts/loading_data.html>).

The following topics provide additional information for configuring aggregation:

## Aggregating Accounts and Entitlements

After you create and test the connection, you can load accounts or entitlements.

You must first [discover the schema](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#discovering-an-account-schema>) and identify attributes to use for required identity data before loading accounts or entitlements.

> [!NOTE]
>
> **Best Practice**
>
> Test the SQL queries outside of SailPoint to make sure you get the results you expect. If you receive a general error, check that your query does not contain a semicolon (;) at the end.

## Account Aggregation

After you have discovered the schema and identified required attributes, you can load accounts.

**Prerequisite**: Ensure you entered an Account SQL Query into the source connection. For more information, refer to [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>).

1. Go to the **Import Data** tab and select **Start** beside **Manual Aggregation**. For more information on running a manual aggregation, refer to [Starting a Manual Aggregation](<https://documentation.sailpoint.com/saas/help/setup/load_entitlements.html#starting-a-manual-aggregation>).
2. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

## Entitlement Aggregation

Load entitlements to SailPoint from the JDBC server.

1. Enter the query into the **Group SQL Query** field in the [Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-query-settings>).
2. Manually start an **Entitlement Aggregation** in the **Import Data** tab.
3. Check the results in the Aggregation Activity log.

   If the results do not seem right, try running the query as a database user and compare the number of rows returned by the query.

## 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:

```sql
Select * from <TableName> where NativeIdentity = '$(identity)'
```

For example:

```sql
Select * from Users where UserID = '$(identity)'
```

- You configure the SQL query for accounts in the **Single Account SQL Query** field in [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>).
- You configure the SQL query for groups in the **Single Group SQL Query** field in [Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-query-settings>).

### Account Status

For the JDBC 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](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#editing-an-account-schema>).

The following SQL query can be used to set up the state for the account:

```sql
select <Column1>, <Column2>, <Column...n>, case when <conditional Column for enable disable> then 'true' else 'false' end as "IIQDisabled" from <table_name>;
```

For example:

```sql
select EmployeeId, FirstName, LastName, IsActive, Groups, case when IsActive=0 then 'true' else 'false' end as "IIQDisabled" from Employee;
```

If you are using a stored procedure, the stored procedure needs to be updated to set a status.

For example:

```sql
CREATE PROCEDURE empstatus AS select empid, FirstName, LastName, isactive, Groups, case when isactive=0 then 'true' else 'false' end as "IIQDisabled" from master.dbo.emp GO;
```

> [!NOTE]
>
> In cases where the stored procedure can not be modified, use the JDBC BuildMap Rule to set the account status. For more information, refer to [Identity Security Cloud Rule Guide - BuildMap Rule](<https://community.sailpoint.com/knowledge/IdentityNow-Rule-Guide-JDBC-BuildMap-Rule>).

## Data Merging Support

For the JDBC source, if the data for a single account spans multiple rows of database table, you can merge the data for successful aggregation.

For the usage of the REST APIs for the following attributes and keys, refer to:

- API to get source details:

  [https://developer.sailpoint.com/docs/api/v2024/list-sources](<https://developer.sailpoint.com/docs/api/v2024/list-sources>)
- API to get specific source's current schema details:

  [https://developer.sailpoint.com/docs/api/v2024/get-source-schemas/](<https://developer.sailpoint.com/docs/api/v2024/get-source-schemas/#request>)
- API to add new schema attribute to source:

  [https://developer.sailpoint.com/docs/api/patch-requests/\#add](<https://developer.sailpoint.com/docs/api/patch-requests/#add>)

For example, if you want to merge data of the attributes such as email and phone, you can use the REST API to set the `mergeRows` attribute and use the value as `true`.

If you want to merge the columns, set form-data values as follows:

- **key**: use the `indexColumns` attribute key
- **value**: the value of this key must be the name of the column or list of columns that you want as index column for merging. For example, you can you use value in a format such as:

  ```text
  [<IndexKey1>, <IndexKey2>,...<IndexKeyn>]
  ```
- **key**: use the `mergeColumns` attribute key
- **value**: the value of this key must be the name of the column or list of columns that you want to merge with the index column. For example, you can you use value in a format such as:

  ```text
  [<MergeKey1>, <MergeKey2>,...<MergeKeyn>]
  ```

> [!NOTE]
>
> The Account SQL query should have order by clause when merging is enabled. The query should be ordered by the index columns defined in the source.
>
> ```sql
> eg: select * from <Table> order by IndexKey1, IndexKey2
> ```

## Delta Aggregation Tables

When delta aggregation is enabled, only accounts that have changed since the last aggregation are loaded. A delta aggregation typically takes less time and fewer system resources than a full aggregation.

1. Perform a full aggregation as described in [Loading Account Data](<https://documentation.sailpoint.com/saas/help/accounts/loading_data.html>).
2. Create the delta change table (as described below).
3. Enable delta aggregation in your source as described in [Account Delta Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-delta-aggregation>).

The next time you load accounts, only accounts that have changed in the source database tables are loaded.

> [!NOTE]
>
> Currently SailPoint does not support delta aggregation for groups. Open a [support ticket](<https://community.sailpoint.com/t5/Community-Tutorials/How-To-Use-the-SailPoint-Support-Portal/ta-p/157222>) for assistance.

### Creating the Delta Change Table

The database table containing the delta changes attribute must be provided with the value of table name (this is what you enter in the **Database Table Name that Contains the Delta Changes** field). This table must have read and write permissions.

To create the Delta Change tables, complete the following:

1. Create two tables to capture the identities whose data is modified in the master tables:

   - One table for capturing the account whose attributes, entitlements, or direct permissions are modified in the master table for account, master table for its entitlements, or master table for its direct permissions, respectively.
   - Another table for capturing the account-group whose attributes are modified in the master table for account-group.

   Each of the two tables must contain two columns such that:

   - The first column stores the identity attribute defined in SailPoint.
   - The second column stores the action. The values of the action can be `Insert`, `Update`, or `Delete`.

   For example, the SQL command to create such a table for account or entitlement in Oracle Database is as follows:

   ```sql
   CREATE TABLE USER_DELTA (USER_ID VARCHAR2(20), ACTION VARCHAR2(10));
   ```
2. Assign the privileges to read and delete the records from the tables created in Step 1 to the connection user whose credentials you entered in the source configuration page within SailPoint.
3. Create the triggers on the master table.

   When there is a change in the attributes, entitlements, or permissions on the master table (or the tables that are linked to it, like the Entitlements table and the Permissions table for the Account), this triggers the system to write the Account Identity attribute in the first table created in Step 1.

   For example, in Oracle databases the following trigger writes the user IDs in the first table (Account table), which has undergone some modifications, along with the respective action:

   ```sql
   CREATE or REPLACE TRIGGER T1
   AFTER DELETE OR INSERT OR UPDATE ON USER_MASTER
   FOR EACH ROW
   BEGIN
       IF INSERTING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:NEW.USER_ID, 'Insert');
       END IF;

       IF UPDATING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:NEW.USER_ID, 'Update');
       END IF;

       IF DELETING THEN
           INSERT INTO USER_DELTA (USER_ID, ACTION)
           VALUES (:OLD.USER_ID, 'Delete');
       END IF;
   END;
   /
   ```

   With the above example, triggers can be created for the following tables also by replacing USER\_MASTER with the following respective table names:

   - Entitlements table for account (ACCOUNTENTITLEMENT\_MASTER)
   - Permissions table for account (PERMISSION\_MASTER)
4. After the delta aggregation completes, all processed rows are deleted from the delta tables. Rows that are added during or after the aggregation are processed in the next delta aggregation.

## Aggregation Out of Memory Error and Resolution

If your JDBC source has to aggregate a large amount of data, it may encounter an `out of memory` error. To mitigate this issue and aggregate the data, you can perform one of the following solutions:

- Use the `TYPE_FORWARD_ONLY` ResultSet type for JDBC drivers that support it.
- Increase RAM of the Java heap size (memory) in the Java virtual machine for JDBC drivers that do not support the `TYPE_FORWARD_ONLY` ResultSet type.

JDBC drivers that support the `TYPE_FORWARD_ONLY` ResultSet type read the data in the forward direction only which reduces data caching and results in lower memory consumption. Use the SailPoint REST API to update JDBC drivers that support the `TYPE_FORWARD_ONLY` ResultSet type.

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

Use the following attribute keys:

| Attribute Keys | Suggested Values |
| --- | --- |
| resultSetFetchSize | 1000 |
| statementFetchSize | 1000 |
| resultSetType | TYPE\_FORWARD\_ONLY |

For JDBC drivers that don't support the `TYPE_FORWARD_ONLY` ResultSet type, increase the RAM allocated for the Java heap size (memory) in the Java virtual machine. To increase the allocated RAM:

1. Create a text file and name it `java_overwrites.yaml`. Copy the following parameters into the file.

   ```yaml
   #Xmx: 912m
   #DbsfPoolSize: 5
   ```

   Currently, the virtual appliance only supports the following CCG JAVA HEAP options:

   - `Xmx`
   - `DbsfPoolSize`
2. Uncomment the two lines `#Xmx:` &amp; `#DbsfPoolSize` by removing the `#`. Then configure your required values and save the file.

   > **Note**
   >
   > The default value for `Xmx`, `912m`, represents 912 MB. This parameter can only be configured in megabytes. For example, if you want to configure 24 GB, set `Xmx` to `24000m`.

   For example:

   ```yaml
   Xmx: 24000m
   DbsfPoolSize: 5
   ```

   > **Caution**
   >
   > Always leave at least 3GB of RAM on the virtual appliance to run non-CCG processes.
3. Copy the `java_overwrites.yaml` file to the virtual appliance in the following directory:

   ```text
   /home/sailpoint/ccg/
   ```

   > **Note**
   >
   > On newer CCG images, CCG is owned by root. To add the `java_overwrites.yaml` file, you may need to run the following command:
   >
   > ```text
   > sudo /usr/bin/chown sailpoint /home/sailpoint/ccg
   > ```
4. Restart CCG by running the following command:

   ```bash
   sudo systemctl restart ccg
   ```
5. Confirm the ccg.log, found at `/home/sailpoint/log/ccg.log` does not contain errors related to the new configuration.

## Aggregation Retry Configuration

Use the following parameters to specify the number of aggregation retries, the delay between each attempt, and create custom retry exceptions.

> [!NOTE]
>
> Use the REST API to set the values for the parameters in this section.
>
> For more information on the REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

### Enable Retry

> [!NOTE]
>
> Once Retry is enabled, it's applicable for all aggregation types.

This enables you to configure the number of times the system attempts to retry aggregation and the delay between each attempt. Use this feature to retry the whole aggregation in case of failure.

To enable this feature, set the `connRetryEnable` attribute to `true`.

### Retry Count

Specify the number of times you want the system to retry aggregation. The maximum attempts is 10.

To specify the number of retries, set the `connMaxRetry` attribute and enter the value as a `<string number>`.

### Delay Between Retry Attempts (in Seconds)

Specify the delay time between attempts in seconds. The maximum is 10 seconds.

To specify the delay time, set the `connWaitTimeForRetry` attribute and enter the value as a `<string number>`.

### `connRetryExceptionList`

This parameter must be set to retry only specific exceptions.

Use the following format:

- key: `connRetryExceptionList`
- value: a comma separated list of the exception strings

For example, you can use the value: `[<Communications link failure >, <Connection reset >]`

In this case, the system only retries exceptions that contain the strings "Communications link failure" or "Connection reset".

## Account Schemas

Manage the account schema from the JDBC source. The **Account Schema** option in the source's **Import Data** tab enables you to discover your JDBC account schema, edit the schema, and add attributes to the schema.

> [!NOTE]
>
> You must have discovered the account schema first before managing it or its attributes. For more information on discovering the account schema, refer to [Discover the Schema](<#discover-the-schema>).

### Discover the Schema

You must discover the schema and identify the attributes to use for the required identity data before doing anything else.

**Prerequisite:** Ensure you entered an Account SQL query into the source connection. For more information, refer to [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>).

1. From your connector page, go to **Import Data \> Account Schema**.
2. Select **Options \> Discover Schema**.

   The Account SQL Query automatically executes and the discovered attribute names are displayed.
3. Define the attributes to be used for `Name` and `Account ID`. Mark them in the **Account ID** column.

> [!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 not, source accounts could correlate to the first Identity that includes that ID/Name instead of creating an additional Identity.

### Add a New Account Schema Attribute

Add additional attributes to your JDBC account schema from SailPoint.

1. In the JDBC source page, go to **Import Data \> Account Schema**.
2. Select **Add New Attribute** to create a new attribute.

   A form to configure the new attribute displays.
3. Add a **Name** and unique **Description** to the attribute, then select a **Type**. These are required fields.
4. (Optional) As required, check the boxes for **Entitlement** and **Multi-Valued**.
5. (Optional) If you want to create another new attribute after saving the current one, check the **Add Another** box. When you save the current one, another form displays for the next attribute you want to create.
6. Select **Save**.

### Edit the Account Schema

Give the account schema a unique description, and define the attributes for the schema's **Account ID** and **Account Name**.

1. In the JDBC source page, go to **Import Data \> Account Schema**.
2. Select **Options \> Edit Schema**.

   A form displays which allows you to update the schema.
3. Enter a unique description into the **Description** field.
4. Select the attribute to use as the schema's Account ID from the **Account ID** dropdown.
5. Select the attribute to use as the schema's Account Name from the **Account Name** dropdown.
6. Select **Update**.

## Entitlement Schemas

You need to manually add and configure entitlement schemas to the JDBC source.

### Add Entitlement Types

This process describes how you can add entitlement types (entitlement schemas) and configure their attributes in SailPoint.

> [!NOTE]
>
> Any new entitlement schemas you create appear in **Query Settings**. In Query Settings, you can configure the connection and SQL queries as needed. For more information, refer to [Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-schema-query-setup>).

1. In the JDBC source page, go to **Import Data \> Entitlement Types**.
2. Select **Create Entitlement Type**.

   A form to configure the new entitlement type displays.
3. Give the entitlement a **Name** and a unique **Description**.

   > **Note**
   >
   > Once you enter the entitlement type's name you can't change it later.
4. (Optional) If the new entitlement should include permissions during aggregations, check the **Include permissions in aggregations** checkbox.
5. Select **Save**.

### Add Attributes to an Entitlement Type

Edit the attributes associated with each of your entitlement types.

1. In the JDBC source page, go to **Import Data \> Entitlement Types**.
2. Select an entitlement type from the list.

   The entitlement type entry expands to display the associated attributes.
3. Select **Add New Attribute** to create a new attribute.

   A form to configure the new attribute displays.
4. Add a **Name** and unique **Description** to the attribute, then select a **Type**. These are required fields.
5. (Optional) As required, select the checkboxes for **Entitlement** and **Multi-Valued**.
6. (Optional) If you want to create another new attribute after saving the current one, select the **Add Another** checkbox. When you save the current one, another form displays for the next attribute you want to create.
7. Select **Save**.

## Configuring Multiple Group Objects

The JDBC connector supports multiple group objects for Entitlement Aggregation. Depending on the managed system that you are connecting with the JDBC source, you can set up attributes such as entitlements and roles. You can add one or more entitlement schemas for aggregation.

> [!NOTE]
>
> To aggregate entitlement schemas, regardless of if you need one or multiple, you must to manually add and configure them in the JDBC source. For more information, refer to [Entitlement Schemas](<#entitlement-schemas>).

### Adding or Updating Multiple Group Objects from SailPoint

Once you establish a connection to the JDBC database as described in [Account Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings>), you can access the **Account Schema** and **Entitlement Types** options in the **Import Data** tab.

### Adding an Entitlement Schema Using the REST API

In the JDBC source for entitlement aggregation, you can add the entitlement schema using the REST API. This topic provides information on adding the entitlement schema using the REST API. Alternatively, you can add the entitlement schema in the SailPoint user interface. For more information, refer to [Entitlement Schemas](<#entitlement-schemas>).

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

Example 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)"
}
```

### Configuring Multiple Group Objects with the REST API

Use the following request body for the REST API:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

```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)"
}
```

## Partitioning Aggregation

The JDBC connector supports the manual partitioning through configured SQL statements.

For example, if there is an employee dataset that has 100,000 rows with a sequential `employeeId` field, the partitioning statements that can be used are as follows:

```sql
select x,y,z from a where employeeId <= 10000;
select x,y,z from a where employeeId > 10000 AND employeeId <= 20000;
select x,y,z from a where employeeId > 20000 AND employeeId < =30000;
…
select x,y,z from a where employeeId > 90000;
```

The above example has 10 partitions, handling approximately 10,000 accounts. The last row (with `employeeId > 90000`) handles a larger number of accounts depending on the total number of employees in the system.

> [!NOTE]
>
> An additional aggregation option, `noAttributePromotion`, has been added. If this attribute is set to `true`, the attribute promotion is skipped during aggregation.

To enable partitioning, add the following attribute key-value pairs into the source XML using the REST API.

- **Attribute Key** - `partitionAggregationEnabled`; **Value** - `true` to trigger a specific operation rule.
- **Attribute Key** - `partitionMode`; **Value** - `true` to assign the rule to the source.
- **Attribute Key** - `partitionStatements`; **Value** - enter either of the following to use SQL statements or stored procedures for aggregation:

  SQL query, for example:

  ```sql
  select x,y,z from a where employeeId <= 10000;
  ```

  Stored procedure, for example:

  ```sql
  EXEC emp ;
  ```

  > **Important**
  >
  > To use a stored procedure with the `partitioningStatements` instead of a SQL query, you must also add the `useStoredProcedurePartition` attribute key with the value `true`.

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

# Connecting SailPoint and JDBC

To connect SailPoint with JDBC, 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](<https://documentation.sailpoint.com/saas/help/sources/index.html#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](<https://documentation.sailpoint.com/saas/help/va/index.html>).
   - **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](<https://documentation.sailpoint.com/saas/help/common/users/user_levels.html#source-sub-admin-user-level>) or [role](<https://documentation.sailpoint.com/saas/help/common/users/user_levels.html#role-sub-admin-user-level>) sub-admin level oversight of the source and its access. For more information, refer to [Creating and Managing Governance Groups](<https://documentation.sailpoint.com/saas/help/common/users/governance_groups.html>).
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](<https://documentation.sailpoint.com/saas/help/setup/identity_profiles.html>).
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](<https://documentation.sailpoint.com/saas/help/va/index.html>).
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](<https://documentation.sailpoint.com/connectors/help/credential_providers.html>).
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](<https://documentation.sailpoint.com/saas/help/common/users/governance_groups.html#associating-a-governance-group-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**.

## Connection Settings

Configure how SailPoint connects to the database in the connection settings for [accounts](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings>) and [groups](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-connection-settings>). The following topics describe the additional files and settings the connection requires.

### Connector Files

> [!NOTE]
>
> **Screenshot**
>
> [![Connector Files](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_base1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_base1.png>)
>
> [![Connector Files continued](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_base2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_base2.png>)

1. Upload the JDBC driver for your database into the **Upload Files** field. The current file (if there is one) and upload history are listed below the drag-and-drop box. For more information on the supported driver .jar files for the related databases, refer to [Required JDBC Driver JAR Files](<https://community.sailpoint.com/knowledge/Required-JDBC-Driver-JAR-Files>).

   > **Warning**
   >
   > Ensure that the file you upload is not vulnerable or malicious.

   > **Note**
   >
   > You must download the library from a trusted source and validate the authenticity of the download location via inspection of valid SSL/TLS certificates. You must also validate the authenticity of the library by inspecting digital signatures or validating checksums when that information is made available.
2. (Optional) Upload the `SQLJDBCDriver.config` and `krb5.config` files used to connect to a Microsoft SQL Server Database using Windows authentication. The current file and upload history are listed below the button.

   > **Warning**
   >
   > Ensure that the file you upload is not vulnerable or malicious.

   > **Important**
   >
   > This step is necessary to connect a Microsoft SQL server database using Kerberos authentication.
   >
   > Once you upload the file to the selected location, you may need to restart the Cloud Connection Gateway (CCG) service on the Virtual Appliance. This is to make sure that the previous cached configuration file - if one exists - is removed and a fresh configuration file is used. For more information, refer to the [Virtual Appliance Troubleshooting Guide](<https://community.sailpoint.com/knowledge/Virtual-Appliance-Troubleshooting-Guide>) in Compass. You may need to log in to your Compass account access this information.

   > **Note**
   >
   > For more information on Windows authentication and its requirements, refer to [Windows Authentication and Kerberos](<#windows-authentication-and-kerberos>).

   Drag and drop the file onto the **Upload Files** box to upload it, or select anywhere within the **Upload Files** box to choose the file on your computer.
3. Select **Save**.

### Windows Authentication and Kerberos

The following topics contain information for configuring a connection to SQL database servers using Windows authentication and the Kerberos authentication protocol that supports it.

#### Windows Authentication

Windows authentication can go by many names. Some of the most common names include:

- Windows authentication
- Windows domain authentication
- Domain authentication
- Integrated authentication
- Kerberos authentication

In this topic, the terms "Windows authentication" and "Kerberos" are used.

> [!IMPORTANT]
>
> To configure Windows authentication, you must properly configure [Kerberos](<#kerberos>).

When you connect using a Windows user account, the JDBC SQL Server source validates the account name and password using the Windows principal token in the operating system. In other words, the user identity is validated by Windows, not the source. The source doesn't ask for a password and it doesn't validate the identity. Windows authentication is the default authentication mode. It is much more secure than SQL Server authentication. A connection made using Windows authentication is sometimes called a trusted connection, because the SQL server trusts the credentials provided by Windows.

Windows authentication supports the following:

- The Kerberos authentication scheme
- Password policy enforcement with regard to complexity validation for strong passwords
- Account lockout
- Password expiration

By using Windows authentication, Windows groups can be created at the domain level and a login can be created on the SQL Server source for the entire group. Managing access at the domain level can simplify account administration.

> [!NOTE]
>
> When you create a service account to authenticate with Microsoft SQL, configure the account using the required format:
>
> ```text
> <name>@<realm>
> ```
>
> In this format, `<name>` refers to the account's username and `<realm>` is the domain name.

For more information, refer to [Connecting through Windows Authentication](<https://learn.microsoft.com/en-us/sql/relational-databases/security/choose-an-authentication-mode?view=sql-server-ver16#connecting-through-windows-authentication>) in the Microsoft documentation.

> [!NOTE]
>
> The linked document is not maintained by SailPoint and is subject to change without notice.

##### Windows Authentication Compatibility Check

To properly support Windows authentication, the virtual appliance must be able to resolve the SQL server host, the key distribution center (KDC), and the default Kerberos realm.

To check if the VA can resolve the necessary components, run the following command, replacing `<host_fqdn>` with each component's fully qualified domain name:

```bash
nslookup <host_fqdn>
```

For example:

- Verify resolvability with the SQL server:

  ```bash
  nslookup MYSQLSERVER.EXAMPLE.COM
  ```
- Verify resolvability with the KDC:

  ```bash
  nslookup MYKDC.EXAMPLE.COM
  ```
- Verify resolvability with the Kerberos realm:

  ```bash
  nslookup EXAMPLE.COM
  ```

> [!NOTE]
>
> You may to change your DNS configuration on Virtual Appliance. For more information on changing the DNS, refer to [Network Troubleshooting](<https://community.sailpoint.com/knowledge/Virtual-Appliance-Troubleshooting-Guide>).

#### Kerberos

The SailPoint JDBC connector supports Windows authentication using the Kerberos authentication scheme as an underlying protocol for authentication information exchange.

To provide a Login Module Configuration File and a Kerberos Configuration File, refer to [Base Configuration](<#base-configuration>).

##### Kerberos Prerequisites

- The SQLJDBCDriver.config and krb5.config files should be uploaded to the source.
- Update the `useMssqlKerberosAuth` parameter in the source XML using the REST API.

  | Parameter | Description |
  | --- | --- |
  | `useMssqlKerberosAuth` | Boolean. Default - `false`. |

  This parameter should be set to `true` when you need to connect to a Microsoft SQL server database with Kerberos authentication.

  ```json
  [
      {
          "op": "add",
          "path": "/connectorAttributes/useMssqlKerberosAuth",
          "value": true
      }
  ]
  ```

  > **Note**
  >
  > For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

##### Login Module Configuration File

**Filename:** SQLJDBCDriver.config

> [!IMPORTANT]
>
> This file is mandatory to be provided for Windows Authentication. You can upload the file via [Base Configuration](<#base-configuration>) tab.

A login module configuration file consists of one or more entries, each specifying which underlying authentication technology should be used for a particular application or applications. The login module configuration file for this connector allows the SQL JDBC driver to acquire Kerberos credentials.

Each login module configuration file entry consists of a name followed by one or more login module-specific entries. Each entry is terminated by a semicolon. Groups of entries are enclosed by braces.

For more information, refer to *Creating a login module configuration file* in [Using Kerberos integrated authentication to connect to SQL Server](<https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16#creating-a-login-module-configuration-file>) in the Microsoft documentation.

> [!NOTE]
>
> The linked document is not maintained by SailPoint and is subject to change without notice.

##### Kerberos Configuration File

**Filename:** krb5.config

The krb5.config file contains the Kerberos configuration information, including the locations of key distribution centers (KDCs), administration daemons for the Kerberos realms of interest, defaults for the current realm and for the Kerberos applications, and mappings of host names onto the Kerberos realms. This file must reside on all Kerberos clients.

The format of the krb5.config consists of section headings in square brackets. Each section can contain zero or more configuration variables (called relations), of the form:

```text
relation = relation-value
```

or

```text
relation-subsection = {
    relation= relation-value
    relation= relation-value
```

> [!NOTE]
>
> For single domain environments, Kerberos connectivity may succeed without a defined KDC and Realm or any configuration file. This may occur if the Kerberos client loaded on the client computer can resolve and reach the default Kerberos realm and KDC automatically.
>
> If you have multiple sources with multiple domains and connectivity across the domains, you must provide a `krb5.config` file.
>
> - Each Virtual Appliance uses a single `krb5.config` file. If you have multiple domains, configure them as multiple realms within the same file.

For more information, refer to [Creating a Kerberos configuration file](<https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16#creating-a-kerberos-configuration-file>) in the Microsoft documentation.

> [!NOTE]
>
> The linked document is not maintained by SailPoint and is subject to change without notice.

### JDBC Pooling Parameters

The following parameters are not displayed on the UI, but are used to tune JDBC pooling.

To add the parameter to SailPoint, use the following JSON example and add in the required parameter and value. Add it to the source XML using the SailPoint REST API.

```json
{
    "op": "add",
    "path": "/connectorAttributes/<attribute_name>",
    "value": "<value>"
}
```

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

| Parameter | Description |
| --- | --- |
| `pool.maxWait` | Maximum time to wait for connection to become available in milliseconds. The default value is 60000 milliseconds. |
| `pool.evictRuns` | Wait time between closing idle connections in milliseconds. The default value is 300000 milliseconds. |
| `pool.maxActive` | Maximum number of connections. The default value is 10 connections. |
| `pool.maxIdle` | Maximum number of idle connections. The default value is 5 idle connections. |
| `pool.minEvictIdle` | Minimum idle time to close connection in milliseconds. The default value is 6000000 milliseconds. |

## 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](<https://documentation.sailpoint.com/saas/help/ai/app_onboarding/index.html>) in the SailPoint SaaS documentation.

# Dataset Management

Dataset Management lets you review the object types a source collects and control how the source aggregates those objects into SailPoint. Use Dataset Management to view and manage resource attributes, datasets and their resources, enable dataset aggregation schedules, perform manual aggregation, and view aggregation history.

> [!NOTE]
>
> Datasets are also available in SailPoint Agentic Fabric. Contact your Customer Success team and refer to [SailPoint Agentic Fabric](<https://documentation.sailpoint.com/saas/help/agentic_fabric/index.html>) for more information.

For more information, refer to [Managing Datasets and Resources](<https://documentation.sailpoint.com/saas/help/sources/datasets.html>) for datasets, resources, owner correlation, and aggregation.

## Datasets

A *dataset* is a named group of one or more related resources that the source aggregates together.

When aggregation runs for a dataset, the source collects all resources in that dataset from the managed system in one aggregation job. Activate or deactivate aggregation at the dataset level, not for individual resources.

> [!NOTE]
>
> Each resource belongs to exactly one dataset for that source. The source defines which resources belong to each dataset. You can enable aggregation and review associated resources, but you cannot create or delete datasets or move resources between them.

### Dataset Aggregation

Configure aggregation on the **Dataset Aggregations** tab for each dataset. Configure each dataset separately. During aggregation, your owner correlation rules are automatically applied to link machine identities to their human owners. For more information, refer to [Configuring Dataset Aggregations](<https://documentation.sailpoint.com/saas/help/sources/datasets.html#configuring-dataset-aggregations>).

## Resources

A resource defines an object type from the managed system. It specifies the kind of object, the attributes the source collects, and how the source identifies and displays each object. Resources form the building blocks of the data model.

In the UI, each resource appears under **Dataset Management** \> **Resources** with its schema. A resource does not run aggregation by itself. The dataset that owns the resource collects it during aggregation.

For more information, refer to [Managing Resources](<https://documentation.sailpoint.com/saas/help/sources/datasets.html#managing-resources>).

### Owner Correlation

Configure owner correlation on each resource to link aggregated objects to human owners. If you do not configure owner correlation, or if the mapping produces no match, an owner is not assigned. For more information, refer to [Configuring Owner Correlation](<https://documentation.sailpoint.com/saas/help/sources/datasets.html#configuring-owner-correlation>).

## Creating Datasets and Resources

Create datasets and resources for your JDBC source in Dataset Management. Refer to [Creating Datasets](<https://documentation.sailpoint.com/saas/help/sources/datasets.html#creating-datasets>) and [Creating Resources](<https://documentation.sailpoint.com/saas/help/sources/datasets.html#creating-resources>) for the steps.

Use the following JDBC-specific guidance when you create them:

- Name datasets to describe the group of resources they aggregate, for example `snowflake:cortex`.
- Name resources to describe the object type you want to aggregate, for example `Snowflake Cortex Agent`.
- Enter schema attribute names that match names your SQL query returns.

> [!IMPORTANT]
>
> The resource ID attribute must exactly match a column name that your SQL query returns. Some databases, such as Snowflake, treat column names as case-sensitive.

After you create the resource, configure SQL queries in [Configuring Dataset Query Settings](<#configuring-dataset-query-settings>), then aggregate the dataset.

## Configuring Dataset Query Settings

Configure the SQL queries SailPoint runs to load resource data from your database.

### Enabling Agent Governance

> [!NOTE]
>
> **Screenshot**
>
> [![Agent Governance Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_query_settings_agent_governance.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_query_settings_agent_governance.png>)

1. Select the **Enable Agent Governance** toggle. This action reveals the Dataset Query Settings section, which displays a card for each resource you created in Dataset Management.

   > **Note**
   >
   > When you turn off this toggle, SailPoint hides the Dataset Query Settings section.

### Configuring Resource Query Settings

1. Select the edit icon for the resource you want to configure.
2. Perform the following tasks to configure queries for each resource:

   1. [Configure connection settings](<#configuring-connection-settings>)
   2. [Configure SQL query settings](<#configuring-sql-query-settings>)
   3. [Set additional query settings](<#setting-additional-query-settings>)

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

#### Configuring Connection Settings

Enter the connection details SailPoint uses to connect to your database and load resource data for dataset aggregation.

> [!NOTE]
>
> If you leave these fields blank, the connector uses the Account Connection Settings.

> [!NOTE]
>
> **Screenshot**
>
> [![Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_connection_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_connection_settings.png>)

1. In the **Database URL** field, provide the JDBC connection string corresponding to the database with which to connect. The generic format is as follows:

   `jdbc:<database_type>://:<port>/<database_name>`

   For example:

   `jdbc:snowflake://sailpoint-conn.snowflakecomputing.com`

   > **Note**
   >
   > The JDBC connector is a generic connector that supports connecting to a database, provided that the database has a JDBC driver available.
2. Enter the **Driver Class** path according to database you want to connect.

   For example:

   `net.snowflake.client.jdbc.SnowflakeDriver`
3. Enter the **User Name** required to access the server.
4. Enter the **Password** associated with the user name to access the server.
5. Select **Save**.

#### Configuring SQL Query Settings

Configure the SQL query SailPoint runs to load resource data from your database.

> [!NOTE]
>
> **Screenshot**
>
> [![Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_query_settings.png>)

1. Enter an SQL query in the **SQL Query** field. The query must return columns that match the attribute names you defined in the resource schema.

   For example, for a resource schema with attributes NAME, DATABASE\_NAME, and SCHEMA\_NAME:

   ```sql
   SELECT NAME, DATABASE_NAME, SCHEMA_NAME FROM SNOWFLAKE.CORTEX.AGENTS
   ```

   For example, for a capability resource with attributes CAPABILITY\_NAME, AGENT\_NAME, and DESCRIPTION:

   ```sql
   SELECT CAPABILITY_NAME, AGENT_NAME, DESCRIPTION FROM SNOWFLAKE.CORTEX.AGENT_CAPABILITIES
   ```
2. (Optional) Select the **Use Stored Procedure for SQL Query** checkbox to execute a stored procedure on the database.

   For example:

   ```sql
   CALL GetCortexAgents()
   ```
3. Select **Save**.

#### Setting Additional Query Settings

Set additional query settings to support the SQL query for a resource.

> [!NOTE]
>
> **Screenshot**
>
> [![Additional Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_additional_query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/dataset_resource_additional_query_settings.png>)

1. (Optional) Select the **Use executeQuery** checkbox to return a single ResultSet object when connector queries the JDBC database.
2. Select **Save**.

### Governance Attributes

During aggregation, the connector adds the `spBusinessApplication` attribute with the value `JDBC_NHI` to agent resources (`std:agent`). You do not need to include this attribute in your SQL query or resource.

The `spStatus` attribute tells SailPoint whether an agent is enabled or disabled. To report agent status, add `spStatus` as an attribute on the resource and return the matching column in your SQL query. You can also add `spDescription` to provide a description for the resource.

For example:

```sql
SELECT NAME, DATABASE_NAME, STATUS AS spStatus, DESCRIPTION AS spDescription FROM SNOWFLAKE.CORTEX.AGENTS
```

# Group Schema Query Setup

Set up how the connector queries the JDBC source for account and/or group schema information.

> [!NOTE]
>
> **Screenshot**
>
> [![Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_settings.png>)

Select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/query_configure_button.png>) for the schema you want to configure to launch additional settings.

Perform the following tasks to configure the queries for groups, which help with JDBC entitlements.

## Group Connection Settings

Enter the connection information to allow SailPoint to locate and connect to the JDBC server to load group information, as well as any group-related functions.

> [!NOTE]
>
> If these fields are left blank, the connector uses the [Account Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings>).

> [!NOTE]
>
> **Screenshot**
>
> [![Group Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group_conn_va.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group_conn_va.png>)

1. Enter the **User Name** required to access the server.
2. Enter the **Password** associated with the user name to access the server.
3. Enter the server's address into the **Database URL** field. The address should be in the following format:

   ```text
   jdbc:<database_type>://<host>:<port>/<database_name>
   ```

   For example:

   ```text
   jdbc:snowflake://sailpoint-conn.snowflakecomputing.com
   ```
4. Enter the **Driver Class**. For example:

   ```text
   net.snowflake.client.jdbc.SnowflakeDriver
   ```
5. Select **Save**.

## Group Query Settings

Configure the SQL queries SailPoint uses to retrieve group data from your JDBC server.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group1.png>) [![Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_group2.png>)

1. Enter a SQL query to load groups into the **Group SQL Query** field. Groups are used for entitlements. Check the **Use Stored Procedure for Group SQL Query** box to execute a stored procedure on the database.
2. Enter a SQL query to load a single group into the **Single Group SQL Query** field. Check the **Use Stored Procedure for Single Group SQL Query** box to execute a stored procedure on the database.
3. Select **Save**.

## Additional Group Query Settings

Set additional query settings to support the SQL queries.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

> [!NOTE]
>
> **Screenshot**
>
> [![Additional Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_dp.png>)](<https://documentation.sailpoint.com/connectors/jdbc/va/help/img/jdbc_dp.png>)

1. Check the **Use executeQuery** box to tell SailPoint to return a single ResultSet object when it queries the JDBC database.
2. Enable **Direct Permission** and add the SQL query. For more information, refer to [Direct Permissions](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#direct-permissions>).
3. Select **Save**.

# Integrating SailPoint with JDBC

If you want to load user data, you can create a direct connect source for JDBC and configure a SQL query in it to load the data you want. To manage that data, either create an [identity profile](<https://documentation.sailpoint.com/saas/help/setup/identity_profiles.html>) based on the source or verify that the accounts you load are [correlated](<https://documentation.sailpoint.com/saas/help/accounts/correlation.html#configuring-account-correlation>) to an existing identity.

If you want to load the database user accounts, use a direct connection to the database itself.

> [!CAUTION]
>
> JDBC sources use the default correlation configuration which uses the Display Name attribute on the identity to correlate with the `displayName` attribute on the source.

> [!NOTE]
>
> To view the latest features, enhancements, and fixes for all SaaS connectors, refer to the [SaaS Release Notes](<https://community.sailpoint.com/release-notes/saas>) page.

## Supported Features

> [!NOTE]
>
> Features marked with an asterisk (\*) must be purchased and activated. This is an advanced connector for entitlements, provisioning, and password management. Please open a [support ticket](<https://community.sailpoint.com/knowledge/collection/working-with-services>) for assistance.

The SailPoint JDBC connector supports the following features:

- Accounts
- \*Password Management
- \*Provisioning
- \*Access Certifications (certification of entitlements connected to accounts)
- Multiple group objects for entitlement aggregation
- Group Hierarchy
- Delete Account

For more information on features, refer to [Source Features](<https://community.sailpoint.com/knowledge/Identity-Security-Cloud-Source-Features>).

### Password Management

> [!IMPORTANT]
>
> To implement the password management feature, you must:
>
> - Purchase the Password Management feature.
> - Contact [Services](<https://community.sailpoint.com/knowledge/collection/working-with-services>) to implement the feature for your environment.

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](<https://documentation.sailpoint.com/saas/help/pwd/index.html>).

> [!IMPORTANT]
>
> You must add the entitlement schema in the JDBC source configuration for entitlement aggregation. For more information, refer to [Configuring Multiple Group Objects](<https://documentation.sailpoint.com/connectors/jdbc/va/help/aggregation.html#configuring-multiple-group-objects>).

### Non-Human Identity Discovery

- Aggregate non-human identity resources, including AI agents, applications, capabilities, credentials, and devices

Refer to Dataset Management for more information.

Refer to [Discovering Potential Non-Human Accounts](<https://documentation.sailpoint.com/saas/help/agentic_fabric/nhi/non_human_accounts.html#discovering-potential-non-human-accounts>) for more information.

## Prerequisites

- Configure at least one virtual appliance cluster and successfully test the connection. For more information, refer to the [Virtual Appliance Reference Guide](<https://documentation.sailpoint.com/saas/help/va/index.html>).
- Identify or create a service account on the database you will access through JDBC.
- An appropriate JDBC Driver for the database. For more information, refer to [System Requirements](<#system-requirements>).
- To load entitlements, you must add the entitlement schema in the JDBC source configuration for entitlement aggregation. For more information, refer to [Configuring Multiple Group Objects](<https://documentation.sailpoint.com/connectors/jdbc/va/help/aggregation.html#configuring-multiple-group-objects>).
- For Snowflake key pair authentication, generate an encrypted public and private key pair. For more information, refer to [Generating Keys for Snowflake](<#generating-keys-for-snowflake>).
- For Snowflake OAuth 2.0 authentication, configure an external IdP and Snowflake security integration. For more information, refer to [Configuring an External IdP for OAuth 2.0](<#configuring-an-external-idp-for-oauth-20>).

#### Provisioning Prerequisites

- Purchase the Provisioning feature.
- Contact [Services](<https://community.sailpoint.com/knowledge/collection/working-with-services>) to implement the feature for your environment.

#### Password Management Prerequisites

- Purchase the Password Management feature.
- Contact [Services](<https://community.sailpoint.com/knowledge/collection/working-with-services>) to implement the feature for your environment.

## System Requirements

The JDBC source should be able to access databases that have a JDBC driver. Refer to the version requirements for direct database connections for supported versions of the following databases:

- [IBM DB2 for Windows](<https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads>)
- [Oracle DB](<https://www.oracle.com/database/technologies/>)
- [Sybase](<https://documentation.sailpoint.com/connectors/sap/sybase_ase/va/help/index.html>)
- [Microsoft SQL Server](<https://docs.microsoft.com/en-us/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?redirectedfrom=MSDN&amp;view=sql-server-ver15>)
- [MySQL](<https://www.mysql.com/products/connector/>)
- [PostgreSQL](<https://jdbc.postgresql.org/>)
- [AWS RDS](<#aws-rds-support>)

### AWS RDS Support

To create a successful connection with an AWS RDS database:

1. Obtain the public IP of the virtual appliance (VA) connected to your source.
2. On the AWS Console, go to the JDBC RDS instance and update the public IP of the VA in the security groups' inbound rules with the Type, Protocol, Port, and Source.
3. After you update the inbound rules, test the connection.

## Permissions

The JDBC connector uses the database user context to support aggregation and other provisioning operations. The database user in the source configuration must have the appropriate rights to fetch and set data related to the entities and attributes mentioned in the JDBC SQL queries, such as accounts, groups, or entitlements.

## Generating Keys for Snowflake

To connect to Snowflake, you need a public and private key pair in PEM format. If you do not already have these keys, you must generate them.

> [!NOTE]
>
> - SailPoint supports only encrypted private key for connecting to Snowflake.
> - Windows systems do not support OpenSSL by default. To generate keys on Windows, download and install OpenSSL from [OpenSSL](<https://www.openssl.org/source/>). macOS support OpenSSL by default.
> - Enter the generated private key on the [Account Connection Settings for Snowflake](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings-for-snowflake>) page.

**To generate the private and public keys, complete the following steps:**

1. Open a command prompt or terminal window.
2. Run the following command to generate an encrypted private key:

   ```bash
   openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8
   ```

   When you are prompted, enter a passphrase to protect the key. Store this passphrase in a secure location.
3. Run the following command to generate the public key from the private key file:

   ```bash
   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
   ```
4. Copy the public and private key files to a local directory.
5. Save the public and private key files to a secure local directory, and record the file paths.

## Configuring an External IdP for OAuth 2.0

Use this procedure to configure an external identity provider (IdP) for Snowflake OAuth 2.0 authentication with the JDBC connector. The following steps use Microsoft Azure as the external IdP.

> [!NOTE]
>
> For Okta, PingFederate, or another external IdP supported by Snowflake, follow the corresponding Snowflake configuration guide to obtain the issuer, JWKS URL, audience, and token claims. Enter the resulting Snowflake `LOGIN_NAME` as the connector **User Name** on the [Account Connection Settings for Snowflake](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings-for-snowflake>) page.

### Registering the OAuth Resource (Snowflake) in Entra ID

1. Go to **Azure Portal \> Entra ID \> App registrations** and select **New registration**.
2. Enter `Snowflake OAuth Resource` as the name.
3. Select **Single tenant** and select **Register**.
4. Go to **Expose an API** \> **Add (Application ID URI)**, accept the default URI or supply a unique URI and save the URI as `<SNOWFLAKE_APPLICATION_ID_URI>`.
5. Go to **App roles** \> **Create app role** and configure the following settings

   1. Allowed member types: Select Applications (This is required for the Client Credentials grant type)
   2. Enter a **Value** for the app role

      Enter `session:role:<snowflake-role-name>` to assign a specific Snowflake role

      Enter `session:role-any` to let the connection select the role
   3. **Display name**/ **Description**: Enter a meaningful name and description
   4. **Enable this app role**: Yes
6. Go to **Overview \> Endpoints** and record the following values:

   1. **OAuth 2.0 token endpoint (v2)** as `<AZURE_AD_OAUTH_TOKEN_ENDPOINT>`.
   2. Open the **OpenID Connect metadata document**, record the `jwks_uri` value as `<AZURE_AD_JWS_KEY_ENDPOINT>`.

      For example: `https://login.microsoftonline.com/<tenant-id>/discovery/v2.0/keys`
   3. Open the **Federation metadata document** and record the `entityID` value as `<AZURE_AD_ISSUER>`.

      For example: `https://sts.windows.net/<tenant-id>/`

### Registering the OAuth Client (Connector) in Entra ID

1. Go to **App registrations** \> **New registration**.
2. Enter `Snowflake OAuth Client` as the name.
3. Select **Single tenant** and select **Register**.
4. From **Overview**, copy the **Application (client) ID** to use as your `<Client_ID>`.
5. Go to **Certificates &amp; secrets** and select **New client secret**.
6. Enter a description, set an expiry date and select **Add**. Copy the secret **Value** to use as your `<Client_Secret>`.

   > **Important**
   >
   > The secret value is shown only once.
7. Go to **API permissions** \> **Add a permission** \> **APIs my organization uses** and select the OAuth resource that you registered in Step 1.
8. Go to **Application permissions**, select the **app role** that you configured in Step 1, and select **Add permissions**.

   > **Note**
   >
   > Client Credentials requires application permissions, not delegated permissions.
9. Select **Grant admin consent for** `<tenant>` and confirm.

### Requesting and Decoding the OAuth Access Token

Perform this verification once. The connector performs the verification automatically at runtime.

#### Request the Token

Run the following command. A successful response contains an `access_token` field.

```bash
curl -X POST -H "Content-Type: application/x-www-form-urlencoded;charset=UTF-8" \
  --data-urlencode "client_id=<Client_ID>" \
  --data-urlencode "client_secret=<Client_Secret>" \
  --data-urlencode "grant_type=client_credentials" \
  --data-urlencode "scope=<SNOWFLAKE_APPLICATION_ID_URI>/.default" \
  '<AZURE_AD_OAUTH_TOKEN_ENDPOINT>'
```

#### Decode the Token

Paste the access\_token into a JWT decoder (for example, [https://jwt.ms](<https://jwt.ms>)), and verify the following values:

- The `iss` value matches the `<AZURE_AD_ISSUER>` from Step 1.
- The `aud` value matches the `<SNOWFLAKE_APPLICATION_ID_URI>` from Step 1.
- The `roles` array contains the app role configured in Step 1 (for example, `session:role:ANALYST`).
- The `sub` value is a GUID. Record this as `<SUB_VALUE>`. This identifies the service principal of the OAuth client in your tenant.

  > **Note**
  >
  > The `sub` claim is used because Client Credentials tokens do not contain a `upn` claim. For more information, refer to the Snowflake Service-Principal example in [Create a security integration (Power Apps)](<https://docs.snowflake.com/en/connectors/microsoft/powerapps/create-security-integration>).

### Creating the External OAuth Security Integration in Snowflake

Run the following command as `ACCOUNTADMIN` (or a role with `CREATE INTEGRATION` privileges):

```sql
CREATE SECURITY INTEGRATION EXTERNAL_OAUTH_AZURE
    TYPE = external_oauth
    ENABLED = true
    EXTERNAL_OAUTH_TYPE = azure
    EXTERNAL_OAUTH_ISSUER = '<AZURE_AD_ISSUER>'
    EXTERNAL_OAUTH_JWS_KEYS_URL = '<AZURE_AD_JWS_KEY_ENDPOINT>'
    EXTERNAL_OAUTH_AUDIENCE_LIST = ('<SNOWFLAKE_APPLICATION_ID_URI>')
    EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = 'sub'
    EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';
```

> [!NOTE]
>
> Set `EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM` to `sub` because the token is issued for the client, not a user. All values are case-sensitive, including trailing slashes. For more information, refer to the [Create a security integration in Snowflake](<https://docs.snowflake.com/en/user-guide/oauth-azure#create-a-security-integration-in-snowflake>).

#### Coexistence with Power BI SSO

The `EXTERNAL_OAUTH_ISSUER` must be unique per account. If Power BI SSO already uses the same Entra tenant, a second `CREATE` statement fails with the error "*An integration with the given issuer already exists for this account.*"

If this occurs, merge both into a single integration using following command:

```sql
CREATE OR REPLACE SECURITY INTEGRATION EXTERNAL_OAUTH_AZURE
    TYPE = external_oauth
    ENABLED = true
    EXTERNAL_OAUTH_TYPE = azure
    EXTERNAL_OAUTH_ISSUER = '<AZURE_AD_ISSUER>'
    EXTERNAL_OAUTH_JWS_KEYS_URL = (
        '<AZURE_AD_JWS_KEY_ENDPOINT>',
        'https://login.windows.net/common/discovery/keys'
    )
    EXTERNAL_OAUTH_AUDIENCE_LIST = (
        '<SNOWFLAKE_APPLICATION_ID_URI>',
        'https://analysis.windows.net/powerbi/connector/Snowflake'
    )
    EXTERNAL_OAUTH_TOKEN_USER_MAPPING_CLAIM = ('upn','sub')
    EXTERNAL_OAUTH_SNOWFLAKE_USER_MAPPING_ATTRIBUTE = 'login_name';
```

Skip the merged integration if no other integration shares the issuer.

### Verifying the Token Against the Security Integration

Run the following command:

```sql
SELECT SYSTEM$VERIFY_EXTERNAL_OAUTH_TOKEN('<access_token>');
```

A successful output reports `"Validation Result": "Passed"` and the Extracted User claim(s) from token value matches your `<SUB_VALUE>`. Snowflake looks up this value in `LOGIN_NAME`. Record this value.

### Creating the Service User in Snowflake

Create the service user with the following requirements:

- Set `LOGIN_NAME` to `<SUB_VALUE>` from Step 5.
- Set `TYPE` to `SERVICE`.
- Grant the role referenced in the `roles` claim of the token. Optionally, set this as the `DEFAULT_ROLE`.
- Verify that this role is not included in the `EXTERNAL_OAUTH_BLOCKED_ROLES_LIST` on the integration.
- Do not assign high-privilege roles such as `ACCOUNTADMIN`, `SECURITYADMIN`, or `ORGADMIN`.

Run the following command:

```sql
CREATE ROLE ANALYST;
-- GRANT the privileges your connector needs to ANALYST

CREATE USER SNOWSQL_OAUTH_USER
    LOGIN_NAME   = '<SUB_VALUE>'
    TYPE         = SERVICE
    DEFAULT_ROLE = ANALYST
    DISPLAY_NAME = 'SnowSQL OAuth User'
    COMMENT      = 'Service user for OAuth Client-Credentials connectivity';

GRANT ROLE ANALYST TO USER SNOWSQL_OAUTH_USER;
```

After completing these steps, configure OAuth 2.0 fields on the [Account Connection Settings for Snowflake](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-connection-settings-for-snowflake>).

# Provisioning

The JDBC connector supports provisioning, allowing you to create and manage accounts and entitlements in JDBC 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.

Refer to [Provisioning Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#provisioning-query-settings>) for information on configuring provisioning operations using SQL queries or stored procedures.

## Provisioning Rules

The JDBC source does not generate out-of-memory errors when processing provisioning requests. The pool size for BSF tokens in the BSF Manager is changed to optimum level to overcome the out-of-memory issue without having impact on performance.

For Provisioning Rules template and examples, refer to the [Identity Security Cloud Rule Guide - JDBC Provisioning Rule](<https://community.sailpoint.com/t5/IdentityNow-Wiki/IdentityNow-Rule-Guide-JDBC-Provision-Rule/ta-p/77339>) document.

# Schema Reference

The application schema is used to configure the objects returned from a connector.

> [!NOTE]
>
> - The **Account Schema** tab is used to define the attributes for each object type in the application being configured. The schema attributes can be defined as **Entitlement** or **Multi-Valued**. Refer to [Managing Source Account Schemas](<https://documentation.sailpoint.com/saas/help/accounts/schema.html>) for more information.
> - The schema attributes that are not present by [default](<#default-account-schema>) must be defined as strings if not specified.

The JDBC connector uses the following default correlation and object configurations.

## Default Account Schema

Each source supports a variety of details, or attributes, about each user who has an account, such as their name, email address, manager name, and location.

The set of account attributes each source stores and how they're organized is known as the account's *schema*. To best represent your data, you can configure sources to use an account schema matching the one you use in the external connector. Refer to [Managing Source Account Schemas](<https://documentation.sailpoint.com/saas/help/accounts/schema.html>) for more information.

The JDBC connector has no default account attributes. The connector's most important attribute is the SQL Statement. In many cases this is a stored procedure (`call mystoredProcedure`). In other cases it is selected from a table with any number of joins included. If this connector is configured to use the automatic discovery function, it connects to the database and executes the statement provided and then uses the meta-data returned from the result to build the column names.

> [!IMPORTANT]
>
> SQL entries can't end with a semicolon (;). Ending an SQL entry with a semicolon (;) can cause a general error message to display.

Enter the Account SQL query in the source connection before you discover the schema. Refer to [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/account_query_settings.html#account-query-settings>) for more information.

Define the attributes to use for the account schema's Account Name and Account ID.

> [!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 not, source accounts could correlate to the first Identity that includes that ID/Name instead of creating an additional Identity.

Refer to [Discovering an Account Schema](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#discovering-an-account-schema>) and [Editing an Account Schema](<https://documentation.sailpoint.com/saas/help/accounts/schema.html#editing-an-account-schema>) for more information.

## Default Entitlement Attributes

Entitlement attributes define the entitlements, such as groups or roles, that can be assigned to the managed system's user accounts. Refer to [Managing Entitlements](<https://documentation.sailpoint.com/saas/help/access/entitlements.html>) for more information.

> [!CAUTION]
>
> Changing the attribute marked as the Entitlement ID after aggregating accounts or entitlements can result in duplicate entitlements being created, or access profiles and roles breaking. To avoid this, edit the entitlement schema before performing any aggregations.

The JDBC connector has no default entitlement attributes. To aggregate entitlements, you must manually add and configure an entitlement schema for each entitlement type in the JDBC source. Refer to [Creating and Managing Entitlement Types](<https://documentation.sailpoint.com/saas/help/loading_entitlements/entitlement_types.html>) for more information.

The JDBC connector supports multiple group objects for entitlement aggregation. Depending on the managed system that you are connecting with the JDBC source, you can set up attributes such as entitlements and roles. You can add one or more entitlement schemas for aggregation.

Entitlement schemas you add appear in **Query Settings**, where you configure the connection and SQL queries for each. Refer to [Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/va/help/group_query_settings.html#group-schema-query-setup>) for more information.

> [!NOTE]
>
> Once you enter an entitlement type's name, you can't change it later.

## Defining Group Hierarchy

The JDBC source supports the group hierarchy. If the group schema has any attribute which holds the hierarchy of that group type then `hierarchyAttribute` can be defined and `childHierarchy` can be used 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. For more information, refer to [listSchemas](<https://developer.sailpoint.com/docs/api/get-source-schemas-v-1/>) API and [createSchema API](<https://developer.sailpoint.com/docs/api/create-source-schema-v-1/>).

```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
}
```

When you create data for hierarchical attributes, you must preserve the child-parent relationships. For example, refer to the following table showing a relationship between the `Name` and `memberOf` attributes. The hierarchy is `[A > B > C > D > D]`, which is the proper child-parent relationship, so a group aggregation process will execute properly.

| Name | memberOf |
| --- | --- |
| A | B |
| B | C |
| C | D |
| D | D |

# 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](<https://documentation.sailpoint.com/saas/help/index.html>)
- [SailPoint Support Knowledge Base](<https://support.sailpoint.com/csm?id=kb_view2&amp;kb_id=05ff44289f011200550bf7b6077fcfa3>)
- [SailPoint Developer Community](<https://developer.sailpoint.com/>)

## ObjectNotFound Exception During Provisioning Operations

During provisioning, the connector returns an error message similar to the following:

```text
Error : java.lang.Exception: sailpoint.connector.ObjectNotFoundException: Object not found:-- <username>
```

**Resolution**: Do one of the following:

- Recheck the `getObject` SQL query on the managed system. There may be an error with the single account aggregation SQL query.
- If you are creating a new account, confirm that there is no delay with the process. Your DB administrator can assist with this resolution.

## Attribute Values Are Not Updating After Adding IIQDisabled to the Query

After using `IIQDisabled` in a query, attribute values aren't populated following account aggregation or provisioning.

**Resolution**: Ensure that IIQDisabled is written in the query with double quotes: (`""`)

## Connection Keeps Locking Up

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 SailPoint REST API:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

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

## Closed ResultSet Exception During Account and Group Aggregation

During both account and group aggregations, the following exception occurs intermittently:

```text
java.sql.SQLException: Closed Resultset: next
```

**Resolution**: Add the following to the source XML using the SailPoint REST API:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

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

## java.lang.RuntimeException Error

The following error is displayed:

```text
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 SailPoint REST API:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

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

## Unrestructured Data Error

The following error message displays:

```text
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 SailPoint REST API:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

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

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

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

## JDBC Source Fails with AAD Authentication and the MSAL4J Java Library

```text
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

## SQL Compilation Error

```text
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.

## Data Out of Order Exception While Merging When the Index Column Contains Special Characters for Microsoft SQL Database

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

```text
[ 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

> [!NOTE]
>
> You may have to have a collator class to accomplish this. Set the collator on the application if you know how the database is sorting.

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 SailPoint REST API:

   > **Note**
   >
   > For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

   ```json
   {
       "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)](<https://docs.oracle.com/javase/7/docs/api/java/text/RuleBasedCollator.html>).

   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 SailPoint REST API:

   > **Note**
   >
   > For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

   ```json
   {
       "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.

## JDBC Connector Throws an Exception While Configuring Databricks or Amazon Athena

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:

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1>) in the SailPoint Developer Community.

```json
{
    "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.

## JDBC Source's IIQDisabled Field Is Not Updating the Account Status for IBM AS/400

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 users. 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:

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

## Accounts Appear Enabled When Disabled via IIQDisabled Attribute

When you disable an account by setting the `IIQDisabled` attribute to `true`, it should mark the account as disabled, but it still appears to be enabled.

**Resolution**: Check for any extra spaces before or after the `true` values or around the `IIQDisabled` string. Extra spaces may prevent the system from correctly detecting `true` as a valid value, so the system accepts the default `false` value.

## Test Connection Results in an Error

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

```text
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:

```text
jdbc:dbtype:@ipaddress:databasename
```

For example:

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

## Delta Aggregation Does Not Escape Characters from Delta Aggregation Table

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:

   ```sql
   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:

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

## Create Account Operation Fails While Using Recommended Mappings in the Account Policy

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.

## Mismatch in the Count Between Aggregated Accounts and the Database When Merging During Account Aggregation and Partitioning Processes

After enabling and adding partitioned queries, if there is a mismatch in the number of accounts between the database and what the connector is pulling in, check the partitioning queries.

**Resolution**: Partition queries should not overlap accounts. This means an account should not be pulled by multiple partitions. Create partition queries where it is possible to pull a set of accounts only once. For example, create a partition that aggregates a range of employees IDs. If merging is enabled, then each partition statement should have an `order by` clause. For more information, refer to [Partitioning Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/va/help/aggregation.html#partitioning-aggregation>).

## Provisioning Rule Executing Multiple Times

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:

   ```java
   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;
   ```

## Test Connection Using Windows Authentication Fails

After configuring Windows authentication, test connections fail.

**Resolutions:** Do one of the following:

- Enter the domain name in all capital letters. For example:

  ```text
  jdbc:sqlserver://MSSQLSERVER.SQLKERB.LAB;trustServerCertificate=true;integratedSecurity=true;authenticationScheme=JavaKerberos;
  ```
- Refer to the [Kerberos Setup Checklist](<https://community.sailpoint.com/knowledge/Kerberos-Authentication-Setup-Checklist>) and confirm that Windows authentication and Kerberos are configured correctly.

## Kerberos Authentication Failure with GSSException: Invalid Name Provided

Test connections fail with the following error:

```text
[ ConnectorException ] [ Error details ] Error occurred in Kerberos Authentication while test configuration operation. GSSException: Invalid name provided (Mechanism level: KrbException: Cannot locate default realm)
```

**Resolution:** Recheck the configuration for the `krb5.config` file. It should be configure exactly as instructed in the [Creating a Kerberos configuration file](<https://learn.microsoft.com/en-us/sql/connect/jdbc/using-kerberos-integrated-authentication-to-connect-to-sql-server?view=sql-server-ver16#creating-a-kerberos-configuration-file>) topic in the Microsoft documentation. For more information, refer to [Windows Authentication and Kerberos](<https://documentation.sailpoint.com/connectors/jdbc/va/help/connecting.html#windows-authentication-and-kerberos>).

> [!NOTE]
>
> The linked document is not maintained by SailPoint and is subject to change without notice.

## Invalid Configuration Exception During Kerberos or Windows Authentication

During Kerberos or Windows authentication, you receive the following error, even with the correct credentials configured.

```text
[InvalidConfigurationException] [Possible suggestions]
Login failed for the service account user. This may be due to incorrect credentials or insufficient permissions.
[Error details]
The server encountered an unexpected error while contacting the target system. Please check the logs.
Cannot log in with the Kerberos principal; verify the credentials.
Kerberos login failed: Integrated authentication failed.
LoginException: Message stream modified (41)
```

**Resolution:** Enter the Kerberos username in UPN format and with all capital letters. For example:

```text
USER@DOMAIN.COM
```

## Skip Single Account Aggregation (GetObject) After Provisioning

**Resolution:** Add the following entry in Application.xml

```xml
<entry key="skipGetObjectAfterProvision">
    <value>
        <Boolean>true</Boolean>
    </value>
</entry>
```

## Retry Count Does Not Work as Expected

**Resolution:** Verify that the `maxRetryCount` attribute is configured in the following format:

```xml
<entry key="maxRetryCount" value="5"/>
```

> [!NOTE]
>
> Enter the value as string.

## Kerberos Authentication Failing with Defective Token Detected

Test Connection fails with the following error:

```text
[ ConnectorException ] [ Error details ] Error occurred in Kerberos Authentication while test configuration operation. GSSException: Defective token detected (Mechanism level: AP_REP token id does not match!)
```

**Resolution:** Do the following:

- **Validate Active Directory Account Encryption**: Ensure the service account used for the connection has the correct encryption types enabled in Active Directory. In the account properties, verify that **AES 128-bit** and **AES 256-bit** are enabled. Legacy or mismatched types, such as RC4, often cause intermittent authentication failures or "Defective Token" errors in secured environments.
- **Verify Hostname and DNS Alias (SPN Integrity)**: Ensure the JDBC connection string uses the actual host name (A record) instead of a DNS alias (CNAME). Since the Service Principal Name (SPN) is registered to the physical server host, using an alias prevents the Kerberos ticket exchange because the returned token ID does not match the expected SPN of the alias.

## Test Connection Fails with Communications Link Failure or unknown\_ca When Connecting to MySQL over TLS (useSSL=true) with a Custom Keystore or Truststore

When you enable TLS for a MySQL JDBC application (`useSSL=true`), the connection test might fail even if the database is reachable and you configured SSL with a custom keystore or truststore.

Test fails with following error:

```text
The server encountered an unexpected error while contacting the target system during the test configuration operation. Please check the logs. Communications link failure. The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server.
```

**Resolution**: Add `fallbackToSystemKeyStore=false` to the JDBC URL. This ensures the MySQL driver uses only the certificates specified in your connection string and does not fall back to the JVM system keystore.

For example:

```text
jdbc:mysql://<Hostname>:<Port>/<Database_Name>?useSSL=true&fallbackToSystemKeyStore=false
```

> [!NOTE]
>
> If Partition Aggregation is enabled, ensure the same JDBC URL and certificate paths are configured on all IIQ task servers, not only the UI server.

## Dataset Query Settings Page Is Not Visible

The Dataset Query Settings page is not visible in the source configuration.

**Resolution**: The `AGENTIC_IDENTITY_GOVERNANCE` product flag must be enabled for your tenant. Contact your SailPoint representative to enable the product flag.

## No Resource Schemas Found During Dataset Aggregation

The following message is displayed:

```text
No resource schemas found on application for dataset aggregation
```

**Resolution**: Verify that you created resource schemas under the correct dataset in Dataset Management. Ensure the dataset ID configured on the resource schemas matches the dataset being aggregated.

## SQL Execution Returned No ResultSet for Resource

The following message is displayed:

```text
SQL execution returned no ResultSet for resource <schema_name>, skipping
```

**Resolution**: Verify that the SQL query is entered in Dataset Query Settings for the resource schema. Go to Dataset Query Settings, select the resource schema card, and enter the SQL query in **Query Settings**.

## ResultSet Type Is Not Supported

The following message is displayed:

```text
ResultSet type 1004 is not supported
```

**Resolution**: Set the `resultSetType` source attribute to `TYPE_FORWARD_ONLY` in the **Additional Connection Parameters**. This is a known requirement for Snowflake databases.

For example:

```text
resultSetType=TYPE_FORWARD_ONLY
```

## Athena Azure JDBC Source Test Connection Errors

### Invalid Jar File athena-jdbc-xxx.jar

Invalid jar file athena-jdbc-xxx.jar, should not contain connector framework classes or packages ex. openconnector.Connector, org.apache.log4j, org.apache.commons.logging, org.apache.logging.log4j, org.aspectj

Message Status(500) Error response from connector: `software/amazon/awssdk/services/datazone/DataZoneClient`

**Resolution**: Check with SailPoint Support to confirm whether a different Athena JDBC jar should be used.

### Column Initialization Failure

Exception during column initialization: `com.simba.athena.support.exceptions.GeneralException: AWS_CLIENT_ERR`

**Resolution:** Verify if a credential provider is enabled. If so, disable the credential provider and embed AWS UID/PWD directly in the JDBC URL in your source.

For example:

```xml
<entry key="url" value="jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://aws-athena-query-results-57...58-us-east-1/;UID=AK...XQ;PWD=S0...GL"/>
```

### Signature Mismatch

sailpoint.connector.ConnectorException: An error has been thrown from the AWS Athena client. The request signature we calculated does not match the signature you provided. Check your AWS Secret Access Key and signing method. Consult the service documentation for details.

**Resolution:** Verify if a credential provider is enabled. If so, disable the credential provider and embed AWS UID/PWD directly in the JDBC URL in your source.

For example:

```xml
<entry key="url" value="jdbc:awsathena://AwsRegion=us-east-1;S3OutputLocation=s3://aws-athena-query-results-57...58-us-east-1/;UID=AK...XQ;PWD=S0...GL"/>
```

# 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](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) 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": [
            "<error_message_1>",
            "<error_message_2>"
        ]
    }
]
```

> [!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"
> }
> ```

## Maximum Retry Count

The `maxRetryCount` parameter enables you to configure the maximum number of attempts the connector retries a failing operation before terminating it.

- **Description**: Sets the maximum number of attempts before terminating a failing operation.
- **Type**: Integer
- **Default**: `2`

Set the parameter as follows using the REST API:

```http
PATCH https://{orgName}.api.identitynow.com/v3/sources/:id
```

In the body of the PATCH, enter the maximum retry count as follows:

```json
[
    {
        "op": "add",
        "path": "/connectorAttributes/maxRetryCount",
        "value": <required_value>
    }
]
```

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

## Wait Time Before Retry

Use the `waitTimeBeforeRetry` to configure the duration of time in seconds that the connector waits before retrying a failed operation.

- **Description**: Sets the wait time in seconds.
- **Default**: The default value is null. The connector retries failed attempts immediately.

Set the parameter as follows using the REST API:

```http
PATCH https://{orgName}.api.identitynow.com/v3/sources/:id
```

In the body of the PATCH, enter the maximum retry count as follows:

```json
[
    {
        "op": "add",
        "path": "/connectorAttributes/waitTimeBeforeRetry",
        "value": <required_value>
    }
]
```

> [!NOTE]
>
> For more information on SailPoint's REST APIs, refer to [Best Practices: REST API Authentication](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) in the SailPoint Developer Community.

# 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](<https://developer.sailpoint.com/docs/api/authentication/>) and [REST API - Update Source (Partial)](<https://developer.sailpoint.com/docs/api/update-source-v-1/>) 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"
    }
]
```

# Using TLS

JDBC sources support connections through TLS for the databases listed below. For more information, select one of the following databases:

## Oracle Database

- Save the Oracle Server certificates on the virtual appliance at location `/home/sailpoint/certificates` and restart the CCG.
- Provide the `PROTOCOL`, `HOST`, `PORT`, and `SERVICE_NAME` parameters in Oracle database application URL in the following format:

  ```text
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=<host_name>)(PORT=<port number>))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = <service_name>)))
  ```

  For example:

  ```text
  jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=tcps)(HOST=WIN-0PR71D80V83)(PORT=2500))(CONNECT_DATA=(SERVER = DEDICATED)(SERVICE_NAME = orcx)))
  ```

## Microsoft SQL Server

1. On the source VA, log in with the SailPoint user.
2. Go to `/home/sailpoint/certificates`.
3. The final certificate file obtained from the server must be placed in this directory.
4. In the `hosts.yaml` file, make an entry of the SQL Server host in the `XXX.XX.XX.XXX <SQL Server hostname>` format.

   > **Note**
   >
   > For information on creating a hosts.yaml file, refer to [Configuring a Hosts.yaml File](<https://documentation.sailpoint.com/saas/help/va/config_va.html#configuring-a-hostsyaml-file>).
5. Restart the CCG with the `sudo systemctl restart ccg` command.
6. Create a source on SailPoint with the following URL:

   ```text
   jdbc:sqlserver://<SQL Server hostname>:<port on which TLS is configured>;encrypt=true;trustServerCertificate=false;hostNameInCertificate=<SQL Server hostname>
   ```

## Sybase (SAP ASE)

1. Import the SSL certificates from the Sybase SAP ASE server to `/home/sailpoint/certificates` directory on the source VA.
2. Restart the CCG with the following command:

   ```bash
   sudo systemctl restart ccg
   ```
3. In configuration of Sybase SAP ASE source config for the URL field, use the following URL:

   ```text
   jdbc:sybase:Tds:<HOST>:<PORT>/?ENABLE_SSL=true
   ```

## IBM DB2

1. On the source VA, login with the SailPoint user.
2. Go to `/home/sailpoint/certificates`.
3. The final certificate file obtained from the server must be placed in this directory.
4. In the hosts.yaml file make an entry of the DB2 Server host, in the `xxx.xx.xx.xxx <DB2 server hostname>` format.

   > **Note**
   >
   > For information on creating a hosts.yaml file, refer to [Configuring a Hosts.yaml File](<https://documentation.sailpoint.com/saas/help/va/config_va.html#configuring-a-hostsyaml-file>).
5. Restart the CCG using the following command: `sudo systemctl restart ccg`
6. Create a source on SailPoint with the following URL:

   ```text
   jdbc:db2://<host>:<port on which TLS is configured>/<Database>:sslConnection=true;
   ```

## SAP HANA

1. Import the SSL certificates from the SAP HANA Server to the `/home/sailpoint/certificates` directory on the source VA.
2. In the hosts.yaml file enter the SAP HANA Server in the `xxx.xx.xx.xxx <sap hana server hostname>` format.

   > **Note**
   >
   > For information on creating a hosts.yaml file, refer to [Configuring a Hosts.yaml File](<https://documentation.sailpoint.com/saas/help/va/config_va.html#configuring-a-hostsyaml-file>).
3. Restart the CCG with the `sudo systemctl restart ccg` command.
4. In the configuration of the SAP HANA source for the **Database URL** field, use the following URL mentioned in the SSL certificate:

   ```text
   jdbc:sap://:/?databaseName=&encrypt=true&hostNameInCertificate=<HostName mentioned in certificate>
   ```