Making JDBC Connections Work in SailPoint IIQ

Ever tried to pull data from an external system into SailPoint IIQ and felt like you were wiring an old phone line with a blindfold on? Yeah, JDBC connections can feel like that—until you get the hang of it.

This guide shows how to work with external and internal JDBC databases using BeanShell in SailPoint IIQ. It’s written for real humans—developers, architects, and IAM folks who want to get stuff done, not decode cryptic documentation.

---

🌐 Connecting to External Databases (JDBC-style)

Say you need to pull regional codes or department info from another system. Maybe it lives in MySQL, MSSQL, Oracle—you name it. You’re not doing a full aggregation. You just need to look up something.

Common use cases:

• Looking up user attributes during aggregation (e.g. translate a region code)
• Enriching identity attributes (e.g. adding cost center details)
• Feeding dropdowns in forms (e.g. list of available office locations)
• Saving form input to external systems (e.g. laptop requests)

✏️ The External Connection Example: Lookup Region by Code

import java.sql.*;
import org.apache.log4j.*;

Logger log = Logger.getLogger("sailpoint.rule.jdbc");
log.setLevel(Level.DEBUG);

String driver = "com.mysql.cj.jdbc.Driver";
String url = "jdbc:mysql://localhost:3306/orgdata";
String user = "iiq_user";
String pass = context.decrypt("enc_pass_here");

try {
  Class.forName(driver);
  Connection conn = DriverManager.getConnection(url, user, pass);
  log.debug("Connected to external DB");

  String regionCode = "MENA";
  PreparedStatement stmt = conn.prepareStatement("SELECT name FROM region_lookup WHERE code = ?");
  stmt.setString(1, regionCode);

  ResultSet rs = stmt.executeQuery();
  if (rs.next()) {
    String regionName = rs.getString("name");
    log.debug("Region: " + regionName);
  } else {
    log.warn("Region not found: " + regionCode);
  }
  rs.close();
  stmt.close();
  conn.close();

} catch (Exception e) {
  log.error("DB Error:", e);
}

return "Done";

---

🏛 Connecting to SailPoint’s Own Database

Need to read from or write to a custom table inside IIQ’s own DB? Good news: you don’t need to authenticate. SailPoint gives you a connected handle.

Use case:

You created a table coresec_region_map to store static reference data shared across the cluster.

import java.sql.*;
Logger log = Logger.getLogger("sailpoint.rule.internal");
log.setLevel(Level.DEBUG);

Connection conn = context.getJdbcConnection();
String lookupCode = "APAC";
String query = "SELECT name FROM coresec_region_map WHERE code = ?";

try {
  PreparedStatement stmt = conn.prepareStatement(query);
  stmt.setString(1, lookupCode);
  ResultSet rs = stmt.executeQuery();

  if (rs.next()) {
    String regionName = rs.getString("name");
    log.debug("Found region: " + regionName);
  } else {
    log.warn("No match for code: " + lookupCode);
  }
  rs.close();
  stmt.close();

} catch (Exception e) {
  log.error("Internal DB error", e);
}

return "Finished";

Important: Don’t call conn.close() here. SailPoint manages that connection lifecycle.

---

⚠️ Gotchas and Best Practices

• Always use PreparedStatements to avoid SQL injection and improve performance.
• Don’t log passwords.
• Close your ResultSet and PreparedStatement.
• For internal DB connections, never touch tables starting with spt_.
• If you write to the IIQ DB, avoid commit() unless you’re 100% sure.

📊 Real-World Example

One client had a user onboarding form that needed to fetch available floor locations per department. Those were managed in a legacy Oracle DB. With JDBC + a prepared lookup table, the dropdowns were always up-to-date — no CSVs, no redeploys.

💬 What About You?

Are you using JDBC in IdentityIQ to feed or fetch data outside the usual model? What patterns have saved you headaches?

Drop a comment or share your favorite JDBC trick below. Let’s learn from each other.