topic Re: MacOS M1 ODBC connection to MS SQL Server in Discussions

MacOS M1 ODBC connection to MS SQL Server

DSA — Thu, 08 Jun 2023 15:00:27 GMT

Hi,

Has anyone managed to get JMP 17.0 to query a SQL Server using ODBC on a M1/M2 Mac.

I have the native ODBC driver and I can connect from command line and python (pyodbc) without any issues, but from JMP I'm always getting a "not found" error regardless if I try to connect from JSL with open database() or from the query builder.

Here are my ODBC settings through unixODBC:

I have symlinks to /etc/odbcinst.ini and /etc/odbc.ini as well.

Has anyone found a guide or been through this?

Thanks in advance,

Daniel

Re: MacOS M1 ODBC connection to MS SQL Server

bryan_boone — Fri, 17 Mar 2023 13:28:57 GMT

I cannot speak the Native SQL Server, however JMP will connect to SQL Server on an M1/M1 Mac.

I do it with the ActualTech ODBC drivers. The ODBC driver is set up with either ODBC Manager or the IODBC Administrator. You might be able to test out (or even configure) your install with either of these apps.

The ActualTech driver configuration has a "test" button. This is good since if the drivers won't connect with the Manager (or Administrator), they won't connect in JMP either.

As a follow up, I will try the install.
I assume this is the reference?
https://learn.microsoft.com/en-us/sql/connect/odbc/linux-mac/install-microsoft-odbc-driver-sql-server-macos?view=sql-server-ver16

Re: MacOS M1 ODBC connection to MS SQL Server

DSA — Wed, 20 Dec 2023 22:04:33 GMT

Thanks Bryan,
yes that's the reference, as you can see it is biased against unixodbc and not iodbc/ODBCmanager.
I've actually had this on hold for a while only using a python connection from JMP to do the query since everything works fine with pyodbc through python.

I've today for the first time managed to get iODBC to accept another driver and it works fine through JMP in a JSL script (even though the UI for the query builder is acting out and does not allow me to pick any schemas or tables to work against).

That got me hooked on trying to get my ms sql servers up and running in the same way, but i still can't make it happen.

iodbc just shows these kinds of errors when i click test (even if i make a DSN straight out of the documentation):

and JSL will show similar stuff if i try to hit that DSN or driver:

If i hit the same DSN from python and pyodbc it works without any errors... I am wondering if it can be something with the encoding but iodbc or jmp are not really giving me a lot to troubleshoot on.
iODBC is at least not understanding how the driver is working, that's my only conclusion so far.

I guess i need to try other drivers...

Re: MacOS M1 ODBC connection to MS SQL Server

DSA — Mon, 08 Jan 2024 08:09:14 GMT

I managed to get it to work with the Simba SQL Server ODBC Driver by Magnitude if I run JMP under rosetta2.

And i can get it to work with ActualTech ODBC Driver without rosetta2 but i have some kerberos domain issues with that driver. I liked the ActualTech documentation and ODBC Manager, thanks for the tip @bryan_boone. If i can get the domain issues sorted i'll continue with those drivers, the cost is not significant.

Long story short, the Micosoft SQL Server ODBC Driver seems to be only unixODBC which JMP does not support.

Re: MacOS M1 ODBC connection to MS SQL Server

bryan_boone — Mon, 08 Jan 2024 14:01:02 GMT

is this the 17.8 driver?

17.7 would require JMP to run in rosetta since the driver is only for Intel.