Can't connect to a legacy SQL Server DB in MacOS (OpenSSL v3 is suspicious)

226 views Asked by At

Due to a new brew installed in my MacOS Catalina, PHP 7.4 got mangled so I had to reinstall it. Because of that, I had also to reinstall PHP's dependencies. PHP and my local projects worked correctly with the exception of the connections to SQL Server (which are old databases that require TLS v1.0). Because of that, I reinstaled the PHP drivers and the brews FreeTDS, unixOBDC, msodbcsql17 and mssql-tools. However, when trying to connect with this command sqlcmd -S <DB_IP> this error comes in:

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSL Provider: [error:0A000102:SSL routines::unsupported protocol].
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

First I though that by modifying the /etc/ssl/openssl.cnf file as stated here, it would solve the problem but it didn't. I also tried to downgrade from OpenSSL v3.2 to OpenSSL v1.0 but hte error changed to this

Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : SSPI Provider:  The operation or option is not available.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Cannot generate SSPI context.

Both errors appear also in my PHP projects when trying to connect to SQL Server so I went back to OpenSSL v3. One of my theories is that OpenSSL somehow is not obeying the /etc/ssl/openssl.cnf modifications. This is because when running this command sudo dtruss sqlcmd -S <DB_IP> this appears in the whole trace:

open_nocancel("/usr/local/etc/openssl@3/openssl.cnf\0", 0x0, 0x1B6)      = 5 0

but no receferences of the /etc/ssl/openssl.cnf file appears. By the way, I also tried to set the modifications directly in /usr/local/etc/openssl@3/openssl.cnf with no avail. What else can I do before resorting to go to another dev machine? Some aditional details:

odbcinst -j

unixODBC 2.3.12
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /Users/MY-USER/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

odbcinst -q -d

[ODBC Driver 13 for SQL Server]
[ODBC Driver 17 for SQL Server]

OpenSSL v3.2 brew installation

openssl@3 
==> Patching
==> Applying cafccb768be5b8f5c21852764f7b2863b6f5e204.patch
patching file crypto/x509/by_file.c
==> perl ./Configure --prefix=/usr/local/Cellar/openssl@3/3.2.0_1 --openssldir=/usr/local/etc/openssl@3 --libdir=/usr/local/Cellar/openssl@3/3.2.0_1/lib no-ssl3 no-ssl3-method no-zlib darwin64-x86_64-cc enable-ec_nistp_64_gcc_128
==> make
==> make install MANDIR=/usr/local/Cellar/openssl@3/3.2.0_1/share/man MANSUFFIX=ssl
==> make test
==> Caveats
A CA file has been bootstrapped using certificates from the system
keychain. To add additional certificates, place .pem files in
  /usr/local/etc/openssl@3/certs

and run
  /usr/local/opt/openssl@3/bin/c_rehash
==> Summary
  /usr/local/Cellar/openssl@3/3.2.0_1: 6,798 files, 32.5MB, built in 23 minutes 33 seconds
==> Running `brew cleanup openssl@3`...
1

There are 1 answers

0
starboy98.1 On BEST ANSWER

DISCLAIMER: Under normal circunstances I do not recommend doing this. Better try first these proposed modifications instead.

Sort of found a workaround for now. I edited the /usr/local/etc/openssl@3/openssl.cnf file and setted ALL the modifications at the start of the file.

openssl_conf = default_conf

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=0

#
# OpenSSL example configuration file.

After that, I executed both sqlcmd and PHP as root and now I can connect to the old DBs. Fortunately, the DB will soon be upgraded so I can discard this workaround.