Support SSO authentication in Snowflake

[carlineng]

What happens?

We should support SSO authentication for Snowflake in the VS Code extension.

From Slack:

Hi there. I'm attempting to try out Malloy with VS Code extension on top of Snowflake. Docs say you can configure a connection with ~/.snowflake/connections.toml file. I have the file configured (and I know it's good because I already use it for connecting from Python), but there is no obvious answer in how to configure Malloy to use that connection info. When I add a Snowflake connection in VS Code settings it wants account, user, password, etc. How do I configure it to use connections.toml?

For context: my company uses SSO, so we use externalbrowser authentication option, not user/password.

also:

Just an FYI - Snowflake now provides a dedicated Snowflake extension for VS Code which works seamlessly with SSO authentication.  I'm not sure if there's any way to re-use the connections across the Snowflake and Malloy extensions.

and

I'm no programmer, but it looks like the [email protected] (current 1.14) does provide for externalbrowser authentications.

Based on my experience with Archie Sarre Wood and the good folks at Evidence, I'd suggest using the Snowflake Node.js driver 1.12.0 or above, which gives you SSO credential caching.  Just turn on the server-side caching required by the Node driver, and use the updated driver and set clientStoreTemporaryCredential to true.

To Reproduce

N/A

OS:

N/A

Malloy Client:

N/A

Malloy Client Version:

N/A

Database Connection:

N/A

[whscullin]

Unfortunately the current implementation is incompatible with web authentication, even after updating. Snowflakes own pooling mechanism, which we use, doesn't support it:

 Error [ClientError]: connect() does not work with external browser or okta authenticators, call connectAsync() instead
    at createError (/Users/scullin/src/malloydata/malloy/node_modules/snowflake-sdk/lib/errors.js:537:17)
    at exports.createClientError (/Users/scullin/src/malloydata/malloy/node_modules/snowflake-sdk/lib/errors.js:374:10)
    at Connection.connect (/Users/scullin/src/malloydata/malloy/node_modules/snowflake-sdk/lib/connection/connection.js:219:20)
    at /Users/scullin/src/malloydata/malloy/node_modules/snowflake-sdk/lib/core.js:273:20
    at new Promise (<anonymous>)
    at ConnectionFactory.create (/Users/scullin/src/malloydata/malloy/node_modules/snowflake-sdk/lib/core.js:272:14)
    at Pool._createResource (/Users/scullin/src/malloydata/malloy/node_modules/generic-pool/lib/Pool.js:319:42)
    at Pool._dispense (/Users/scullin/src/malloydata/malloy/node_modules/generic-pool/lib/Pool.js:237:12)
    at Pool.acquire (/Users/scullin/src/malloydata/malloy/node_modules/generic-pool/lib/Pool.js:468:10)
    at Pool.use (/Users/scullin/src/malloydata/malloy/node_modules/generic-pool/lib/Pool.js:479:17) {

Sort of weird, since .connect() is async, afaict, but we'll need to figure out whether to tackle our own pooling or provide a non-pooled mechanism for web authentication.