ODBC connection not working after elevating from JMP 17 to JMP 18

[#1]

Following elevation from JMP 17 to JMP 18 my ODBC connection stopped working. In JMP 17, it was used for various SQL queries embedded into Open Database() as well as New SQL Query() functions, pulling data from Google BigQuery. Depending on how I try to connect, I get slightly different error messages:

Open Database(), error message from log:

New SQL Query():

Windows user ID and password don't work in this window.

Create database connection via > File > Open Database > New Connection > Sign in:

I also created a new DSN from scratch, which didn't work either. The ODBC is not the latest version, as our IT restricts which versions we can use due to presumed bugs. Is it wrong to assume that if the ODBC connection worked in JMP 17 it should also work in JMP 18?

[#2]

You are not wrong.  

Specifically which versions of JMP 17 and JMP 18 are you using?  

This is a bug.  Contacting tech support will help us track this.

As the person involved in compiling libcurl update from source, I'm pretty sure no changes the build time commands were made. I will have to look into curl change lists to see if there were changes to the default build options.

[#3]

Are you able to connect with the Windows ODBC Manager?

What you are seeing the screen shot is the driver-supplied authorization dialog.

[#4]

@Paul_Nelson, thank you. The JMP versions are 17.2.0 and 18.2.0, run on Windows 11 Enterprise. I have contacted tech support and updated this question with the case nr. TS - 00201379.

@bryan_boone, yes, I am able to connect with the ODBC manager (screenshot below). As mentioned, connection works as expected under JMP 17, but doesn't under JMP 18.

Testing the connection:

Refresh token after sign in:

[#5]

@Paul_Nelson This is not JMP's libcurl, it's the one in the ODBC driver.

The error message is coming from the driver, not JMP.

Connections in JMP 17 should work in JMP 18.

What JMP 18 does that JMP 17 doesn't is use the connection string from the initial connection to create additional connects (or drop and reconnect as needed) If a driver doesn't return a competed connection string (per spec) then the subsequent connections will fail. This might be what you're seeing.

[#6]

@bryan_boone, I am not sure I understand:

• If connections in JMP 17 should work in JMP 18, then why do I see something different? 
• I created and configured a new DSN from scratch and tried connecting JMP to Google BigQuery via Create database connection via > File > Open Database > New Connection > Sign in. This did not work. I went through this route many times since JMP 14, and this approach is only failing now in JMP 18. Why?

Which next steps do you recommend to me? Force a version elevation of the ODBC driver, for example?

[#7]

@bryan_boone @Paul_Nelson @Dahlia_Watkins, upgraded to the latest Google BigQuery driver 3.1.2.1004. No change.

Below a few screenshots for documentation:

• JMP 17 left vs JMP 18 right
• Both tested using our in-house “view opener” add-in, which facilitates pulling data from Google BigQuery via JSL’s ‘New SQL Query()’.
• In the middle, proof of the ODBC driver update

I don’t understand what it requires for database login in JMP 18? Obviously, company internal user credentials are not sufficient. What else could it want?

When I hit “Change Data Source… > New Connection > Machine Data Source > select my user DSN > confirm with OK” I get the below JMP alert.

[#8]

I have run that setup here.

The driver is non-compliant with the SQLDriverConnect function in the ODBC spec.

Specifically, it doesn't return a completed Connection string upon successful connection.

I've worked with tech support and there is a solution with 18 using Data Connectors (to get passed the non-compliant part of the driver) and a previous version of the driver (to get passed the driver's libcurl error)

You can contact them to get all the specifics.

[#9]

Thank you, @bryan_boone. I have downgraded to a lower version of the Simba ODBC driver for Google BigQuery (3.0.2.1005, link to driver archive) and am now able to use JMP 18 to connect to our Google BigQuery data warehouse. @Dahlia_Watkins' help with identifying and pointing me towards this solution is greatly appreciated.

Before I close this issue accepting this response as solution, is there anything else besides what's already mentioned here that should or can be brought to Simba's / Google's attention via a support case on their end?

[#10]

Downgrading the driver would have affected the libcurl issue you experienced.

As for SQLDriverConnect, the completed connection string only contained the DSN, like DSN=MyBigQuery (where MyBigQuery was defined in the ODBC Manager). The ODBC spec says it should be a completed connection string, meaning you should have all the information in the returned connection string so you can use it (and only it) and create another connection.

The last thing is the completed connection string omits the catalog you configured your connection with (as in CATALOG=). You can see that in say, your Windows Registry, where the driver connection settings are stored at configuration time.