Integrating SailPoint with JDBC

If you want to load user data, you can create a direct connect source for JDBC and configure a SQL query in it to load the data you want. To manage that data, either create an identity profile 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.

Supported Features

The SailPoint JDBC connector supports the following features:

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

For more information on features, refer to Source Features.

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.

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

• 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 on the database you will access through JDBC.
• An appropriate JDBC Driver for the database. For more information, refer to System Requirements.
• To load entitlements, you must add the entitlement schema in the JDBC source configuration for entitlement aggregation. For more information, refer to Configuring Multiple Group Objects.
• 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.

Provisioning Prerequisites

• Purchase the Provisioning feature.
• Contact Services to implement the feature for your environment.

Password Management Prerequisites

• Purchase the Password Management feature.
• Contact Services to implement the feature for your environment.

System Requirements

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

• IBM DB2 for Windows
• Oracle DB
• Sybase
• Microsoft SQL Server
• MySQL
• PostgreSQL
• AWS RDS

AWS RDS Support

To create a successful connection with an AWS RDS database:

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

Permissions

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

Generating Keys for Snowflake

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

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:

   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:

   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.

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

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';

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.