Intermittent problem with DB connection while using Snowflake and SSO

[ddppi]

Hi,

I am using Window 10 + Anaconda (Python 3.7.6) + DBT (v0.18.0) to access Snowflake.

When I do DBT run, I don't have any problem while using username/password in profiles.yml. However, when I switch to SSO, I always have intermittent problem and the run fails half way, and the error message is

ERROR: Database Error
  250007 (08001): Failed to connect to DB: <account>.snowflakecomputing.com:443, Invalid HTTP request from web browser. Idp authentication could have failed.

In the web browser, I can see the page opens up site
https://.snowflakecomputing.com/fed/login
saying
Redirecting to Snowflake client in 1 seconds...
, then the site is redirected to
http://localhost:/?token=
but I gets the error message on the webpage saying
This site can't be reached

My colleagues seem to get the same problem. Any suggestions, or what information could be helpful to trace the problem?

thanks,

[jtcohen6]

Thanks for opening @ddppi. We've been seeing some issues lately with Snowflake + browser auth.

Have you turned on the allow_id_token parameter in your Snowflake account (docs)?

[ddppi]

Hi Jeremy, Our tech team have told me the token cache has been turned on for our account. I am using snowflake connector python version 2.3.1, and I still see numerous popup authentication tabs opened in my Chrome. Is there a way to verify? From: Jeremy Cohen [mailto:notifications@github.com] Sent: 08 September 2020 22:32 To: fishtown-analytics/dbt <dbt@noreply.github.com> Cc: ddppi <ji.peng@outlook.com>; Mention <mention@noreply.github.com> Subject: Re: [fishtown-analytics/dbt] Intermittent problem with DB connection while using Snowflake and SSO (#2742) Thanks for opening @ddppi<https://github.com/ddppi>. We've been seeing some issues lately with Snowflake + browser auth. Have you turned on the allow_id_token parameter in your Snowflake account (docs<https://docs.snowflake.com/en/sql-reference/parameters.html#allow-id-token>)? — You are receiving this because you were mentioned. Reply to this email directly, view it on GitHub<#2742 (comment)>, or unsubscribe<https://github.com/notifications/unsubscribe-auth/AM6SDLRO6EUPLGQRGHKCTRTSEYP4PANCNFSM4Q7T34SA>.

[jtcohen6]

Hmm. I don't know that this would be the culprit, but it's worth saying that we've pinned snowflake-connector-python==2.2.10 here, and we've found we need to be quite careful with that dependency.

[ddppi]

ok, will create another virtual environment in conda and try that.

In my research, I also get to know that for token caching to work, I need to have the package "secure-local-storage" installed when I install snowflake-connector-python. However, I can't run pip install "snowflake-connector-python[secure-local-storage]" because my PC always failed due to "SSL: CERTIFICATE_VERIFY_FAILED". What I did is to clone the repo to my local disk, and install manually there by running python setup.py for both DBT and python connector. Any change you know how to install the optional package "secure-local-storage" when I install snowflake-connector-python manually? I think it is part of the v2.3.1 installation? https://github.com/snowflakedb/snowflake-connector-python/blob/f4fe2277b82d98f2122478d5df712c59420ea0bd/setup.py#L236

I also reduce the threads to 1 in profiles.yml, as I think it would only run 1 model at a time? However, it doesn't help either.

[jtcohen6]

Not sure I can be helpful around how to install that optional package manually on your machine. It looks like it's the same in v2.2.10, though.

[ddppi]

Close the issue. After I got the optional package installed, the problem seemed gone.
pip install "snowflake-connector-python[secure-local-storage, pandas]"

[acatarinavr]

I had the same issue. Did the same:
pip install "snowflake-connector-python[secure-local-storage, pandas]"

and it worked.