Pass-through Authentication Support

CData Virtuality supports pass-through authentication for Snowflake via the Snowflake SSO.

The Snowflake SSO as implemented in our Snowflake translator works in the following way:

1. When creating a new Snowflake connection, the translator reads the current role from the Snowflake database (using the select current_role() command) and stores it as a default role. If the defaultRole translator property has been already provided, this step will be skipped.
2. When getting a Snowflake connection from the connection pool, this translator sets the current Snowflake role (using the use role command) by the value taken from Step 1.
3. When executing a query on the Snowflake data source, this translator reads roles from the current CData Virtuality user session, tries to get a corresponding Snowflake role from the roleMapping translator property, and sets it as the current Snowflake role (using the use role command). If the roleMapping translator property is not provided and the enableReAuth property is set to TRUE, the translator will throw an exception.

This way, you get the full metadata using the default role and restrict access to the Snowflake data according to your Snowflake roles provided in the roleMapping translator property.

Translator Properties Needed for Snowflake SSO

Enabling Snowflake SSO

To enable the functionality, follow these steps:

1. Enable the feature at the data source level with the translator property enableReAuth=true;

2. Define a role mapping of the CData Virtuality Server and Snowflake roles (also at the data source level) with the roleMapping translator property - here is a usage example: roleMapping="admin-role=DEMO_ROLE_ADMIN,dv-role2=DEMO_ROLE_2,dv-role3=DEMO_ROLE_3".

3. On the Snowflake side, ensure that the user provided for creating the data source in the CData Virtuality Server has access to all the needed metadata in Snowflake and can also assume all the Snowflake roles defined in the mapping above.

Example

These example statements show how to create a Snowflake data source with the enabled SSO mechanism:

CALL "SYSADMIN.createConnection"(
    "connectionOrResourceAdapterProperties" => 'host=<host>'/* Optional */,
    "encryptedProperties" => ''/* Optional */,
    "jbossCLITemplateName" => 'snowflake'/* Optional */,
    "name" => 'snowflake_2'/* Optional */
);;

CALL "SYSADMIN.createDataSource"(
    "encryptedModelProperties" => ''/* Optional */,
    "encryptedTranslatorProperties" => ''/* Optional */,
    "modelProperties" => 'importer.catalog=TEST_DB,importer.defaultSchema=TEST_PG,importer.schemaPattern=TEST_PG,importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW"',
    "name" => 'snowflake_2'/* Optional */,
    "translator" => 'snowflake'/* Optional */,
    "translatorProperties" => 'varcharReserveAdditionalSpacePercent=300,supportsNativeQueries=true,enableReAuth=true,roleMapping="admin-role=TESTROLE2, test-role=TESTROLE1"'/* Optional */
);;