SNOW-1011471 Authentication failed when setting search_path parameter on connection string about gosnowflake

[sfc-gh-dszmolka]

hi and thank you for submitting this issue - thanks to all the details you provided, I reproduced the same problem.

However, I do not think it is anyhow related to the Snowflake Go driver; since the exact same behaviour reproduces with the Python driver as well:

import snowflake.connector


con = snowflake.connector.connect(
    user='admin',
    account='myaccount.eu-central-1',
    password='password',
    database='TEST_DB',
    schema='PUBLIC',
    session_parameters={ "SEARCH_PATH": "'$current'" }
)

print(con.cursor().execute("select current_user()").fetchall())
con.close()

crashes the exact same way, producing very similar output to that of gosnowflake's:

ERRO[0085]log.go:352 gosnowflake.(*defaultLogger).Errorln Authentication FAILED                        
2024/01/22 04:55:44 failed to run a query. SELECT 1, err: 370001 (08004): Internal error: Processing aborted due to error 300002:1428825699; incident 4650476.

suggesting something goes wrong inside the Snowflake engine, rather than the driver.

I now passed the details found during the investigation on to the team responsible for the Snowflake engine to investigate further.

While it gets sorted, you can perhaps consider setting up a multi-statement query as a workaround and after authenticating without SEARCH_PATH session parameter, fire it as the first query in the multi-statement, something like:

multi_statement_query := "ALTER SESSION SET SEARCH_PATH='$current'; SELECT 1;"

example program for multi-statement queries

tested it and specifying it inside the multi-statement query did not crash it.

Hope it helps you too. For this issue, I'm going to close it since it does not seem to be related to the gosnowflake driver but rather the backend does not seem to support SEARCH_PATH as part of the connection string.

[mattj150]

Thanks for the clarification on this @sfc-gh-dszmolka

Would you have any recommendations/work around on running ALTER SESSION every time a new connection is added to the connection pool? I'd prefer to set the search path only once after I run db.Open() instead of having to set it on every SQL query in my code.

[sfc-gh-dszmolka]

perhaps you could try using context, example from insertvariantobject.go from over the example programs here (many others use the same approach)

	db, err := sql.Open("snowflake", dsn)
	if err != nil {
		log.Fatalf("failed to connect. %v, err: %v", dsn, err)
	}
	defer db.Close()

	ctx := context.Background()
	conn, err := db.Conn(ctx)
	if err != nil {
		log.Fatalf("Failed to acquire connection. err: %v", err)
	}
	defer conn.Close()

per sql.DB.Conn documentation, this way a pool is used and thus the same session for all the subsequent commands = same ALTER SESSION.. should affect all subsequent commands in the same session. (edit: this is not true , I need to correct myself, there is no guarantee always the same connection will be returned from the pool. )

Alternatively perhaps since the param seems to be account lineage (Can be set for Account » User » Session), if that makes sense to your use case, you can 'hardcode' it even for one or more users with
ALTER USER <username> SET SEARCH_PATH = '<value...>'

then it will be the default for the particular user, without needing to set it always.