# Connecting SailPoint and SQL Loader

To connect SailPoint with SQL Loader, perform the following tasks:

## Adding the Source in SailPoint

To connect to SailPoint, perform the following steps. Once the connection is made, you can configure it to meet your company's needs.

> [!IMPORTANT]
>
> By default, the provisioning feature is **disabled**, and the connector only supports read-only operations. To support provisioning operations, you must manually enable the feature in the source configuration.

**To configure a new source:**

1. Sign in to SailPoint.
2. Go to **Admin \> Connections \> Sources**.
3. Select **Create New**.
4. Search for the connector by entering the name in the search box, and then select **Configure**.
5. In the **Source Name** field, enter a unique and descriptive name for your source. Source names cannot exceed 128 characters.
6. In the **Description** field, provide additional details about the source to help distinguish it from similar sources. This field allows a maximum of 2,000 characters.
7. In the **Source Owner** field, begin typing the name of an owner. Matches appear after you type two or more letters. For more information, refer to [Assigning a Source Owner](<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**.

## Query Settings

Set up how the connector queries the SQL Loader source for account and group schema information.

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

Select the edit button for the schema you want to configure to launch additional settings.

### Account Schema Query Setup

Perform the following tasks to configure the queries for SQL Loader accounts.

#### Connection Settings

Enter the connection information to allow SailPoint to locate and connect to the SQL Loader server to load account information, as well as for any account-related functions.

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

1. Enter the service account user name making the connection to the data files into the **User Name** field.
2. Enter the service account password into the **Password** field.
3. Enter the URL to the data files into the **Data Files URL** field. For more information, refer to [Data Files URL Formatting](<#data-files-url-formatting>).

   The address should be in the following formats, depending on the data file type:

   - CSV files - `jdbc:csv:ftp://<host/ server IP>/<directory path>`
   - Text files - `jdbc:text:ftp://<host/ server IP>/<directory path>`

   CSV file example:

   `jdbc:csv:ftp://myhost/home/testdata`, `jdbc:csv:ftp://000.00.00.00/home/testdata`
4. Enter the HXTT text driver used to communicate to the CSV or text file into the **Driver Class** field.

   For example:

   `com.hxtt.sql.text.TextDriver`
5. Add any additional parameters into the **Additional Connection Parameters** field in the form of a key value pair. If multiple parameters need to be specified, they need to be passed in a new line.

   For example:

   `_CSV_Header=true`

   `_CSV_Separator=,`

   > **Note**
   >
   > If you set the `_CSV_Header=` parameter to `true`, the first row of the CSV or text file is treated as a column header. If you set it to `false`, then the first row is treated as the account record.
   >
   > The value you set for the `_CSV_Separator=` parameter is used to separate the values of the fields in the CSV and text files.
6. Select **Save**.

#### Account Query Settings

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

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

1. Enter a simple SQL query to test the connection to the database in the **Test Connection SQL Query** field.
2. Enter a SQL query to load accounts into the **Account SQL Query** field.
3. Enter a SQL query to load a single account into the **Single Account SQL Query** field.
4. Select **Save**.

#### Additional Account Query Settings

Set additional query settings to support the SQL queries.

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

1. Select the **Use executeQuery** checkbox to execute an SQL query for additional settings. Use `Statement.executeQuery()` instead of the default `Statement.execute()`.
2. Enable **Direct Permission** and add the SQL query. For more information, refer to [Direct Permissions](<https://documentation.sailpoint.com/connectors/sql_loader/va/help/aggregation.html#direct-permissions>).
3. Select **Save**.

### Group Schema Query Setup

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

> [!NOTE]
>
> Groups are used for entitlements.

#### Group Connection Settings

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

> [!NOTE]
>
> If these fields are left blank, the connector uses the account [Connection Settings](<#connection-settings>).

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

1. Enter the **User Name** required to access the data files server.
2. Enter the **Password** associated with the user name to access the server.
3. Enter the server's address into the **Data Files URL** field. For more information, refer to [Data Files URL Formatting](<#data-files-url-formatting>).

   The address should be in the following format:

   `jdbc:dbtype://ipaddress/databasename`

   For example:

   `jdbc:oracle:thin:@172.16.23.155:1521:orcl`
4. Enter the **Driver Class**.

   For example:

   `oracle.jdbc.driver.OracleDriver`
5. Select **Save**.

#### Group Query Settings

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

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

1. Enter a SQL query to load groups into the **Group SQL Query** field.
2. Enter a SQL query to load a single group into the **Single Group SQL Query** field.
3. Select **Save**.

#### Additional Group Query Settings

Set additional query settings to support the SQL queries.

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

1. Select the **Use executeQuery** checkbox to execute an SQL query for additional settings. Use `Statement.executeQuery()` instead of the default `Statement.execute()`.
2. Enable **Direct Permission** and add the SQL query. For more information, refer to [Direct Permissions](<https://documentation.sailpoint.com/connectors/sql_loader/va/help/aggregation.html#direct-permissions>).
3. Select **Save**.

## Data Files URL Formatting

Use the following table to determine the URL format you need to enter into the **Data Files URL** field in the SQL Loader **Account Connection** and **Group Connection**, based on your server protocol and file type.

| Protocol Server | File Type | Example |
| --- | --- | --- |
| FTP server | .csv | `jdbc:csv:ftp://<host/ server IP>/<directory path>` |
| FTP server | .txt | `jdbc:text:ftp://<host/ server IP>/<directory path>` |
| SFTP server | .csv | `jdbc:csv:sftp://<host/ server IP>/<directory path>` |
| SFTP server | .txt | `jdbc:text:sftp://<host/ server IP>/<directory path>` |
| HTTP server | .csv | `jdbc:csv:http://www.<http_host_path>` |
| HTTP server | .txt | `jdbc:text:http://www.<http_host_path>` |
| HTTPS server | .csv | `jdbc:csv:https://www.<https_host_path>` |
| HTTPS server | .txt | `jdbc:text:https://www.<https_host_path>` |

## SQL Query Example

For the **Account SQL Query** and **Group SQL Query** fields, see the following join query example:

The Employee.csv file has the following columns:

- ID
- FullName
- Email
- ContactNo

The EmpDept.csv file has the following columns:

- ID
- DeptNam
- Role

To aggregate data from Employee.csv and EmpDept.csv, the JOIN query would be as follows:

```sql
select e.ID, e.FullName, e.Email, e.ContactNo, d.DeptName, d.Role
from Employee e , EmpDept d
where e.ID=d.ID
```

> [!NOTE]
>
> When the file name is Employee.csv in the SQL query, you must mention only "Employee"; the ".csv" file extension is not required.

## Review and Test

Perform a test to confirm the connection to SailPoint.

1. Go to **Review and Test**.
2. Confirm that the entries in each field are correct.

   If you note any mistakes, return to the section and make corrections.
3. Select **Test Connection**.

## SailPoint Application Onboarding

SailPoint application onboarding enables you to easily onboard new applications and create sources following the successful onboarding of a discovery source. To use this feature, continue to **Discovered Applications** in the SailPoint user interface.

For more information, refer to [Application Onboarding](<https://documentation.sailpoint.com/saas/help/ai/app_onboarding/index.html>) in the SailPoint SaaS documentation.