dbt snowflake connections through proxy no longer work

[mosqueteiro]

Describe the bug

A clear and concise description of what the bug is. What command did you run? What happened?

dbt debug, run, test, and any other commands that initiate a connection with the snowflake database are no longer able to utilize a proxy to make the connection.

Steps To Reproduce

In as much detail as possible, please provide steps to reproduce the issue. Sample data that triggers the issue, example model code, etc is all very helpful here.

Need: Snowflake server that only accepts whitelisted IPs and proxy server whose IP is whitelisted for Snowflake
server; environment variables set for HTTP_PROXY, HTTPS_PROXY; dbt profile for snowflake connection to database; snowflake-connector-python>=2.4.6

run

dbt debug

Expected behavior

A clear and concise description of what you expected to happen.

dbt debug runs detects environment variables for proxy and sets up snowflake connection accordingly, outputs

...
Connection test: OK connection ok
...

Screenshots and log output

If applicable, add screenshots or log output to help explain your problem.

dbt debug output:

Running with dbt=0.20.2
dbt version: 0.20.2
python version: 3.7.11
python path: /opt/conda/envs/dbt/bin/python
os info: Linux-5.10.47-linuxkit-x86_64-with-debian-bullseye-sid
Using profiles.yml file at /path/to/profiles.yml
Using dbt_project.yml file at /path/to/dbt_project.yml

Configuration:
  profiles.yml file [OK found and valid]
  dbt_project.yml file [OK found and valid]

Required dependencies:
 - git [OK found]

Connection:
  account: *****.*******.***
  user: username
  database: DATABASENAME
  schema: schemaname
  warehouse: warehousename
  role: rolename
  client_session_keep_alive: False
  Connection test: ERROR

dbt was unable to connect to the specified database.
The database returned the following error:

  >Database Error
  250003: Failed to get the response. Hanging? method: post, url: https://*****.*******.***.snowflakecomputing.com:443/session/v1/login-request?request_id=...

Check your database credentials and try again. For more information, visit:
https://docs.getdbt.com/docs/configure-your-profile

System information

Which database are you using dbt with?

• postgres
• redshift
• bigquery
• snowflake
• other (specify: ____________)

The output of dbt --version:

installed version: 0.20.2
   latest version: 0.20.2

Up to date!

Plugins:
  - bigquery: 0.20.2
  - redshift: 0.20.2
  - snowflake: 0.20.2
  - postgres: 0.20.2

The operating system you're using:

Ubuntu 20.04
Linux-5.10.47-linuxkit-x86_64-with-debian-bullseye-sid

The output of python --version:
Python 3.7.11

Additional context

Add any other context about the problem here.

There is a bug with snowflake-connector-python>=2.4.6 where it is no longer able to use the environment variables HTTP_PROXY and HTTPS_PROXY meaning connections that require a proxy with dbt no longer work. The SnowflakeConnection class has parameters to pass proxy settings into it. Utilizing these parameters dbt profiles of the snowflake type could include parameters to define the proxy and side-step this issue.

[mosqueteiro]

dbt-labs/dbt-core#3695 had similar error

[jtcohen6]

Thanks for opening the issue @mosqueteiro, and for getting to the bottom of the change to proxy support in dbt v0.19 → v0.20—really, it sounds like this is a bug in snowflake-connector-python v2.4.5 → v2.4.6.

Since dbt v0.20 supports any version of snowflake-connector-python >=2.4.1,<2.5.0:

https://github.com/dbt-labs/dbt/blob/733f35f5e133327e8e54477e94166ec76eec926e/plugins/snowflake/setup.py#L50

Have you been able to install and pin an older version of snowflake-connector-python (e.g. v2.4.5) and confirm that the proxy is still working?

Thanks also for opening the issue in the Snowflake connector: snowflakedb/snowflake-connector-python#865. I think we need clarity there before we should proceed with a solution on dbt-snowflake's side.

Based on what they've documented, it sounds like using the env vars is the recommended approach, rather than passing proxy_host and proxy_port into the connection. If indeed that becomes necessary to get this working, I have no opposition to adding those as optional parameters to the Snowflake connection profile.

[mosqueteiro]

I have been able to pin the snowflake-connector-python<=2.4.5 and have also pinned dbt<=0.19.0 although it sounds like the latter may not be necessary. This is working fine at the moment. I have also gotten the newer version of snowflake-connector-python to work outside of dbt if I use the proxy_host and proxy_port parameters —even though they are listed as deprecated. Since dbt has no way to access these with its use of the connector I have to keep the version pinned in my dbt environment. I am advocating for the snowflake-connector-python to keep these parameters since there are cases where you may only want to use a proxy for specific connections and not route all computer traffic through that proxy and I would advocate that dbt support those parameters too —assuming Snowflake will choose to keep them instead of removing them.

[laxjesse]

@jtcohen6 we've got similar issues with connecting using a proxy. we actually found that (for our case) it was best for us to directly use the host and port parameters as well as the undocumented protocol. e.g

con = snowflake.connector.connect(
    host=<host>,
    port=<port>,
    protocol='http',  
...
)

I have no opposition to adding those as optional parameters to the Snowflake connection profile.

would you be opposed to a PR that adds this collection of parameters (host, proxy_host, port, proxy_port, method) as optional snowflake profile parameters? I think it's uncommon for these to be necessary but not unheard of for companies in industries with older security regulations

(Forgot to add this initially)
It's also sometimes undesirable to use the environment parameters as HTTP_PROXY is a bit generic and can impact other processes. This isn't unsolvable but it does add a layer of difficulty for CLI users that are less comfortable with operating on the command line

[jtcohen6]

@laxjesse I wouldn't be opposed. We have a "catch-all" method for just this purpose, to ferry auth-related arguments over to the Snowflake python connector:

dbt-snowflake/dbt/adapters/snowflake/connections.py

Lines 71 to 73 in c7c865b

	def auth_args(self):
	# Pull all of the optional authentication args for the connector,
	# let connector handle the actual arg validation

Question: Do you provide host, port, proxy_host, proxy_port instead of standard arguments, such as account and user? If so, you'll still need to pass placeholder values for those, since they're required, or add conditional logic making them optional.

[laxjesse]

Question: Do you provide host, port, proxy_host, proxy_port instead of standard arguments, such as account and user? If so, you'll still need to pass placeholder values for those, since they're required, or add conditional logic making them optional.

makes sense! we do provide the standard args as well.