connect to Snowflake external system with OAuth 2.0 authentication method

Virginie
Virginie Member
in General

Hello,

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

Thank you

Best Answer

  • Henry Simms
    Henry Simms Administrator
    edited December 18 Answer ✓

    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}
    ]
    }

Answers

Categories