# SailPoint JDBC SaaS Connector Documentation

> JDBC SaaS 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/saas/help/img/query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_settings.png>)

Select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/jdbc_conn_other.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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.
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 [Upload a JAR File Using Connectivity Customizers](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/upload_jar_using_customizer.html>). 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/saas/help/img/jdbc_conn_snowflake_auth.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/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/saas/help/img/jdbc_conn_snowflake_keypair.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/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/saas/help/img/jdbc_query1_1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query1_1.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query2.png>) [![Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_query3.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/jdbc_addquery_saas.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_addquery_saas.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/saas/help/aggregation.html#delta-aggregation-tables>).

> [!NOTE]
>
> **Screenshot**
>
> [![Delta Aggregation](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_deltaagg.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/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 EMPLOYEES_DELTA;
   ```

   For more information, refer to [Delta Aggregation Tables](<https://documentation.sailpoint.com/connectors/jdbc/saas/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

You can configure provisioning operations through a series of SQL queries and stored procedures.

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

Create SQL queries and stored procedures to handle provisioning in the JDBC SaaS connector.

To create provisioning operations in the connector configuration, select **Configure Provisioning Operations in the Connector**, then select any of the following options:

### 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$)
```

If the database name is already specified as an additional attribute in the query settings:

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

For example:

```sql
insert into Employee (name, city) values ($plan.NAME$, $plan.CITY$)
```

If the native identity is included in the provisioning plan:

```sql
insert into Employee (EmpID, 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, `EmpID` is returned as the native identifier in the following SQL query:

```sql
select EmpID from Employee where firstName=$plan.firstName$
```

#### Stored Procedure

The stored procedure format is as follows:

```sql
CALL InsertEmployee($plan.name$, $plan.city$, $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 SaaS 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
     CALL InsertEmployeeRole($plan.nativeIdentity$, $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
     CALL InsertEmployeeRole($plan.nativeIdentity$, $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
     CALL DeleteEmployeeRole($plan.nativeIdentity$, $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.SchemaName.[TableName] set AttributeName='value' where AttributeName=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL StoredPrecedureName($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 Employee set isactive='1' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeStatusDisable($plan.nativeIdentity$)
```

##### Disable Account Examples

Query:

```sql
update Employee set isactive='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeStatusDisable($plan.nativeIdentity$)
```

##### Unlock Account Examples

Query:

```sql
update Employee set islock='0' where empid=$plan.nativeIdentity$
```

Stored Procedure:

```sql
CALL UpdateEmployeeLockStatus($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.

1. Enter the query into the **Account SQL Query** field in the [Account Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/account_query_settings.html#account-query-settings>).
2. Go to **Account Management \> Account Aggregation** and select **Start Aggregation**.
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.

## 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/saas/help/group_query_settings.html#group-query-settings>).
2. Go to **Entitlement Management \> Entitlement Aggregation** and select **Start Aggregation**.
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/saas/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/saas/help/group_query_settings.html#group-query-settings>).

### Account Status

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 SaaS 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/saas/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:

   - One table to use for account aggregation, where all identity data resides.
   - Another table to capture only those identities who are undergoing modifications. This table must contain two columns:

     - 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 accounts in SAP HANA Cloud Database is as follows:

   ```sql
   CREATE COLUMN TABLE EMPLOYEES_DELTA (EMPLOYEE_ID NVARCHAR (20), ACTION NVARCHAR(10));
   ```
2. Assign the privileges to read and delete the records from the tables you created in the previous step to the connection user whose credentials you entered in the source configuration page within SailPoint.
3. Create triggers on the master table (the account aggregation table) which activate during aggregation processes. The triggers should cause the connector to store modifications in the delta table.

   For example, in SAP HANA Cloud databases, the example trigger below writes the `EMPLOYEE_ID` in the account aggregation table, which has undergone modifications, along with the respective action.

   #### Key

   - Table for account - `EMPLOYEES`
   - Delta table for account - `EMPLOYEES_DELTA`

   ```sql
   CREATE TRIGGER TRIEMPDELTA
   AFTER INSERT OR UPDATE OR DELETE
   ON MYSCHEMA.EMPLOYEES
   FOR EACH ROW
   BEGIN
       -- For INSERT operations
       IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NULL THEN
           INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
           VALUES (:NEW.EMPLOYEE_ID, 'Insert');
       END IF;

   -- For UPDATE operations
       IF :NEW.EMPLOYEE_ID IS NOT NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
           INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
           VALUES (:NEW.EMPLOYEE_ID, 'Update');
       END IF;

   -- For DELETE operations
       IF :NEW.EMPLOYEE_ID IS NULL AND :OLD.EMPLOYEE_ID IS NOT NULL THEN
           INSERT INTO MYSCHEMA.EMPLOYEES_DELTA (EMPLOYEE_ID, ACTION)
           VALUES (:OLD.EMPLOYEE_ID, 'Delete');
       END IF;
   END;
   ```
4. After you finish the delta aggregation the tables are reset/re-initialized and start capturing changes again.

## Aggregation Retry Configuration

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

You can update these parameters 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.

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

# Connecting SailPoint and JDBC SaaS

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. (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>).
9. 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>).
10. 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. (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>).
4. Configure the authentication to allow SailPoint to connect to the source. Refer to [Connection Settings](<#connection-settings>) for more information.
5. (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>).
6. (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.
7. Select **Save**.

## Connection Settings

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

### Upload the JDBC Driver

Before you can use the JDBC SaaS source, you must upload the JDBC driver .jar file using a connectivity customizer. For more information, refer to [Upload a JAR File Using Connectivity Customizers](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/upload_jar_using_customizer.html>).

## 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/saas/help/img/dataset_query_settings_agent_governance.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/dataset_resource_connection_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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 SaaS 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/saas/help/img/dataset_resource_query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/dataset_resource_additional_query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/query_settings.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/query_settings.png>)

Select the **Edit** icon ![](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/account_query_settings.html#account-connection-settings>).

> [!NOTE]
>
> **Screenshot**
>
> [![Group Connection Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_group_conn_saas.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_group_conn_saas.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/saas/help/img/jdbc_group1.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_group1.png>) [![Group Query Settings](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/img/jdbc_group2.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/img/jdbc_dp.png>)](<https://documentation.sailpoint.com/connectors/jdbc/saas/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/saas/help/account_query_settings.html#direct-permissions>).
3. Select **Save**.

# Integrating SailPoint with JDBC SaaS

If you want to load user data, you can create a direct connect source for JDBC SaaS 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.

You can use Dataset Management to aggregate non-human identity resources, including AI agents, applications, capabilities, credentials, and devices. For more information, refer to [Dataset Management](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/datasets.html>).

> [!CAUTION]
>
> JDBC SaaS 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

The SailPoint JDBC SaaS connector supports the following features:

- Full Account Aggregation
- Single Account Aggregation
- Delta Account Aggregation
- Entitlement Aggregation
- Enable/Disable account
- Update account attributes
- Add/remove entitlements from account
- Unlock accounts
- Multiple group objects for entitlement aggregation
- Group Hierarchy Representation
- Direct Permission Representation
- Delete accounts

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

> [!IMPORTANT]
>
> You must add the entitlement schema in the JDBC SaaS source configuration for entitlement aggregation. For more information, refer to [Default Entitlement Attributes](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/schema.html#default-entitlement-attributes>).

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

- Identify or create a service account on the database you will access through JDBC.
- An appropriate JDBC Driver for the cloud database.
- Upload a .jar file using the customizer. For more information, refer to [Upload a JAR File Using Connectivity Customizers](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/upload_jar_using_customizer.html>).
- To load entitlements, you must add the entitlement schema in the JDBC SaaS source configuration for entitlement aggregation. For more information, refer to [Default Entitlement Attributes](<https://documentation.sailpoint.com/connectors/jdbc/saas/help/schema.html#default-entitlement-attributes>).
- 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>).

## System Requirements

The JDBC SaaS source should be able to access cloud databases that have a JDBC driver.

## Permissions

The JDBC SaaS 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/saas/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/saas/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/saas/help/account_query_settings.html#account-connection-settings-for-snowflake>).

# Provisioning

The JDBC SaaS 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/saas/help/account_query_settings.html#provisioning-query-settings>) for information on configuring provisioning operations using SQL queries or stored procedures.

## Provisioning Rules

The JDBC SaaS 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 SaaS 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 SaaS 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/saas/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 SaaS 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 SaaS source. Refer to [Creating and Managing Entitlement Types](<https://documentation.sailpoint.com/saas/help/loading_entitlements/entitlement_types.html>) for more information.

The JDBC SaaS connector supports multiple group objects for entitlement aggregation. Depending on the managed system that you are connecting with the JDBC SaaS 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/saas/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.

# 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/>)

## Debug Logs

For information about managing additional source settings, including debug logging and downloading connector logs, refer to [Managing Additional Settings](<https://documentation.sailpoint.com/saas/help/sources/managing_additional_settings.html>).

## 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: (`""`)

## InvalidConfigurationException

```text
Error occurred before establishing the connection with Server. java.lang.ClassNotFoundException : d}
```

**Resolution**:

- Verify that the driver details are correct.
- The connector may be missing a required .jar file. Add the required .jar file using the customizer.

## Stored Procedure Is Incomplete and Rolled Back

**Issue**: `net.snowflake.client.JDBC Saas.SnowflakeSQLException:  Scoped transaction started in stored procedure is incomplete and it was rolled back.`

**Resolution**: Ensure that each stored procedure with a transaction includes a corresponding COMMIT or ROLLBACK.

For example:

```sql
CREATE OR REPLACE PROCEDURE my_proc()
RETURNS STRING
LANGUAGE SQL
AS
$$
BEGIN
    BEGIN TRANSACTION;
    -- Your SQL statements
    INSERT INTO my_table VALUES (1, 'Test');
    -- Must commit or rollback before the procedure ends
    COMMIT;
    RETURN 'Success';
END;
$$;
```

# Upload a JAR File Using Connectivity Customizers

Use a connectivity customizer to upload the required .jar files for your JDBC SaaS source.

> [!NOTE]
>
> For more information on the processes described on this page, refer to the following articles in the SailPoint Developer Community.
>
> - [Building and uploading to Identity Security Cloud](<https://developer.sailpoint.com/docs/connectivity/saas-connectivity/customizers/upload/>)
> - [Linking a connectivity customizer to a source](<https://developer.sailpoint.com/docs/connectivity/saas-connectivity/customizers/linking/>)

## Prerequisites

Before you can upload a .jar file to the JDBC SaaS source complete the following:

- Create an empty customizer project then create the connector customizer that you'll use to upload the .jar file.
- Create the JDBC SaaS source in SailPoint.

### Creating the Empty Customizer Project

Set up an empty connector customizer project, which is necessary before creating and uploading a customizer. To create an empty connector customizer project, run the following command:

```bash
sail conn customizers init my-customizer-project
```

### Creating the Connector Customizer

To create an empty connector customizer, run the create command:

```bash
sail conn customizers create my-connector-customizer
```

This command returns the customizer ID, as shown in the following example:

```text
+--------------------------------------+-------------------------+---------+
|                  ID                  |          NAME           | VERSION |
+--------------------------------------+-------------------------+---------+
| 7b968fab-0f40-49f0-b13b-8bf529fc0b82 | my-connector-customizer |         |
+--------------------------------------+-------------------------+---------+
```

## Building the Customizer

Prepare the .jar file for upload by copying it to the appropriate directory and creating a .zip file containing the .jar file.

1. Copy the .jar file to the respective directory.
2. Create a .zip file of your .jar file using the following command:

   ```bash
   zip <file_name.zip> <file_name.jar>
   ```

## Connectivity Customizer to Source Link Commands

This section lists the commands you can use to manage connections between connectivity customizers and sources.

### Find Sources

Use this command to find sources:

```bash
sail conn instances list
```

The system returns a list of sources with their ID, name, and the ID of any customizers that are already linked to it:

```text
+--------------------------------------+--------------------------+---------------+
|                  ID                  |           NAME           | CUSTOMIZER ID |
+--------------------------------------+--------------------------+---------------+
| 0327d0a4-5e94-4803-90a6-c0819349ecca | airtable test            |               |
| 05da3fa2-17f0-4bd4-9e2a-ab93d7dffe68 | Discourse                |               |
| 069b9398-8ed9-433d-943f-3cb32952524f | github                   |               |
| 0dbb53b0-2e62-4a8e-9efe-97555755025d | Smartsheet               |               |
| edfc9bfb-b55c-482f-b1aa-b4d51caf7558 | JDBC SaaS Source         |               |
+--------------------------------------+--------------------------+---------------+
```

### Find Customizers

Use this command to find customizers:

```bash
sail conn customizers list
```

The system returns a list of customizers, with the customizers' ID, name, and Version:

```text
+--------------------------------------+-------------------------+---------+
|                  ID                  |          NAME           | VERSION |
+--------------------------------------+-------------------------+---------+
| 7b968fab-0f40-49f0-b13b-8bf529fc0b82 | my-connector-customizer |   1     |
+--------------------------------------+-------------------------+---------+
```

### Link to a Source

To link a source to a customizer, find the source ID in the instance list and a customizer from the customizer list. Then issue this command:

```bash
sail conn customizers link -i <source_instance_id> -c <customizer_id>
```

Following a successful link, the customizer's ID is next to the source's ID in the list of sources:

```text
+--------------------------------------+----------------------+--------------------------------------+
|                  ID                  |         NAME         |            CUSTOMIZER ID             |
+--------------------------------------+----------------------+--------------------------------------+
| edfc9bfb-b55c-482f-b1aa-b4d51caf7558 | JDBC SaaS Source     | 7b968fab-0f40-49f0-b13b-8bf529fc0b82 |
+--------------------------------------+----------------------+--------------------------------------+
```

### Unlink from a Source

To unlink a customizer from a source, issue the unlink command and pass the source instance ID:

```bash
sail conn customizers unlink -i <source_instance_id>
```

## Uploading the Customizer to SailPoint

Upload the customizer to SailPoint using the upload command, specifying the customizer ID and the .zip file containing the .jar file.

To upload the .jar file using the customizer to SailPoint, run the upload command:

```bash
sail conn customizers upload -c <your_customizer_ID> -f <file_name>.zip
```

Replace `<your_customizer_ID>` with the customizer ID obtained during the creation step, and `<file_name>.zip` with the name of your .zip file.