Support Browser-based SSO in Snowflake connector

[franloza]

It would be great to support Browser-based SSO to connect to Snowflake.

When federated authentication is enabled for an account, Snowflake does not recommend maintaining user passwords in Snowflake so using SSO is the only way to connect and authenticate with a Snowflake client.

Currently, if I try to connect with an external authenticator, I get the following error:

data-diff \
    "snowflake://<user>@<account>/<database>/<schema>?warehouse=<warehouse>&role=<role>&authenticator='externalbrowser'" \
    <table> \
    "snowflake://<user>@<account>/<database>/<schema>?warehouse=<warehouse>&role=<role>&authenticator='externalbrowser'" \
    <table>

snowflake.connector.errors.ProgrammingError: 251006: Password is empty

In other tools like dbt, this feature is supported by providing the authenticator option: https://docs.getdbt.com/reference/warehouse-profiles/snowflake-profile#sso-authentication

[sirupsen]

@franloza this is definitely legit. Is this something you'd be wiling to contribute?

[franloza]

@sirupsen Sure! This seems a pretty good first issue.

[sirupsen]

@franloza yayy! On your PR, can you provide instructions on how (on an existing Snowflake cluster) to enable SSO so we can test it? ❤️

[sirupsen]

Accidentally closed with comment instead of just comment, apologies!

[franloza]

@sirupsen I don't think it's necessary to open a PR, the current implementation supports the use of an external browser to authenticate. The problem was that my connection string was wrong 🤦

I was using the following URI:

"snowflake://<user>@<account>/<database>/<schema>?warehouse=<warehouse>&role=<role>&authenticator='externalbrowser'"

I used quotes to pass externalbrowser parameter because I followed the Snowflake documentation for the Python client:

Client	Instructions
Python	Pass authenticator='externalbrowser' to the snowflake.connector.connect() function.

However, the correct instructions are the one for JDBC client:

Client	Instructions
JDBC	Set authenticator=externalbrowser in the connection string for the driver.

With the following URI, the connection works:

"snowflake://<user>@<account>/<database>/<schema>?warehouse=<warehouse>&role=<role>&authenticator=externalbrowser"

Is there any place where do you think this should be documented or should I close the issue?

[sirupsen]

OH very cool, we definitely need that documented! My best suggestion right now is to add a comment or footnote to the Snowflake connection string in the database table (try and see what looks less invasive and put it up for review) describing how to do this.

What does the workflow look like when you pass that? Does the driver hook-in and do an OAUTH workflow with stdin?

[franloza]

@sirupsen The workflow looks as follows:

1. This message appears in the console:

Initiating login request with your identity provider. A browser window should have opened for you to complete the login. If you can't see it, check existing browser windows, or your OS settings. Press CTRL+C to abort and try again...

https://github.com/snowflakedb/snowflake-connector-python/blob/f2f7e6f35a162484328399c8a50a5015825a5573/src/snowflake/connector/auth_webbrowser.py#L119-L124

2. A window is opened in your default browser and it carries out the authentication. Then a message like this appears:

Your identity was confirmed and propagated to Snowflake PythonConnector. You can close this window now and go back where you started from.

Then, the command is run.