SNOW-180917: SSO token cache feature not working after version 2.2.2

[damienrj]

Please answer these questions before submitting your issue. Thanks!

1. What version of Python are you using (python --version)?
   Python 3.7.7

2. What operating system and processor architecture are you using (python -c 'import platform; print(platform.platform())')?
   Darwin-19.6.0-x86_64-i386-64bit

3. What are the component versions in the environment (pip list)?
   I have keyring 21.3.01

4. What did you do?

I am using pool to execute multiple queries:
results = pool.starmap(utils.make_table, tables.values()) were make_tables is basically just doing

               sess.execute(query)

5. What did you expect to see?
   Only one SAML window prompting me for my SSO.

6. What did you see instead?
   Getting a new window for each connection.

I am not sure what changed with the caching but it doesn't seem to work any longer.

[sfc-gh-wshangguan]

Hi @damienrj, hope you are doing well! Sorry for the late response first. To help solve the issue, I might need more info. Are you still seeing this issue with the latest version? Below is the code snippet I used to reproduce the issue. From your view, is that correct for reproducing?

def sso_connect():
    conn = snowflake.connector.connect(
      user=config['user'],
      account=config['account'],
      host=config['host'],
      port=config['port'],
      authenticator='externalbrowser',
      protocol=config['protocol'],
      warehouse=config['warehouse'],
      database=config['database'],
      schema=config['schema']
    )
    return conn

class Session():
    def __init__(self):
        self.conn = None
        self.cur = None

    def __enter__(self):
        self.conn = sso_connect()
        self.cur = self.conn.cursor()
        return self.cur

    def __exit__(self, exc_type, exc_value, exc_traceback):
        self.cur.close()
        self.conn.close()

def test_sso_gh_issue(idx):
    print("__enter__: " + str(idx))
    with Session() as sess:
        sess.execute("SELECT current_version()")
    print("__exit__: " + str(idx))

if __name__ == '__main__':
    Pool(3).starmap(test_sso_gh_issue, [(i, ) for i in range(7)])

If the code is right, I can see more than one webpage pop up, in particular, 3 pop ups for this code. If that's exactly what you met, I would say it is expected behavior. Because at the beginning, we don't have any cached credential locally. Then we fork more than one process to parallelly connect to Snowflake by Pool(3). For these 3 processes, none of them can find cache because there is no cache at all. So the only thing they can do is to pop up a page for users to login. Once one of these three is finished, following tries to connect to Snowflake would be able to find the cache, which means no more pop ups. In my case, you can see I have run 7 times of test_sso_gh_issue, and I got 3 pop ups for first three processes & no pop ups for the rest of 4.

Hope this helps and please let me know if this is not your case!

[damienrj]

I will try with the latest version tomorrow. We were having the issue of each connection creating a popup where in the past we would only get one popup for multiple connections. I will also try your code snippet. Thanks

[sfc-gh-wshangguan]

Hi @damienrj, could you please also share more about the version if you still meet the problem with the latest version? Like, by after 2.2.2, do you mean you can still use SSO feature with versions <= 2.2.2 now but not versions > 2.2.2? Or none of them works for you now?

And here is the latest doc for Snowflake SSO. https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use.html#label-sso-with-command-line-clients. Maybe it's helpful.

Looking forward to your update later :)

[damienrj]

Sorry, I was out of office for a bit. But yeah, we can use the SSO feature with versions <= 2.2.2 but after that the same code that was sharing the cache not opens 10 plus windows where before there was only one. I did go and try the newest version and added secure-local-storage as an option and I do get prompted for keychain access. I still need to make a code snippet. But not with some issues with boto we can no longer use the older connector.

[sfc-gh-wshangguan]

Hi @damienrj, yeah, after v2.2.2, we move the cache into local key store for higher security. Hope that still works for you. Feel free to comment the new problems you meet if any!

[damienrj]

import snowflake.connector
import os
connection = snowflake.connector.connect(account='account', user=os.environ["USER"] + "@domain", authenticator="externalbrowser")
connection = snowflake.connector.connect(account='account', user=os.environ["USER"] + "@domain", authenticator="externalbrowser")
connection = snowflake.connector.connect(account='account', user=os.environ["USER"] + "@domain", authenticator="externalbrowser")

With snowflake-connector-python==2.2.2 This only opens one browser window, anything more recent opens three browser windows.

[feluelle]

Hi @damienrj, hi @sfc-gh-wshangguan,

I experience exactly the same behaviour with latest 2.6.2 version and secure-local-storage package installed. We are using dbt to run snowflake queries. The connector is prompting for every query dbt does.

cc @pecigonzalo

[sfc-gh-wshangguan]

Hi @damienrj @feluelle , I tried the same code above and failed to reproduce it. Can you please check the following two points?
1. Follow the To enable connection caching: in https://docs.snowflake.com/en/user-guide/admin-security-fed-auth-use.html#label-sso-with-command-line-clients. You would need to install the correct python connector deps, e.g., using pip install "snowflake-connector-python[secure-local-storage]". You also need to enable the cached token feature by setting the account-level parameter ALLOW_ID_TOKEN to true.
2. Make sure the python has access to the local secure storage, i.e., Keychain Access on Mac, Credential Manager on Windows.

[feluelle]

Hi @sfc-gh-wshangguan,

Thank you for your quick response.
We have followed the documentation and also installed with secure-local-storage and set ALLOW_ID_TOKEN to true, but still running into this issue.

[feluelle]

I have also tested keyring and it seems to work. I do not see any errors there.

[feluelle]

Hi @sfc-gh-wshangguan,

I don't know what has happened, but for some reason it starts working. 🚀 I don't know what has changed. :(

[iamontheinet]

Can we close this issue?