Connect Superset to Snowflake with keypair result in invalid arguments

[kenonit]

How to reproduce the bug

Have a snowflake account with MFA setup and required.
In order to add snowflake support, In the values file (my-values.yaml) modify bootstrap script to the following:

bootstrapScript: |
  #!/bin/bash
  rm -rf /var/lib/apt/lists/* && \
  pip install \
    psycopg2-binary==2.9.1 \
    redis==3.5.3 \
    snowflake-connector-python==2.7.9 \
    snowflake-sqlalchemy==1.2.4 && \
  if [ ! -f ~/bootstrap ]; then echo "Running Superset with uid {{ .Values.runAsUser }}" > ~/bootstrap; fi

Finally, Install superset with Helm
helm upgrade --install --values my-values.yaml --namespace superset superset

Before continuing to the steps, we need to create a public key using openssl, should look like
openssl rsa -in ${PRIVATE_KEY} -out ${PUBLIC_KEY} -pubout -passin file:${PASSPHRASE_FILE}
and make sure you set it up in Snowflake as the public key and passphrase of your account.

Then, we need to try and create the connection

1. Go to 'Data'
2. Click on 'Databases'
3. Click on '+Database' to create a new database
4. Under 'Supported Databases' Scroll down to 'Snowflake.'
5. On the bottom, click on 'Connect this database with a SQLAlchemy URI string instead'
6. Fill the SQLALCHEMY URI field.
7. Click on the 'Advanced' tab
8. Expand 'Security' accordion tab
9. Under secure extra, try to paste the keypair and passphrase you have in this format:

{
     "auth_method": "keypair",
     "auth_params": {
         "privatekey_body": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n...\n-----END ENCRYPTED PRIVATE KEY-----",
         "privatekey_pass":"Your Private Key Password"
     }
 }

10. Go to 'Basic' tab again
11. Click 'Test Connection'
12. See error on the bottom-left side of the screen

If you click 'Connect' rather than 'Test Connection', it just shows some error occurred but not writing what it is..

Expected results

First, the expected thing is that the json syntax would be supported, as stated in the superset-snowflake official documentation, which can be found here
It is also expected that Superset will be able to connect to snowflake and won't require approval of MFA (or by push) when trying to connect to it throughout the usage.

Actual results

After clicking 'Connect' i receive:
ERROR: Invalid argument(s) 'auth_method','auth_params' sent to create_engine(), using configuration SnowflakeDialect/NullPool/Engine. Please check that the keyword arguments are appropriate for this combination of components.

if I wrap auth_method and auth_params with an object named connect_args, which finally looks like that:

{
   "connect_args": {
     "auth_method": "keypair",
     "auth_params": {
         "privatekey_body": "-----BEGIN ENCRYPTED PRIVATE KEY-----\n...\n...\n-----END ENCRYPTED PRIVATE KEY-----",
         "privatekey_pass":"Your Private Key Password"
     }
   }
 }

Then the syntax is accepted, no error occurs, but every query issued from superset requires me to approve a new connection with my MFA app (Duo Mobile)

Screenshots

Environment

(please complete the following information):

• browser type and version: irrelevant ([Version 1.37.116 Chromium: 100.0.4896.127 (Official Build) (arm64)])
• superset version: superset-0.7.4

Make sure you add snowflake to the bootstrap script

bootstrapScript: |
  #!/bin/bash
  rm -rf /var/lib/apt/lists/* && \
  pip install \
    psycopg2-binary==2.9.1 \
    redis==3.5.3 \
    sqlalchemy-redshift==0.8.11 \
    snowflake-connector-python==2.7.9 \
    snowflake-sqlalchemy==1.2.4 && \
  if [ ! -f ~/bootstrap ]; then echo "Running Superset with uid {{ .Values.runAsUser }}" > ~/bootstrap; fi

Make sure the template processing feature flag is on

configOverrides:
  my_override: |
    FEATURE_FLAGS = { "ENABLE_TEMPLATE_PROCESSING": True }

Checklist

Make sure to follow these steps before submitting your issue - thank you!

• I have checked the superset logs for python stacktraces and included it here as text if there are any.
• I have reproduced the issue with at least the latest released version of superset.
• I have checked the issue tracker for the same issue and I haven't found one similar.

Additional context

Simply following the official instructions and it won't work

The issue, IMO, is that superset limits the arguments being sent to the db engine.

[blaccan]

Same here

[rusackas]

Tempted to close this as stale, but it seems to mirror #22143

Is anyone experiencing this in current Superset (3.x)?