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 based on the source or verify that the accounts you load are correlated 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.

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

Important

You must add the entitlement schema in the JDBC SaaS source configuration for entitlement aggregation. For more information, refer to 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 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.
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.
For Snowflake key pair authentication, generate an encrypted public and private key pair. For more information, refer to 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.

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. macOS support OpenSSL by default.
Enter the generated private key on the Account Connection Settings for Snowflake page.

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

Open a command prompt or terminal window.
Run the following command to generate an encrypted private key:
```
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.
Run the following command to generate the public key from the private key file:
```
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
```
Copy the public and private key files to a local directory.
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 page.

Registering the OAuth Resource (Snowflake) in Entra ID

Go to Azure Portal > Entra ID > App registrations and select New registration.
Enter Snowflake OAuth Resource as the name.
Select Single tenant and select Register.
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>.
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
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

Go to App registrations > New registration.
Enter Snowflake OAuth Client as the name.
Select Single tenant and select Register.
From Overview, copy the Application (client) ID to use as your <Client_ID>.
Go to Certificates & secrets and select New client secret.
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.
Go to API permissions > Add a permission > APIs my organization uses and select the OAuth resource that you registered in Step 1.
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.
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.

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

Creating the External OAuth Security Integration in Snowflake

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

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.

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:

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:

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:

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.

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.