JDBC now requiring username to be passed with update to v0.2-10

[AlisonLanski]

I am running the following code to connect directly to snowflake using a jdbc driver.

DBI::dbConnect(RJDBC::JDBC(driverClass = "net.snowflake.client.jdbc.SnowflakeDriver", 
                                   classPath = "[location]/snowflake-jdbc-3.13.18.jar"),
                "jdbc:snowflake://[web address]/?authenticator=externalbrowser")

With version 0.2-8, this code works: it opens a browser window to collect credentials and establishes a connection for use in queries.
With version 0.2-10, this code throws an error, does not open a browser window, and does not establish a working connection

The error is provided is:

Unable to connect JDBC to jdbc:snowflake://[web address]/?authenticator=externalbrowser
JDBC ERROR: JDBC driver internal error: missing user name for opening session.

The JDBC ERROR message appears to be coming from the driver code (https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/core/SessionUtil.java) line 254 at the time of this issue submission.

I have tested the code using the current driver/old jdbc drivers, the current version of DBI/older versions of DBI, and the current/older version of RJDBC (all combinations). The only time I get the error is when using the current version of RJDBC (versions of other items don't matter), so I assume something in the update from 2-8 to 2-10 changed how information is being collected or passed from the driver.

This can be solved in my code by adding an explicit username to the connection string.

"jdbc:snowflake://[web address]/?user=[username]&authenticator=externalbrowser"

Even so, I would prefer not to introduce a parameter to the code if it can be avoided. (This code is part of an internal package for many database connections). I'm not familiar enough with your scripts to pinpoint which change led to this behavioral difference and if it is actually desirable for some reason. I am hoping there is a solution within the RJDBC package which can prevent the need to add an explicit username to the connection string.

Thanks!

[s-u]

Alison, looking at the driver sources it seems that the only authenticator type that does not require login is OAUTH, so you may want to consult the driver documentation for the correct properties you need to use (EXTERNALBROWSER authenticator does require a username). Note that the explicit driver authentication (which you are using) allows you to pass properties directly, they don't have to be part of the URL string. It is quite possible that you have to set the user regardless even if it is not the actual name of the user - again, check the driver.

The only difference between 0.2-8 an 10 is that previously the "old" way of obtaining a driver from driver manager was always used unconditionally, i.e., JDBC always used username and password first (even it it was just an empty string) since the old JDBC API required both in the method. This is now deprecated in JDBC and the driver is always used. The only possible issue is that we do not set the user property if the the name is empty ("") so if the driver needs the special value of an empty string, that may be a problem and you may need to pass that in the URL. So check for the requirements of the driver. I suspect that with the authentication method and driver you simply have to set the username, the driver doesn't allow you to skip it.

[AlisonLanski]

Thanks for this explanation. We have been using the code (as written) with externalbrowser authentication and username/pw supplied via browser login for at least 2 years, and have not run into any issues of "missing username" until now [and only on v0.2-10]. I checked previous versions of the driver code, and did not see any changes with the OAUTH vs EXTERNAL BROWSER parts of the scripts (indeed, this behavior can be replicated even if I use driver versions from up to 2 years ago: it works with v0.2-8 and fails with v0.2-10). This makes me think that the empty string change has caused the difference.

Based on your analysis, I tried the following:

con <- DBI::dbConnect(RJDBC::JDBC(driverClass = "net.snowflake.client.jdbc.SnowflakeDriver", 
                                  classPath = "G:/Shared drives/connectrND_drivers/snowflake-jdbc-3.13.18.jar"),
               "jdbc:snowflake://notredame.snowflakecomputing.com/?user=''&authenticator=externalbrowser")

Adding an "empty" user like this did trigger my browser to open and perform authentication. I even received the success message in the browser window (Your identity was confirmed and propagated to Snowflake JDBC driver. You can close this window now and go back where you started from.)
However, R still won't form the connection correctly, throwing this error message

JDBC ERROR: The user you were trying to authenticate as differs from the user currently logged in at the IDP.

(which makes sense: my actual username is not [empty string]). It turns out that as long as I have any string value after <user=> it will trigger the browser popup, but returns the error if the string and my username don't match.

Ultimately, it looks like I may need to add usernames into my functions, unless you think there is a good reason and method for handling empty strings better. I appreciate your time and help with this.

[s-u]

Alison, try using an empty user name: "jdbc:...?user=&authenticator=externalbrowser" - your above example sets the user name to the two-character string "''". If that doesn't work then it would require more digging into the driver to see which API of it ways supports setting the empty username.

[AlisonLanski]

Alas! leaving the user empty as you suggest simply results in the original error message missing user name for opening session Perhaps the updated JDBC behavior simply can't be skirted: it wants a username before the externalbrowser can supply it.

[s-u]

Alison, it is still possible to use the old way of connecting if you supply a NULL-driver, so:

con <- DBI::dbConnect(RJDBC::JDBC(NULL), ...)

That way the driver is loaded through the URL as it was in old versions. You may want to load the real driver first to make sure the classes are loaded in the JVM (i.e. you call the JDBC() as you did earlier with the class path etc.), but you don't pass that value it to the dbConnect function.

[AlisonLanski]

This suggestion did work!

RJDBC::JDBC(driverClass = "net.snowflake.client.jdbc.SnowflakeDriver", 
                                   classPath = "[location]/snowflake-jdbc-3.13.18.jar")
con <- DBI::cdbConnect(RJDBC::JDBC(NULL), 
                "jdbc:snowflake://[web address]/?authenticator=externalbrowser")

This will successfully handle authentication solely through the externalbrowser login, and does seem to get java what it needs for the driver. This is the best implementation option for my users, because it means that they don't have to change their behavior at all. (I just have to update the connection code in my package).

Would you recommend AGAINST this solution for any reason? For example, if it would create any known security risk or is likely to be deprecated, or is just the opposite of a best-practice? Otherwise I shall happily implement it and would like to thank you wholeheartedly for the assistance.