How to connect to Snowflake using SSO authentication from DBT

Let’s explore how to connect to Snowflake using SSO authentication from Dbt. This article refers to connecting to Snowflake through the Dbt command line, not DBT Cloud, with single-sign-on (SSO) authentication. Dbt Cloud requires a security integration to be configured in Snowflake and Dbt will handle new connections between Dbt Cloud and Snowflake on its own once this is set up.

An important note: Snowflake currently only supports SSO authentication with macOS and Windows. Linux is not yet supported.

Connecting to Snowflake from the command line

Snowflake has a number of ways to connect from the command line:

1. Enter a username and password each time you connect

• The –prompt flag prompts for a password. Passwords cannot be passed through connection parameters.
  snowsql -a <account_identifier> -u <user> –prompt

1. Configure a default connection

• No credentials need to be passed as arguments to snowsql to connect. This can be achieved through a local snowsql configuration file or environment variables.

1. Using a 3rd party Identity Provider for Single-Sign-On

• This method requires that a 3rd party Identity Provider has been set up in Snowflake. This method cannot be used with Snowflake credentials only.
  snowsql -a <account_identifier> -u <user> –authenticator externalbrowser

Connecting with DBT using SSO

Once Snowflake has been configured to use an SSO provider, connecting using DBT requires the same configuration in a DBT profile:

profiles.yml

cli_test:
  target: dev
  outputs:
    dev:
      type: snowflake
      account: <account_identifier>
      user: <user>
      role: public

      # SSO config
      authenticator: externalbrowser

      database: temp
      warehouse: compute_wh
      schema: dbt
      threads: 1
      client_session_keep_alive: False

Troubleshooting Errors

Here’s the general process that Snowflake follows for SSO:

1. DBT starts a new connection with Snowflake
2. Snowflake sees that this connection specifies that SSO should be used.
3. Snowflake opens a browser and sends the user to a 3rd party SSO provider
4. The SSO provider sends the user back to Snowflake
5. Snowflake uses the SSO configuration to log the user in

If a browser window fails to open when connecting with SSO

Step 1, above, can fail because you have an incorrect account or user name. Your account identifier might need to specify the cloud region. For example, if your Snowflake instance is in AWS us-east-1 , you’ll need to specify the account name plus the region, like abc1234.us-east-1 .

Step 2 can fail if there is no SSO provider configured in Snowflake.

Step 3 can fail because of an issue preventing your OS from opening a new browser window. Snowflake’s snowflake-connector-python needs to be installed if it is not already. This is required to use SSO.

If a browser window opens but DBT fails to connect

Steps 4 and 5 can fail if the SSO provider is not configured properly. You’ll most likely have to contact Snowflake support or a security administrator in your IT department to resolve the issue.

As you can see, the majority of the work is in the setup of the SSO provider and Snowflake. Once that has been done, connecting to Snowflake using SSO in DBT is fairly straightforward. Thanks for exploring how to connect to Snowflake using SSO authentication from Dbt.

Continue reading:

Check out: How to parse a variable in Snowflake as a source reference in dbt