Support Snowflake oauth authentication

[drewbanin]

Describe the feature

Support oauth authentication for Snowflake targets.

dbt's Snowflake plugin already supports the specification of an authenticator config. If the authenticator is configured as oauth, then dbt should also pass along a target config named token to the Snowflake connection constructor.

Note that a password is not required when using the oauth authenticator.

Example config:

my-snowflake-db:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: wxyz526
      # other standard snowflake configs here...
      
      # Specify oauth method + token
      authenticator: oauth
      token: abc123

Who will this benefit?

Snowflake users that connect to the database using SAML providers.

[benjaminsingleton]

Do you know if this will allow for authentication using Azure Active Directory? I use SSO with Azure AD, which I believe will require federated authentication / browser-based SSO.

[drewbanin]

Hey @benjaminsingleton - dbt Core does not currently know how to get access tokens from Snowflake via an SSO provider. Our current thinking is that the deployment code which wraps dbt Core (eg. dbt Cloud, or similar) can implement the OAuth flow for Snowflake and pass the resulting access token into the profile before invoking dbt.

[A132770]

@benjaminsingleton I'm sure you figured this out by now... but for posterity: this will allow authentication (well, authorization) via Azure AD. Once you configure Azure AD app registrations for an appropriate OAuth flow, you can use a client library (or craft your own requests) to get an OAuth token. Pass that token into your dbt configuration and you should be off to the races. As Drew said... you'll need to do this from your deployment code that is running dbt.