Skip to content

Integrating SailPoint with SQL Loader

This document is designed to give specific information about the requirements and field definitions needed to get a working instance of a SQL Loader connector.

The SailPoint SQL Loader connector supports read/write operations on flat file data. The flat file can be either a text file (.txt) or a CSV file (.csv). The data in these files are indexed with delimiters. This connector can handle aggregation for multiple files by defining a complex SQL query.

Note

The SQL Loader connector may cause intermittent timeouts during aggregation and provisioning operations for all supported protocols.

Supported Features

Note

Before you can use any of the following items marked with an asterisk (*), SailPoint must activate the feature for your site.

The SailPoint connector for SQL Loader supports the following features:

  • Load accounts
  • *Provision accounts
  • *Access Certifications (certification of entitlements connected to accounts)
  • *Password management
  • Delete accounts

Note

The SQL Loader source may cause intermittent timeouts during aggregation and provisioning operations for all supported protocols.

For more information on features, refer to Source Features.

Caution

When configuring aggregation or provisioning queries that include a condition or a JOIN action, the column specified in the condition or JOIN cannot contain more than 19 characters. If the column has more than 19 characters, the query may return incomplete results.

For example:

  • SELECT t.id, t.name, u.group FROM ProvisioningUser t,ProvisioningUserGroupAssignment u where t.id=u.id

    Where the column for calculating the condition is id. If this column contains a value with a length of more than 19 characters, the queries may return incomplete results.

  • SELECT u.MAIL_ADDRESS as MAIL_ADDRESS, u.ROLE_SPECIALISM as ROLE_SPECIALISM, ug.GROUP_NAME as groups FROM Users u LEFT OUTER JOIN UsersGroups ug ON u.MAIL_ADDRESS = ug.MAIL_ADDRESS ORDER BY MAIL_ADDRESS

    Where the JOIN column is MAIL_ADDRESS. If this column contains a value with a length of more than 19 characters, the queries may return incomplete results.

Concurrent provisioning operations may fail in any situation where multiple concurrent processes are trying to edit it at the same time. This is a limitation of the end repository being a flat file that does not have active processing on its end. It lacks the capability to multithread and resolve write access conflicts.

Password Management

If Password Management is enabled for your organization, you can change the password for a user of the source. After enabling password management on the source, be aware of the following:

  • The password policy defined in SailPoint applies to accounts on this source.
  • If you change the password for an application connected to this source, the change will automatically be made to the source itself.

For more information on configuring password management, refer to Password Management Overview.

Prerequisites

  • Configure at least one virtual appliance cluster and successfully test the connection. For more information, refer to the Virtual Appliance Reference Guide.
  • Identify or create a service account to access the data files.

Supported Protocols

The SQL Loader connector reads and writes data from flat file data, which uses a delimiter character to index the information. The flat file can be either a text file (.txt) or a CSV file (.csv).

The SQL Loader connector supports the file transfer protocols listed in the following table when a file is in a shared or remote location and is accessible to the configured Virtual Appliance cluster. The file sharing protocol supported varies depending on the server platform in use:

File Transfer Protocol Supported Platform Supported Operations
FTP Linux Read/Write
SFTP Linux Read/Write
HTTP Linux Read
HTTPS Windows and Linux Read
FTPS Linux Read/Write

Note

  • When using FTP, SFTP, or SFTP protocols, the connector may intermittently fail with timeout or login failed errors for aggregation and provisioning operations.
  • The connector can provision values to all attributes with a length less than or equal to 19 characters.

Required Permissions

The service account (acting as an admin account) created on the following servers must have read and write permissions on the files in the given directory path:

  • FTP
  • SFTP
  • HTTP
  • HTTPS

Documentation Feedback

Feedback is provided as an informational resource only and does not form part of SailPoint’s official product documentation. SailPoint does not warrant or make any guarantees about the feedback (including without limitation as to its accuracy, relevance, or reliability). All feedback is subject to the terms set forth at https://developer.sailpoint.com/discuss/tos.