DUO MFA authentication doesn't work with Snowflake version 8.24+

[nikzavada]

Is this a new bug in dbt-snowflake?

• I believe this is a new bug in dbt-snowflake
• I have searched the existing issues, and I could not find an existing issue for this bug

Current Behavior

In the current version of dbt-snowflake the next additional parameter has been used to support caching of the token
result["client_request_mfa_token"] = True

Expected Behavior

I believe based on this documentation https://docs.snowflake.com/en/user-guide/security-mfa#label-mfa-token-caching
the new parameter
result["authenticator"] = "username_password_mfa"
is required to support the expected caching behavior.

Steps To Reproduce

Every dbt build command requires a DUO approval with Snowflake 8.26

Relevant log output

No response

Environment

- OS: Macos
- Python:
- dbt-core: 1.7.11
- dbt-snowflake: 1.7.3

Additional Context

No response

[amychen1776]

Thank you for opening this - we will look into it and discuss it with Snowflake to confirm the change here.

[amychen1776]

@nikzavada I'm curious when you might have seen this behavior change? Did you have MFA enabled already and working with dbt? It appears this has been implemented a while ago at Snowflake so trying to better understand what the change might have been.

[nikzavada]

@amychen1776 we just enabled the MFA and I was trying to set it up in dbt to not to approve every run but it didn't work and I spent some time looking around and eventually I came here with my assumption about it :)

[amychen1776]

@nikzavada That's helpful to note! I wonder if it could be related to this issue where it's not caching the connection. Would you be able to provide a cleaned up version of your profiles.yml (with no sensitive information)?

[nikzavada]

@amychen1776 not sure if that can be related. This is a shortened profiles.yml that currently in use

config:
  send_anonymous_usage_stats: False

dwh:
  outputs:
    dev_xs:
      type: snowflake
      account: xxxxxxxx.us-east-1
      user: "{{ env_var('PERSONAL_SNOWFLAKE_USERNAME') }}"
      password: "{{ env_var('PERSONAL_SNOWFLAKE_PASSWORD') }}"
      authenticator: username_password_mfa
      database: DWH_DEV
      schema: "DBT_{{ env_var('PERSONAL_SNOWFLAKE_USERNAME') }}"
      role: ANALYTICS_ENGINEER
      warehouse: DBT_DEV_WH_XS
      threads: 4
    prod_m:
      type: snowflake
      account: xxxxxxxx.us-east-1
      user: DBT
      password: "{{ env_var('DBT_SNOWFLAKE_PASSWORD') }}"
      database: DWH
      schema: CORE
      role: DBT_AUTOMATION
      warehouse: DBT_WH_M
      threads: 16
  target: dev_xs

[amychen1776]

@nikzavada could you try to set the reuse_connections to True in your profile? Just to also be clear, every new dbt command you run is now requiring an Duo approval? Would you be able to share the logs? In speaking to Snowflake about this, they also asked if you could open up a support ticket so they can help debug.

I have turned on MFA for my account but can't seem to reproduce this error.

[amychen1776]

@nikzavada I think we figured it out. Have you ran alter account set allow_client_mfa_caching = true for your warehouse?

related doc:
https://docs.snowflake.com/en/user-guide/security-mfa#using-mfa-token-caching-to-minimize-the-number-of-prompts-during-authentication-optional

[nikzavada]

@amychen1776 I tried to use reuse_connection set to True and I ran alter account set allow_client_mfa_caching = true;.
This SQL command was one of the first adjustments I did.

And I still need to approve every dbt command I run.

May I ask you what is your Snowflake version?
You can get it by executing the next command SELECT CURRENT_VERSION()
My version is 8.27.1

[amychen1776]

@nikzavada I'm on 8.27.1. I think we might have exhausted working via github. Would you be open to reaching out to me via email ([email protected]) or dbt slack?
As a side note, we have similar folks report this and they fixed it with this [approach](https://docs.getdbt.com/docs/cloud/connect-data-platform/connect-snowflake#snowflake-mfa but it doesn't appear you're on Cloud.

[amychen1776]

I'm going to close this issue for now since we have not been able to recreate it and we do believe this might be a configuration error. Please reach out to me to help debug.

[nikzavada]

@amychen1776 I just discovered that it doesn't work for me only when I use dbt installed with Docker (docker pull ghcr.io/dbt-labs/dbt-snowflake:1.7.1). I just installed dbt with pip and everything works as expected

[amychen1776]

@nikzavada that's so helpful to know! Thank you