ODBC driver can't find correct openSSL version after upgrading openSSL

Erfan Nariman 436 Reputation points
2021-10-06T10:23:22.66+00:00

Update: the problem and solution (workaround) is described here and is caused by homebrew: https://github.com/microsoft/homebrew-mssql-release/issues/59

---

After upgrading to Python3.10 with homebrew, my OpenSSL was upgraded to version 3 as well.

Now I can't connect to my SQL Server anymore, since ODBC requires OpenSSL 1.1 or 1.0. So when I run:

   isql -v -k "<connection string"  

I get the following error

   [08001][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]  
   [08001][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection  

But when I look in /usr/local/etc/ I see that openssl@1.1 is installed:

138143-screenshot-2021-10-06-at-122218.png

How can I solve this problem?

I am on a mac, version 11.5.2

Other useful output:

   ➜  ~ brew list openssl@1.1  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/.bottle/etc/ (7 files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/bin/c_rehash  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/bin/openssl  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/include/openssl/ (104 files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib/libcrypto.1.1.dylib  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib/libssl.1.1.dylib  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib/engines-1.1/ (2 files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib/pkgconfig/ (3 files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib/ (4 other files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/share/doc/ (3971 files)  
   /usr/local/Cellar/openssl@1.1/1.1.1l_1/share/man/ (3971 files)  
   ➜  ~ ls -l /usr/local/opt/openssl/lib/libssl.dylib  
   lrwxr-xr-x  1 erfannariman  admin  14 Sep  7 13:46 /usr/local/opt/openssl/lib/libssl.dylib -> libssl.3.dylib  

Also, weird thing is, when I run openssl command, it finds the correct version:

   ➜  ~ openssl  
   OpenSSL> version  
   OpenSSL 1.1.1l  24 Aug 2021  
   OpenSSL>  

Seems like ODBC can't find the correct openssl version, when I run:

   DYLD_PRINT_LIBRARIES=1 isql -v -k "<connection string"  

I get:

   dyld: loaded:  /usr/local/opt/openssl/lib/libssl.dylib  
   dyld: loaded: /usr/local/Cellar/openssl@3/3.0.0/lib/libcrypto.3.dylib  
   [08001][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]  
   [08001][Microsoft][ODBC Driver 17 for SQL Server]Client unable to establish connection  
   [ISQL]ERROR: Could not SQLDriverConnect  

So it is still pointing to openssl@3 which is the wrong version for ODBC.

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
13,332 questions
0 comments No comments
{count} votes

Accepted answer
  1. Erfan Nariman 436 Reputation points
    2021-10-07T13:12:42.073+00:00

    After trying many things, I found a (hacky) solution:

    I removed the link in /usr/local/opt/:

    rm openssl
    

    Created a new link to openssl@1.1

    ln -s /usr/local/Cellar/openssl@1.1/1.1.1l_1 /usr/local/opt/openssl
    

    Can we leave this issue "open" for now, hopefully someone will come with a better more stable solution.

    3 people found this answer helpful.

2 additional answers

Sort by: Most helpful
  1. CathyJi-MSFT 21,126 Reputation points Microsoft Vendor
    2021-10-07T02:17:19.037+00:00

    Hi @Erfan Nariman ,

    From the screenshot that you offered, the version of odbcini.default openssl is 3. Did you try to change it to openssl 1.1.

    Quote from a similar thread.

    >Modify /etc/ssl/openssl.cnf config file as follows;
    >Change the last line from CipherString = DEFAULT@SECLEVEL=3 to CipherString = DEFAULT@SECLEVEL=1.1

    1 person found this answer helpful.

  2. Cheong00 3,476 Reputation points
    2021-10-06T11:58:21.627+00:00

    Make sure both /usr/local/Cellar/openssl@1.1/1.1.1l_1/bin and /usr/local/Cellar/openssl@1.1/1.1.1l_1/lib are in your "path" environment variable.