JDBC Connection, Query, and Schema Design
Key Takeaways
Use a supported driver, database connection string and credential for the selected JDBC variant.
The Account SQL Query defines the account result set. Discover and check its returned attributes before loading the source.
A single-account query narrows the same data contract to one native account identifier.
Delta needs the documented change table and associated setup. Row merging requires explicit index columns and compatible query order.
Choose the connector and service account
Java Database Connectivity (JDBC) allows SailPoint to collect information from database queries. This section uses SailPoint’s VA-based JDBC connector. Start with a supported driver, a VA cluster that can reach the database and a database service account. Upload the appropriate trusted driver file and configure the database locator and driver class as directed for the selected database. The JDBC SaaS variant has separate instructions, including driver upload through a connectivity customizer. Use the instructions for the selected connector because driver and deployment setup differ.
The service account runs the configured operations in the database. For a read-only inventory it needs access to the relevant query objects. If the use case later includes supported provisioning, confirm the feature is available and configured, and verify appropriate permissions and query or rule requirements for the selected variant. A read query that returns an account proves neither execution rights on a stored procedure nor the ability to modify an entitlement.
Design queries around the expected record
| Requirement | Configure or verify |
|---|---|
| Confirm the service account opens a connection | Review the database URL, driver, server path and authentication, and run a supported test query if needed |
| Load all in-scope accounts | Set the Account SQL Query and check the expected projection, filters and cardinality |
| Refresh a single account | Use a Single Account SQL Query limited to that native identifier when the operation is configured |
| Add an entitlement catalog | Configure the appropriate entitlement schema and group objects |
| Load a worker with many membership rows | Configure the documented merging and index settings, and sort by the index columns |
| Read changes incrementally | Configure the documented delta change table and delta SQL if required |
For example, a service account can connect to a database, but its source needs to return worker_id, last_name, email and department_code. Check the account SQL and the account schema, not just the host and credential. In the documented VA JDBC procedure, schema discovery runs the account query and displays returned attributes. Select the appropriate Account Name and Account ID, confirm a stable identifier before aggregating, and use matching configuration appropriate to the records. When an identity profile uses this data, confirm the profile separately provides its required identity values.
Work through repeated rows
An approved view returns these rows for one account with two permissions.
| worker_id | membership |
|---|---|
| W201 | INVENTORY_READ |
| W201 | COST_REPORT |
The desired result is one account W201 with two membership values. Configure the connector’s row merging using worker_id as an index and membership as the column to merge, with the account query ordered by its index columns. Inspect the source schema so the gathered membership values are represented as intended. Test another account whose membership differs to confirm the grouping boundary. A query sorted only by a membership label could separate rows for the same account in the result set and would not follow the documented merge ordering.
SailPoint recommends testing the query outside the connector before using it. Run the equivalent query in an authorized testing environment with the effective service account permissions. If a JDBC source produces a generic query error, check the exact returned error and SQL format. SailPoint’s VA JDBC setup warns that a trailing semicolon can produce a general error. A driver that can open a connection still needs a valid query and permissions to the referenced tables or views.
For the single-account test, query the same native ID as the full load and compare selected column labels and values. The two operations should tell the same story about that account. A single-account query referencing a different identifier can look healthy at the database layer while returning a person’s neighboring record.
Decide whether delta is viable
A checkbox alone does not capture changed rows. SailPoint documents a delta table prerequisite for this connector. Identify the table that captures changes, how it will be populated and what values are needed when the main query uses aliases. Verify the incremental result with representative changes before relying on it. If the source cannot provide required change tracking, plan a supported ordinary aggregation with an acceptable schedule.
See SailPoint’s VA JDBC prerequisites, VA connection setup, VA query settings, merge and discovery, and SaaS driver setup.
The account query produces multiple permission rows per worker. The engineer enables documented JDBC merging by worker_id. Which query change is necessary?
Group the result by entitlement name and discard the worker identifier
Replace the account query with the connectivity test query
Order the account query by the configured index column worker_id
Disable the account query and rely on schema discovery to supply records
A VA-based JDBC source needs delta aggregation. Which prerequisite does the engineer verify before relying on the setting?
A database table that captures the relevant delta changes is configured and tested
The identity profile selects the source with the largest priority number
Only the test connection query returns results
Each account has a separate VA cluster
Sections you finish are checked off in the contents.