How to aggreagte data access roles details from a view in Snowflake

IdentityIQ (IIQ) IIQ Discussion and Questions
, ,
1

Which IIQ version are you inquiring about?

8.4p2

Please share any other relevant files that may be required (for example, logs).

[Please insert files here, otherwise delete this section]

Share all details about your problem, including any error messages you may have received.

I am having a requirement to manage all the data access roles present in Snowflake accounts.
There are multiple snowflake accounts present in the organization and all the data access roles present across multiple snowflake accounts are consolidated into a view in one of the snowflake accounts. Now, sailpoint needs to connect to the view and get all the data access roles and create as entitlements.

We couldn’t achieve this using the OOTB Snowflake as the connector connects to specific tables for getting the user and role details.

Is there any way or connector in Sailpoint, which we can use to fetch the details from a specific view of Snowflake?

Thanks you.

Regards,
Sam

2

Hi @soswain_resmed

Yes, you can go ahead with the JDBC connector approach.

Using the JDBC connector in SailPoint IIQ allows you to connect directly to a specific Snowflake view, which is ideal for your use case. You will need to configure the connector to use the Snowflake JDBC driver (version 3.13 or later is recommended) and set up key-pair authentication if required.

Once connected, you can define a custom aggregation query to pull entitlement data from the view and map it accordingly in your application definition.

3

Thank you @haideralishaik for your quick response. I was thinking of using the JDBC connector, however was not clear on few things. Would you able provide your input to below queries of mine?

  1. For using snowflake JDBC driver, do we need add any additional jar to the sailpoint library?
  2. I have not explored the option to use key-pair for authentication. Do you have any pointers to do so?

Thanks,
Sam

4

hi @soswain_resmed

1. Do we need to add any additional JAR to the SailPoint library?

Yes. To use the JDBC connector with Snowflake, you need to manually add the Snowflake JDBC driver JAR to the SailPoint IIQ classpath.

  • Recommended version: snowflake-jdbc-3.13.0.jar or later (for key-pair authentication support)
  • Place the JAR in:
[IIQ_HOME]/WEB-INF/lib/
  • Restart the application server after placing the JAR.

2. How to configure key-pair authentication for Snowflake JDBC in IIQ?

Key-pair authentication is supported from Snowflake JDBC Driver 3.13+. Here’s a sample configuration:

JDBC URL Format:

jdbc: snowflake://.snowflakecomputing.com/?user=&private_key_file=<path_to_private_key>&role=&warehouse=&db=&schema=

Example:

jdbc: snowflake://abc12345.eu-central-1.snowflakecomputing.com/?user=sam_user&private_key_file=/opt/keys/snowflake_key.p8&role=SYSADMIN&warehouse=COMPUTE_WH&db=ACCESS_DB&schema=PUBLIC

Driver Class:

net.snowflake.client.jdbc.SnowflakeDriver

Snowflake JDBC in IIQ (with key-pair auth):
Snowflake JDBC connection in IIQ - SailPoint Developer Community
Includes example JDBC URL and troubleshooting tips.

Snowflake client connectivity and troubleshooting | Snowflake Documentation

5

Thank you @haideralishaik.
It worked.