How to fix Microsoft ODBC Driver 17 for SQL Server : SSL Provider ssl_choose_client_version:unsupported protocol

Question

I have installed Sql Server 2019 Developer Edition and mssql-tools on my Ubuntu 20.04 minimal. I can connect to my localhost with no issue, but when I want to remote to another sql server:

sqlcmd -S <server> -U <username> -P <password>

I face this error:

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

I has this issue once in Debian 10, and also search the net for solution, so after that I change my openssl.conf manually (su permission needed):

nano /etc/ssl/openssl.cnf

and add these to my file:

• ess_cert_id_alg = sha1 under the [tsa_config1] heading

• openssl_conf = default_conf near the top

• the following at the end:

  [default_conf]

  ssl_conf = ssl_sect

  [ssl_sect]

  system_default = system_default_sect

  [system_default_sect]

  MinProtocol = TLSv1.0

  CipherString = DEFAULT@SECLEVEL=1

I know that MinProtocol and CipherString are normally set to TLSv1.2 and DEFAULT@SECLEVEL=2, but as I mentioned once in my Debian 10, I edited my openssl.conf and change TLSv1.2 to TLSv1.0 and DEFAULT@SECLEVEL=2 to DEFAULT@SECLEVEL=1 and my connection fixed, but in Ubuntu 20.04 minimal not only there wasn't these lines, but also when I insert these manually again I face the same error:

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

my opnessl version is:

OpenSSL 1.1.1f  31 Mar 2020

I also downgrade my openssl once to 1.0 but it didn't work either!

I couldn't find anything else so I came here to ask for help, appreciate your help.

Solution

The reason might be that your current openssl doesn't support / turned off some ciphers (supported by your previous installation) and the server requires them. Just compare output:

nmap --script ssl-enum-ciphers localhost
nmap --script ssl-enum-ciphers <DB SERVER IP>

Solution: try to install a new version of openssl (>1.1.1f) manually. I upgraded from 1.1.1f to 1.1.1p and it solved my problems, no extra configuration required. I also read similar cases with 18.04 -> 20.04 and 1.1.1f which affected other guys.

The manual installation looks like:

wget https://www.openssl.org/source/openssl-1.1.1p.tar.gz -O openssl-1.1.1p.tar.gz
tar -zxvf openssl-1.1.1p.tar.gz
cd openssl-1.1.1p
./config
make
sudo make install
sudo ldconfig
openssl version

Note: You can also avoid the first line above and manually download the source tar.gz by going to their download page.

Hope, this will help

Note if after the download and tar un-compress the directory is something like openssl-3.0.0 then change to that directory instead, run the steps above, but if openssl version fails with an error

error while loading shared libraries: libssl.so.3: cannot open shared object file: No such file or directory

you need to copy a few more files:

sudo cp /usr/local/lib64/libcrypto.so.3 /usr/lib
sudo cp /usr/local/lib64/libssl.so.3 /usr/lib
sudo cp /usr/local/lib64/libssl.so /usr/lib

and finally, update the dynamic linker's links and cache:

sudo ldconfig

Comments

[normic]

this just led me in the right direction, as it also works the other way round. Having an older Win Machine which does not support TLS1.2 which is provided by openssl 1.1.1d in Debian buster. Downgrading to TLSv1.0 works like a charm.

[brianlmerritt]

This was very useful - just needed sudo ldconfig at the end to ensure openssl was correctly configured