Unable to connect between Office 365 Excel and Mysql database

[BUMPRW]

Using: 2020 MAC mini M1 with OS14.2.1 Sonoma, also tested on 2019 MacBookPro INTEL
Using the Mysql drivers 8.2
iODBC tests successfully in standalone from DSN and System DSN
After starting Excel and from 'Data', 'GetData', 'From Data (Microsoft Query)'
On the MAC Mini unable to Add and configure a DSN, but that works fine on the MacBookPro
On both using the test function from system DSN the connection fails with: (the MacBookPro DSN test yielded the same result)
[iODBC][Driver Manager]dlopen(/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so, 0x0006): tried: '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open()), '/System/Volumes/Preboot/Cryptexes/OS/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (no such file), '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open())

Again both test fine outside of Excel in both system and DSN. I have contacted Microsoft and Apple and they don't have a clue, but Apple has all this information and more. I did attempt to add a subdirectory with sim link by doing a csrutil disable, but that section of the software is readonly.

I did try the Microsoft drivers and they appear to be broke and tech support at Microsoft didn't' disagree

Any help would be appreciated.

Richard Bump

[HughWilliams]

The [iODBC][Driver Manager]dlopen(/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so, 0x0006): tried: '/usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so' (file system sandbox blocked open()) error probably indicates there is some form of notarisation capability issue with the Office 365 Excel being used as indicated on this stack overflow post.

Is the MySQL ODBC driver Intel 64bit only as implied it the directory path name and can be confirmed with the command:

file /usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so

As if so and Excel is an Intel only app the macOS Rosetta 2 needs to be installed on M1 machines for it to work. See this post on OpenLink ODBC Driver and iODBC usage on Apple Silicon M1 machines for more details.

Note also that OpenLink provide MySQL ODBC Drivers for macOS with both Intel 64 and M1 arm64 binaries bundled and will work against Intel 64 or arm64 application on macOS M1 machines.

[smalinin]

Read here #29 at first
Note:

• move /usr/local/mysql-connector-odbc-8.2.0-macos13-x86-64bit/lib/libmyodbc8w.so to /Library/ODBC/...
  symlinks will NOT work, only hard link or copy/move
• also note that you must check all dynamic libs, that is used by MySQL ODBC driver, if driver used some dynamic libs from /usr/local/... so it must be fixed also, ie libs must be moved and pathes in libmyodbc8w.so must be fixed to new locations

[BUMPRW]

Hugh Williams, Thank you for the response. I noted there was only one suggestion on the Stack Exchange link you provided and I had tried something similar. I went through the process of disabling system integrity, shutdowns and reboots, csrutil disable and csrutil enable, and in-between those steps adding a symbolic link, but that failed since the /System/Preboot is read only. Without the correct bind commands from Apple, I'm hesitant to risk my computer in establishing the link in that read only portion of the software. To get what I did done was over a 16 step procedure and I'm not sure that is worthwhile for software that is being frequently updated. When I talked to Apple, they seemed to want to help, but pointed back to Microsoft. When I talked to Microsoft, they were aware that they had a problem, but didn't have a solution. In February 2023, Microsoft appeared to have the problem fixed in a Beta version of MS Office 365, but we are now beyond that version in stable and the ability to connect to an SQL database on a silicon MAC does not appear, nor does it appear on an Intel Mac that runs Sonoma. So I'm still researching and looking to see if I can solve this one. Again, thanks you for the response.

[BUMPRW]

Smalinin, just saw your comment after my last comment. Thanks, I'll give that a try.

[BUMPRW]

Smalinin, Just finished trying. As before works outside of Excel, but testing inside Excel yields the same error message. I think I need to fix that problem with '/System/Volumes/Preboot/Cryptexes/OS' but without Apple providing the proper commands and sequence I'm not willing to risk my computer, although Macs are very resilient. Again, thanks, appreciate all the help I can get.

[smalinin]

@BUMPRW
The next solution must works => #29 (comment)
It is for MariaDB odbc driver, but basic idea is the same.
Did you use these steps ?

