msodbcsql linked against old openssl and is broken

[breiter (Brian Reiter)]

It seems like msqodbsql is linked against openssl 1.0.0 but macports installs openssl 1.1.1c at this stage.

Could not open /opt/local/lib/libssl.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
Could not open /opt/local/lib/libcrypto.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
--->  Found 2 broken files, matching files to ports
--->  Found 1 broken port, determining rebuild order
DEBUG: Broken: msodbcsql
DEBUG: Processing port msodbcsql @0:17.3.1.1_1  
DEBUG: Processing port mssql-tools @0:17.3.1.1_0 

$ ls /opt/local/lib/libcrypto.1*
/opt/local/lib/libcrypto.1.1.dylib
$ ls /opt/local/lib/libssl.1*
/opt/local/lib/libssl.1.1.dylib

$ port installed openssl
The following ports are currently installed:
  openssl @1.1.1c_0 (active)

I think this patch fixes it:

--- Portfile.bak	2019-09-10 22:49:11.000000000 +0200
+++ Portfile	2019-09-10 22:53:20.000000000 +0200
@@ -2,7 +2,7 @@
 
 name                msodbcsql
 version             17.3.1.1
-revision            1
+revision            2
 categories          databases
 platforms           darwin
 supported_archs     x86_64
@@ -30,8 +30,8 @@
                     
 build {
     system "install_name_tool -change /usr/local/lib/libodbcinst.2.dylib ${prefix}/lib/libodbcinst.2.dylib ${worksrcpath}/lib/libmsodbcsql.17.dylib"
-    system "install_name_tool -change /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib ${prefix}/lib/libcrypto.1.0.0.dylib ${worksrcpath}/lib/libmsodbcsql.17.dylib"
-    system "install_name_tool -change /usr/local/opt/openssl/lib/libssl.1.0.0.dylib ${prefix}/lib/libssl.1.0.0.dylib ${worksrcpath}/lib/libmsodbcsql.17.dylib"
+    system "install_name_tool -change /usr/local/opt/openssl/lib/libcrypto.1.0.0.dylib ${prefix}/lib/libcrypto.1.1.dylib ${worksrcpath}/lib/libmsodbcsql.17.dylib"
+    system "install_name_tool -change /usr/local/opt/openssl/lib/libssl.1.0.0.dylib ${prefix}/lib/libssl.1.1.dylib ${worksrcpath}/lib/libmsodbcsql.17.dylib"
 }
 
 destroot {

Thanks! brian reiter

[edgarsstrods (Edgars)]

I have this problem also:

DEBUG: skipping ppc in /opt/local/share/cmake-3.15/Modules/CPack.OSXScriptLauncher.in since this system can't run it anyway
Could not open /opt/local/lib/libssl.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
Could not open /opt/local/lib/libcrypto.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
--->  Found 2 broken files, matching files to ports
--->  Found 1 broken port, determining rebuild order
DEBUG: Broken: msodbcsql
DEBUG: Processing port msodbcsql @0:17.3.1.1_1  

[bvangelis1]

I have the same issue. Should we create a local patch or will this be resolved in a new update? Being new and asking...

--->  Scanning binaries for linking errors
Could not open /opt/local/lib/libssl.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
Could not open /opt/local/lib/libcrypto.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
--->  Found 2 broken files, matching files to ports
--->  Found 1 broken port, determining rebuild order
--->  Rebuilding in order
     msodbcsql @17.3.1.1 
Skipping deactivate msodbcsql @17.3.1.1_1 (dry run)
Skipping activate msodbcsql @17.3.1.1_1 (dry run)

[zhuka]

Got the same issue after upgrading outdated

Could not open /opt/local/lib/libssl.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
Could not open /opt/local/lib/libcrypto.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
--->  Found 2 broken files, matching files to ports
--->  Found 1 broken port, determining rebuild order
DEBUG: Broken: msodbcsql
DEBUG: Processing port msodbcsql @0:17.3.1.1_1  

[GreggGreen (Gregg Green)]

Installing php73-sqlsrv requires msodbcsql which fails

Error: Port msodbcsql is still broken after rebuilding it more than 3 times.
Error: Please run port -d -y rev-upgrade and use the output to report a bug.
Error: rev-upgrade failed: Port msodbcsql still broken after rebuilding 3 times
Error: Follow https://guide.macports.org/#project.tickets to report a bug.

debug output

could not open /opt/local/lib/libssl.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)
DEBUG: Marking /opt/local/lib/libmsodbcsql.17.dylib as broken
Could not open /opt/local/lib/libcrypto.1.0.0.dylib: Error opening or reading file (referenced from /opt/local/lib/libmsodbcsql.17.dylib)

[roederja]

I updated the msodbcsql and mssql-tools ports to the latest version: ​https://github.com/macports/macports-ports/pull/5386 .

