Support SSO authentication for Snowflake

[adrianisk]

SSO Authentication for Snowflake

Feature description

Snowflake support SSO through SAML 2.0 compliant identity providers, allowing companies to manage access to Snowflake without provisioning accounts with passwords for each employee. The feature request is to enable the external browser authentication provided by the official Snowflake python connector when using dbt.

https://docs.snowflake.net/manuals/user-guide/python-connector-example.html#connecting-using-federated-authentication

Based on the options of the above Snowflake connector, the profile might look something like:

[profile-name]:
  outputs:
    external_browser_example:
      type: snowflake
      threads: [1 - 8]
      account: [url prefix for your snowflake connection]

      user: [user]
      authenticator: 'externalbrowser'
      role: [optional, the snowflake role you want to use]

      database: [db name]
      warehouse: [warehouse]
      schema: [schema name]
    native_okta_example:
      type: snowflake
      threads: [1 - 8]
      account: [url prefix for your snowflake connection]

      user: [okta_user]
      passwprd: [okta_password]
      authenticator: 'https://<okta_accout_name>.okta.com/'
      role: [optional, the snowflake role you want to use]

      database: [db name]
      warehouse: [warehouse]
      schema: [schema name]
  target: [target-name]

Who will this benefit?

Any company using SSO to manage employee access to resources, specifically Okta. We'd really like to avoid managing a separate set of credentials for Snowflake as our company grows. Having tried out the external browser authentication in a few python scripts, it's pretty slick.

[drewbanin]

Thanks for the feature request @adriank-convoy. Some pointers if you want to try to tackle this issue:

All snowflake connections are created here. I think this is where you'll want to add the authenticator argument.

From reading the snowflake-connector-python docs, it sounds like there are three options:

1. snowflake user/pass (current implementation)
2. authenticator='externalbrowser'
3. authenticator='<some okta url>'

dbt has contracts for profile specifications, and you can find the Snowflake one here. I think you'll want to add authenticator as an optional string field. I'm not certain how the externalbrowser authenticator works in practice, but you might also need to remove password from the list of required fields at the bottom of that contract. That's probably not ideal, but we do something similar for Redshift IAM Auth... so maybe it's ok.

I like the example profile you provided above! Happy to discuss in this thread (or a PR) if you have any questions about the implementation. Drop me a line on Slack if you have any issues getting set up with a dev environment. GLHF!

[adrianisk]

Awesome, thanks for pointing me in the right direction. I'll try and take a crack at it sometime this week.

[cmcarthur]

Merged to the next dbt release (Stephen Girard). Thanks for the contribution @adriank-convoy !

[mike-seekwell]

@adriank-convoy have you figured out a way to get this working without a user present? e.g. with many SSO flows, you'd get a "refresh token" or some other key after the user authenticates for the first time that would allow to connect when the user is offline.

Or are you just running this from the command line and authenticating in the browser every time?

[adrianisk]

Hey @mike-seekwell, based on a conversation with one of the Snowflake devs (snowflakedb/snowflake-connector-python#140) we requested that they enable the id token cache feature for out account. Once that's enabled, users only need to authenticate through the browser once every 4 hours, and the rest of the requests will use the cached token to authenticate.

For our Airflow jobs we've just created 'service' accounts in Snowflake that just have a username & password, and don't authenticate through Okta. These accounts have IP ACLs (via a Snowflake network policy) to make sure they can only authenticate from our machines.

[mike-seekwell]

Got it, thanks @adriank-convoy !

[dmateusp]

I went through the process of enabling token caching this week and I would like to add some info (snowflakedb/snowflake-connector-python#140 (comment)):

We are using DBT CLI, and asked Snowflake support to enable the feature for us, I just wanted to add some info here:

• Support told us that the feature is public now so running alter account set allow_id_token = true; should enable token caching (without having to contact them in the first place)

• Note that in the current version of DBT (0.18.0b1), the snowflake connector version is 2.2.1. This version of the connector was not using token caching and we had to add snowflake-connector-python>=2.2.8 to our requirements.txt file to get the feature working

Can someone confirm that with the current version requirements of DBT, token caching is not used?

[bessey]

Hmm, @dmateusp I just excitedly tried this exact setup and no luck, i'm getting multiple popups, seemingly 1 per model when i do dbt run. Is there another step necessary that is not mentioned in this comment? I have done the alter account set allow_id_token = true; too

requirements.txt for reference

dbt>=0.18.0b1
snowflake-connector-python>=2.2.8

[dmateusp]

oh :( any chance you are using Linux ? https://docs.snowflake.com/en/user-guide/odbc-parameters.html#using-browser-based-sso-with-connection-caching-macos-and-windows-only

[bessey]

Ah you're absolutely right, I didn't realise only Mac / Windows are supported (I wonder why?)

[drewbanin]

huh! @bessey or @dmateusp - if either of you feels inclined to update the docs at github.com/fishtown-analytics/docs.getdbt.com, we'd love to see a PR for this!

[bessey]

Yeah no problem, on it now

[Tomperez98]

What would the password be? I have okta codes, but not passwords