connect to Snowflake external system with OAuth 2.0 authentication method

Options
Virginie
Virginie Member
in General

Hello,

How can we connect to Snowflake external system with OAuth 2.0 authentication method?

Thank you

Best Answer

Answers

  • Josh Boxer
    Josh Boxer Administrator

    Hi Virginie

    The UI for Snowflake connections is set up for credentials, not oauth in UI. You could submit here and see if others would find this useful: https://community.experianaperture.io/categories/ideas-board Note that once it is added to Aperture you would need to upgrade to take advantage of the new functionality.

    It should be possible to manually build out an advanced Connection string (might take some trial and error):

    https://docs.progress.com/bundle/datadirect-snowflake-jdbc-60/page/OAuth-2.0-access-token-flow.html

    https://docs.snowflake.com/en/developer-guide/jdbc/jdbc-configure#examples

    Regards

  • Henry Simms
    Henry Simms Administrator
    edited December 2024

    For those looking to set up a JDBC Snowflake connection with OAuth using the native Progress driver, here's the approach I would take today (in Data Studio v2.15.3, where OAuth is not yet supported in the out-of-the-box External System config for Data Studio):

    1. The basic approach will be like that described for Salesforce JDBC OAuth in this article . Essentially, a modified datadirectJdbc.json is needed which allows the relevant OAuth params to be added to the External system config and Credentials.
    2. The first task will be to follow the Progress driver documented steps to configure the app in Snowflake and obtaining all the relevant parameters to use in the auth, which can be done outside of Data Studio. The configuration can then be tested and a Connection String generated  using the Progress driver's "Snowflake Configuration Manager", accessed by double-clicking on the DSsnowflake.jar
    image-57f1cfbf126d1-39f8.png

    3. Once you have the connection parameters and have validated these, you'll need to start up Data Studio with a modified datadirectJdbc.json to provide the relevant UI fields to populate in the External System and Credential.

    The new json config is going to be something like the below, although please note that this has not been tested an may require modification:

    {
    "name":"SNOWFLAKE",
    "displayName":"Snowflake (OAuth)",
    "jarFilePattern":"DSsnowflake.jar",
    ….
    "fetchSize":1000,
    "unsupportedSQL":"",
    "connectionString":"jdbc:experian:snowflake:accountname=%s;databasename=%s;partnerapplicationname=%s;schema=%s;warehouse=%s;AuthenticationMethod=oauth2.0",
    "connectionParams":[
    {"param":"ACCOUNT_NAME", "type":"String", "mandatory":false},
    {"param":"DATABASE_NAME", "type":"String", "mandatory":false},
    {"param":"PARTNER_APPLICATION_NAME", "type":"String", "mandatory":false},
    {"param":"SCHEMA", "type":"String", "mandatory":false},
    {"param":"WAREHOUSE", "type":"String", "mandatory":false},
    {"param":"AUTHENTICATION_URI", "type":"String"},
    {"param":"TOKEN_URI", "type":"String"},
    {"param":"SCOPE", "type":"String"}
    ],
    "accessParams":[
    {"param":"CLIENT_ID", "type":"String"},
    {"param":"CLIENT_SECRET", "type":"String"},
    {"param":"ACCESS_TOKEN", "type":"String"},
    {"param":"REFRESH_TOKEN", "type":"String"}
    ],
    "tableParams":[
    {"param":"TABLE_PATTERN", "type":"String", "mandatory":false, "default":"%"},
    {"param":"NAME_REGEXP", "type":"String", "mandatory":false},
    {"param":"NAME_CASE_SENSITIVE", "type":"Boolean", "mandatory":false, "default":false},
    {"param":"INCLUDE_TABLES", "type":"Boolean", "mandatory":false, "default":true},
    {"param":"INCLUDE_VIEWS", "type":"Boolean", "mandatory":false, "default":false}
    ]
    }

Categories