It seems to work on my machine. Does this fix your issue?

[GreggGreen (Gregg Green)]

It installs but it's missing the ODBC Driver. This is the error I get when I try to use sqlsrv_connect() function in PHP 7.3.

	[Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed]

I was able to circumvent by using brew installation just for the sqlsrv.so extension (overriding macports).

​https://docs.microsoft.com/en-us/sql/connect/php/installation-tutorial-linux-mac?view=sql-server-2017

added to php.ini
extension=/usr/local/lib/php/pecl/20180731/sqlsrv.so

/usr/bin/ruby -e "$(curl -fsSL https://raw.githubusercontent.com/Homebrew/install/master/install)"
brew tap microsoft/mssql-release https://github.com/Microsoft/homebrew-mssql-release
brew update
brew install msodbcsql17 mssql-tools

I don't why it doesn't work. I have

msodbcsql @17.4.1.1_0 (active)
mssql-tools @17.4.1.1_0 (active)

It's close to working native with macports.

PS: Would be cool if Microsoft added macports to their instructions.

[roederja]

Do you have the openssl port installed? The mssqlodbc library doesn't seem to have an explicit dependency on libcrypto anymore, but it seems like it's trying to load it at runtime?

[edgarsstrods (Edgars)]

I can confirm the same for me - now it installs, but when I start using it, it gives this error in php. Also using sqlcmd, which depends on this port I get this:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

And yes, I have openssl installed.

[GreggGreen (Gregg Green)]

Yes

openssl @1.1.1d_0 (active)

and I even tried both versions:

openssl @1.1.1d_0 (active)
openssl10 @1.0.2t_0 (active)

[roederja]

That's odd. I tested it with sqlcmd. Which command are you running exactly? Also what do you get for

odbcinst -q -d

?

I get

[ODBC Driver 17 for SQL Server]

[edgarsstrods (Edgars)]

For this request odbcinst -q -d I get this answer [ODBC Driver 17 for SQL Server]

But when i use connection in PHP code still:

SQLSTATE [08001, -1]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [OpenSSL library could not be loaded, make sure OpenSSL 1.0 or 1.1 is installed]
SQLSTATE [08001, -1]: [Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection

[GreggGreen (Gregg Green)]

Here might be why Homebrew works and Macports doesn't. It's using the older version. Also, Microsoft SQL Server latest version doesn't work on newest version of Ubuntu. It has the same error we are getting. I think this is the problem. We can't use 17 yet.

/usr/local/bin/odbcinst
[ODBC Driver 13 for SQL Server]


/opt/local/bin/odbcinst -q -d
[ODBC Driver 17 for SQL Server]

[roederja]

Meh, I was hoping I could avoid the two port shenanigans that MSFT is doing on brew...

[GreggGreen (Gregg Green)]

maybe you could use version 13 or 17 with openssl10 @1.0.2t_0 so at least it works - I don't know.

[roederja]

Can you try now please? I did exactly what MSFT are doing in brew: ​https://github.com/macports/macports-ports/pull/5401

So there is now a new msodbcsql17 port. msodbcsql is back to version 13. The tools package still depends on msodbcsql17, so will likely remain broken in your setup. But I can't do much about that because MSFT have linked it against version 17. It's the same in brew.

[GreggGreen (Gregg Green)]

PHP 7.3 seems to be working now. I was able to connect and run a query. The msodbcsql17 failed to build but doesn't matter to me right now. It's probably not going to work until MSFT get's off openssl 1.0. MSSQL only works on Ubuntu 16 because of this. I upgraded to 18 and it blew up MSSQL. There are workarounds but it wasn't worth it to me so I just put it back. So the MSSQL server I use is running Ubuntu recommended by MSFT and I learned my lesson.

It's matching Homebrew now.

/opt/local/bin/odbcinst -q -d
[ODBC Driver 13 for SQL Server]

Thanks

[roederja]

Cool. I fixed the msosbcsql17 build. School boy error I made when changing the port name.

[GreggGreen (Gregg Green)]

You sure about that? I just tried and it gave me this:

/~/ > sudo port install mssql-tools
--->  Computing dependencies for mssql-tools
The following dependencies will be installed:  msodbcsql17
Continue? [Y/n]: 
--->  Fetching archive for msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from https://packages.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://aus.us.packages.macports.org/macports/packages/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://ywg.ca.packages.macports.org/mirror/macports/packages/msodbcsql17
--->  Fetching distfiles for msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from https://download.microsoft.com/download/1/9/A/19AF548A-6DD3-4B48-88DC-724E9ABCEB9A/
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from https://distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://aus.us.distfiles.macports.org/macports/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://ykf.ca.distfiles.macports.org/MacPorts/mpdistfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://ywg.ca.distfiles.macports.org/mirror/macports/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://mse.uk.distfiles.macports.org/sites/distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://lil.fr.distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://nue.de.distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://fco.it.distfiles.macports.org/mirrors/macports-distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from https://pek.cn.distfiles.macports.org/macports/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://aarnet.au.distfiles.macports.org/pub/macports/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://cjj.kr.distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://nou.nc.distfiles.macports.org/pub/macports/distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://jog.id.distfiles.macports.org/macports/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://jnb.za.distfiles.macports.org/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1.tar.gz from http://kmq.jp.distfiles.macports.org/msodbcsql17
Error: Failed to fetch msodbcsql17: The requested URL returned error: 404 Not Found
Error: See /opt/local/var/macports/logs/_opt_local_var_macports_sources_rsync.macports.org_release_tarballs_ports_databases_msodbcsql17/msodbcsql17/main.log for details.
Error: Follow https://guide.macports.org/#project.tickets to report a bug.
Error: Processing of port mssql-tools failed

[roederja]

maybe it takes a while to feed through.

[GreggGreen (Gregg Green)]

I'll keep trying and let you know. Thanks!

Replying to roederja:

maybe it takes a while to feed through.

[breiter (Brian Reiter)]

I removed mssql-tools, msodbcsql, unixODBC, and openssl10 and then did port install mssql-tools. It found msodbcsql17 and unixODBC as dependencies and installed everything without any broken binaries.

$ sudo port install mssql-tools
--->  Computing dependencies for mssql-tools
The following dependencies will be installed: 
 msodbcsql17
 unixODBC
Continue? [Y/n]: Y
--->  Fetching archive for unixODBC
--->  Attempting to fetch unixODBC-2.3.7_1.darwin_18.x86_64.tbz2 from http://jnb.za.packages.macports.org/packages/unixODBC
--->  Attempting to fetch unixODBC-2.3.7_1.darwin_18.x86_64.tbz2.rmd160 from http://jnb.za.packages.macports.org/packages/unixODBC
--->  Installing unixODBC @2.3.7_1
--->  Activating unixODBC @2.3.7_1
--->  Cleaning unixODBC
--->  Fetching archive for msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://jnb.za.packages.macports.org/packages/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from https://packages.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql17-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://mse.uk.packages.macports.org/sites/packages.macports.org/msodbcsql17
--->  Fetching distfiles for msodbcsql17
--->  Attempting to fetch msodbcsql-17.4.1.1.tar.gz from http://jnb.za.distfiles.macports.org/distfiles/msodbcsql17
--->  Attempting to fetch msodbcsql-17.4.1.1.tar.gz from http://mse.uk.distfiles.macports.org/sites/distfiles.macports.org/msodbcsql17
--->  Attempting to fetch msodbcsql-17.4.1.1.tar.gz from https://distfiles.macports.org/msodbcsql17
--->  Verifying checksums for msodbcsql17                                       
--->  Extracting msodbcsql17
--->  Configuring msodbcsql17
--->  Building msodbcsql17
--->  Staging msodbcsql17 into destroot
--->  Installing msodbcsql17 @17.4.1.1_0
--->  Activating msodbcsql17 @17.4.1.1_0
--->  Cleaning msodbcsql17
--->  Fetching archive for mssql-tools
--->  Attempting to fetch mssql-tools-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://jnb.za.packages.macports.org/packages/mssql-tools
--->  Attempting to fetch mssql-tools-17.4.1.1_0.darwin_18.x86_64.tbz2 from https://packages.macports.org/mssql-tools
--->  Attempting to fetch mssql-tools-17.4.1.1_0.darwin_18.x86_64.tbz2 from http://mse.uk.packages.macports.org/sites/packages.macports.org/mssql-tools
--->  Fetching distfiles for mssql-tools
--->  Verifying checksums for mssql-tools
--->  Extracting mssql-tools
--->  Configuring mssql-tools
--->  Building mssql-tools
--->  Staging mssql-tools into destroot
--->  Installing mssql-tools @17.4.1.1_0
--->  Activating mssql-tools @17.4.1.1_0
--->  Cleaning mssql-tools
--->  Scanning binaries for linking errors
--->  No broken files found.
--->  No broken ports found.

[GreggGreen (Gregg Green)]

IT WORKS! Thanks

[edgarsstrods (Edgars)]

I put the update in morning (EEST) and forgot to send thanks for the fix, because it (and I) started working. Thank you!

When should I try/use the msodbcsql17 package instead of msodbcsql?

[roederja]

@edgarsstrods : Not really sure. It's a question for Microsoft... It is possible to install both packages alongside each other.

[eborisch (Eric A. Borisch)]

Interested users, please take a look at ​https://github.com/macports/macports-ports/pull/5847 and make sure it doesn't break anything for your use cases.