[BUMPRW]

Smalinin, Nope, moved it to ~/Library/ODBC. Will give it a try. Thanks

[BUMPRW]

Smalinin,

  In the original configuration all the crypto libraries were in the same directory as the driver, except v1.1 which supposedly had an end of life last Feb-April timeframe.  When I questioned Microsoft why we were loading version 1.1 for their driver, I got a we need to sort that out.  So I need to move the 1.1 files over there also I assume . . . bad word to use but is that correct?

[smalinin]

1. You need move ALL libs, that is used by drivers and that is located in /usr/local/..
2. What kind of MySQL ODBC installer did you use the

• DMG Archive -- mysql-connector-odbc-8.2.0-macos13-x86-64bit.dmg
  OR
• Compressed TAR Archive -- mysql-connector-odbc-8.2.0-macos13-x86-64bit.tar.gz

I have rechecked Compressed TAR Archive installer, it contains all files, that could be simply copy to /Library/ODBC/.. without additional fix, the library pathes use RPATH already, so it must work, I think.
Try download Compressed TAR Archive installer, extract it and just copy ALL files from ./lib/* to /Library/ODBC/...

[BUMPRW]

this is what tool looks like for ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib
otool -L libmyodbc8w.so
libmyodbc8w.so:
@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
@rpath/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0)
/usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 1.0.0)
/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)
@rpath/libiodbcinst.dylib (compatibility version 1.0.0, current version 3.52.15)
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)
ls -l for same above
total 46920
-r--r--r-- 1 root wheel 2192208 Jan 5 14:02 libcrypto.1.1.dylib
-rw-r--r-- 1 root wheel 3471232 Jan 5 10:23 libcrypto.3.dylib
lrw-r--r-- 1 root wheel 17 Jan 5 10:23 libcrypto.dylib -> libcrypto.3.dylib
-rw-r--r-- 1 root wheel 392320 Jan 5 10:23 libfido2.1.13.0.dylib
lrwxr-xr-x 1 root wheel 21 Jan 5 10:23 libfido2.1.dylib -> libfido2.1.13.0.dylib
lrwxr-xr-x 1 root wheel 16 Jan 5 10:23 libfido2.dylib -> libfido2.1.dylib
-rwxr-xr-x 1 root wheel 8413824 Jan 5 10:23 libmyodbc8a.so
-rwxr-xr-x 1 root wheel 8430560 Jan 5 10:23 libmyodbc8w.so
-r--r--r-- 1 root wheel 506336 Jan 5 14:05 libssl.1.1.dylib
-rw-r--r-- 1 root wheel 598912 Jan 5 10:23 libssl.3.dylib
lrwxr-xr-x 1 root wheel 14 Jan 5 10:23 libssl.dylib -> libssl.3.dylib

Still have the same problem, but we gave it a try. Hope I have this right. I also compared to the original and except for the openssl 1.1 files it was the same. The tool output was identical, only openssl 3.0.
It doesn't see the earlier version of openssl and although Microsoft didn't come out and say it, they certainly implied it when they said they need to sort that one out

[TallTed]

@BUMPRW — I suggest that you code fence terminal output and similar blocks, as this generally helps with clarity. A line with just three backticks (```) above and below each block will do the job. It can also be helpful to code fence terminal commands, SQL queries, and similar short inline items, by putting a single backtick before and after (for example, `ls -l`, which displays as ls -l).

[BUMPRW]

Tall Ted,
Thanks, but I'm just a worthless pogue who comes to GitHub when I need help or clarity. Frankly, I'm clueless and will have to look that one up, but will give it a try, so it would be
'otool -L libmyodbc8w.so'
'libmyodbc8w.so:'
'@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)'
'@rpath/libcrypto.3.dylib (compatibility version 3.0.0, current version 3.0.0)'
'/usr/lib/libresolv.9.dylib (compatibility version 1.0.0, current version 1.0.0)'
'/usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 1319.0.0)'
'@rpath/libiodbcinst.dylib (compatibility version 1.0.0, current version 3.52.15)'
'/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)'

or should i start off as:

'otool -L ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib/libmyodbc8w.so'

because I just normally 'cd' to the directory I'm working with

[smalinin]

Everything works for me on macOS 12.6 on M1 cpu
I did next:

• download mysql-connector-odbc-8.2.0-macos13-arm64.tar.gz
• extract all files from ./lib/ in tar archive to /Library/ODBC/MySQL/

-rw-r--r--  1 root  wheel  3471232 Sep 22 18:40 libcrypto.3.dylib
lrwxr-xr-x  1 root  wheel       17 Sep 24 23:45 libcrypto.dylib -> libcrypto.3.dylib
-rw-r--r--  1 root  wheel   392320 Sep 22 19:23 libfido2.1.13.0.dylib
lrwxr-xr-x  1 root  wheel       21 Sep 24 23:45 libfido2.1.dylib -> libfido2.1.13.0.dylib
lrwxr-xr-x  1 root  wheel       16 Sep 24 23:45 libfido2.dylib -> libfido2.1.dylib
-rwxr-xr-x  1 root  wheel  8413824 Sep 24 23:45 libmyodbc8a.so
-rwxr-xr-x  1 root  wheel  8430560 Sep 24 23:44 libmyodbc8w.so
-rw-r--r--  1 root  wheel   598912 Sep 22 18:40 libssl.3.dylib
lrwxr-xr-x  1 root  wheel       14 Sep 24 23:45 libssl.dylib -> libssl.3.dylib

• add next to /Library/ODBC/odbcinst.ini

[ODBC Drivers]
MySQL = Installed

[MySQL]
Driver = /Library/ODBC/MySQL/libmyodbc8w.so

• add DSN to /Library/ODBC/odbc.ini

[ODBC Data Sources]
mysql	= MySQL

[mysql]
Driver = /Library/ODBC/MySQL/libmyodbc8w.so
Server=192.168.0.22
Database=world

• run macOS app iODBC Administrator and tested DSN

• run MS Excell and try Import data From Database (Microsoft Query)

Screen.mp4

[TallTed]

Backticks (`) a/k/a GRAVE ACCENTs (U+0060); not single-quotes (') a/k/a APOSTROPHEs (U+0027).

Inline would look like `otool -L libmyodbc8w.so` (or `otool -L ~/Library/ODBC/mysql-connector-odbc-8.2.0-macos13-arm64/lib/libmyodbc8w.so`), and the block that follows would look like this to you, as the writer --

```
libmyodbc8w.so:
@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
...
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)
```

-- which would then look like this to us, as the readers --

libmyodbc8w.so:
@rpath/libssl.3.dylib (compatibility version 3.0.0, current version 3.0.0)
...
/usr/lib/libc++.1.dylib (compatibility version 1.0.0, current version 1300.32.0)

This is particularly important when the blocks include characters which GitHub might interpret as Markdown, radically changing what is presented to your readers. Here, it just helps distinguish Terminal input/output Vs. human writing (yours or ours).

[BUMPRW]

Sergey,

  Okay, 1st I'm going to make mine look like yours, except I'm running OS14.2.1.  By the way I haven't run it in a couple of years but I know it works on my Mac-mini which is running High Sierra.  If that doesn't work I'm going to uninstall the DMG version and reinstall using 'mysql-connector-odbc-8.2.0-macos13-arm64.tar.gz'.  Again, thanks.

[BUMPRW]

Tall Ted,

 Where is that on the keyboard? alt character? 

 I use those character codes in Latex, R and Python and when I want to print something special, but that's like $$\U0060$$ 

[TallTed]

@BUMPRW — On my US keyboards, ` is lowercase on the key where uppercase is tilde ~, at upper-left, to the left of 1, below ESC and above TAB.

[BUMPRW]

Tall Ted,

 ``` Okay, I've never used that key.  They don't teach that in mid 1970s Fortran crash courses for linear methods.  Thanks.  

[BUMPRW]

Sergey,

  Step1 failed with the same results. 

So I'm cleaning out the old and loading the .gz file 

[smalinin]

First - your DSN must work in iODBC Administrator app when you click on Test connection

[BUMPRW]

Sergey,

 It works fine when Excel is not running.  

As soon as you try to test it in Office 365 Excel v 16.80 you get the message.

[smalinin]

I updated macos to Sonoma 14.2.1 - it works also in MS Excell .
I use MS Excell ver 16.80 (23121017)

[smalinin]

Try to check your Excell app with Finder, open Get Info for app and recheck, that Excell app is Universal and that you don't have checked option Open using Rosetta - actual for M1 cpu

[BUMPRW]

Sergey,
I'm going to reload MsOffice and rosetta was not checked.

Another strange behavior is that User DSN does not allow adding in Excel.

I don't know of any strange configurations I've done with Excel, but I do use it frequently.

[BUMPRW]

Sergey,

I have a user DSN configured in iODBC, I just have the server and database data filled in.  You appear to use your IP address, I use local host but have tried the IP address and 127.0.0.1 could that be a factor?

[smalinin]

I have MySQL server on an another host.

[BUMPRW]

Symptoms are completely different today.
I can see changes I make in iODBC outside Excel, and I did use your init setups initially
Inside of Excel I see nothing but the driver and get Request Failed on any attempt.
I've played around with permissions, but even with everyone that doesn't make a difference.

[TallTed]

Tall Ted,

 ``` Okay, I've never used that key.  They don't teach that in mid 1970s Fortran crash courses for linear methods.  Thanks.  

Note that when you use the ``` Markdown code fence on leading and trailing lines, you need to include line breaks in your text, because it's like HTML's <code><pre> ... </pre></code>. When you use the ` Markdown code fence, it is more like HTML's <code> ... </code> (no <pre>).

Also note, I did not suggest wrapping all of your messages in code fences; only the CODE (e.g., odbc.ini content) and Terminal blocks (e.g., ls -l command and output).

---

To your continuing issues --

It is generally best to use only System DSNs, for a variety of reasons, including the way the sandboxing works on recent versions of macOS. Even if you need to switch to User DSNs or File DSNs later, I suggest you start by getting a System DSN working.

You may also want to consider using a commercial ODBC driver, such as one from OpenLink Software (maintainer of iODBC). We have strong incentive to make our drivers work "out of the box", which you can test with a $0.00 (FREE!) 30-day evaluation license. Only you can decide whether continuing to tinker with mysql-connector-odbc will "cost" you less than the license fees for such a commercial driver (see current special offers here).

[BUMPRW]

Tall Ted,

Thanks, I'll look into that, but I was trying to avoid the expense because I use databases infrequently.

The connector saves time in converting the output to a ".csv" file.

I have a few more thoughts regarding permission issues that I'm going to try.

I have read some of your previous posts and have reset permissions on the ".ini" files, plus I'm going to re-read Sergey's notes.

I reconfigured last night following his notes, I thought, but Excel is still not reading the odbc.ini file.

Though my wife and I are much older, she is a grad-student and sometimes it is easier to deal with databases than large Excel files.

It is too bad Borland's Dbase is no longer around, I wrote some pretty heavy duty stand-alone programs in that language back in the mid 80s.

Again, Thanks, got to work on consolidating some "csvs", but if I solve my Dbase problem, I'll post a solution.

P.S. How do you get this editor out of the code mode? It seems to randomly jump into that mode, so I'm spacing sentences

[TallTed]

"Code mode" is turned on and off (for either selected text or text as you're entering it) by clicking the <> in the Write | Preview | H B I ... "Add a comment" toolbar. It can also be started by starting any given line with 4 spaces; ending such a start requires an empty line. You may find GitHub's Markdown documentation (also linked from the bottom of the "Add a comment" section, where it says "Markdown is supported") helpful in figuring this part out.