ubuntu 22.04 sqlcmd can not connect to ms sql server 2016

Question

I have spent several days on google search to find a solution, but no right one. so I post it here.

I have 3 VM machines.

VM 1:
windows server 2012 R2 sp1, 192.168.1.10
sql server 2016, port 1432
sql server 2017, port 1433

VM 2:
ubuntu 20.04
openssl 1.1.1p

VM 3:
ubuntu 22.04
openssl 3.0.2

on VM2 (ubuntu 20.04):
connect to sql server 2016
sqlcmd -S 192.168.1.10,1432 -U test -P test
successfully connect
connect to sql server 2017
sqlcmd -S 192.168.1.10,1433 -U test -P test
successfully connect

on VM3 (ubuntu 22.04):
connect to sql server 2017
sqlcmd -S 192.168.1.10,1433 -U test -P test
successfully connect
connect to sql server 2016
sqlcmd -S 192.168.1.10,1432 -U test -P test
get error:
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : TCP Provider: Error code 0x2746.
Sqlcmd: Error: Microsoft ODBC Driver 17 for SQL Server : Client unable to establish connection.

then I check sql server 2016's log on VM1, find 2 errors:
Event ID: 36888
A fatal alert was generated and sent to the remote endpoint. This may result in termination of the connection. The TLS protocol defined fatal error code is 40. The Windows SChannel error state is 1205.
Event ID: 36874
An TLS 1.2 connection request was received from a remote client application, but none of the cipher suites supported by the client application are supported by the server. The SSL connection request has failed.

What I have tried:
1, modify /etc/ssl/openssl.cnf
MinProtocol = TLSv1.0
CipherString = DEFAULT@SECLEVEL=1

2, downgrade openssl to 1.1.1p

3, modify win server reg
HKLM SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Protocols\TLS 1.2
key: Enabled Value:1

4, execute windows update

I have tried all suggestions that I can find about "TCP Provider: Error code 0x2746." but none works for my case.

This issue happens when we sqlcmd to ms sql server 2016 in ubuntu 22.04 and openssl 3.0.2

any idea? any suggestion? any solution?

Answer 1

ubuntu 22.04

make openssl.cnf file ex) /usr/local/ssl/openssl.cnf

openssl_conf = openssl_init

[openssl_init]
providers = provider_sect
ssl_conf = ssl_sect

[provider_sect]
default = default_sect
legacy = legacy_sect

[default_sect]
activate = 1

[legacy_sect]
activate = 1

[ssl_sect]
system_default = system_default_sect

[system_default_sect]
CipherString = ALL:@SECLEVEL=0 

Run nagstamon with legacy TLSv1 ubuntu 22.04 openssl3

and export OPENSSL_CONF=/usr/local/ssl/openssl.cnf

ubuntu 20.04

make openssl.cnf file ex) /usr/local/ssl/openssl.cnf

openssl_conf = default_conf

[default_conf]
ssl_conf = ssl_sect

[ssl_sect]
system_default = ssl_default_sect

[ssl_default_sect]
MinProtocol = TLSv1
CipherString = DEFAULT@SECLEVEL=1

Ubuntu 20.04 - how to set lower SSL security level?

and export OPENSSL_CONF=/usr/local/ssl/openssl.cnf

Answer 2

you can also add to systemd setting the OPENSSL_CONF environment variable:

• create directory /etc/systemd/system/php8.1-fpm.service.d

• create a disable_SSL_SECLEVEL=2.conf file there with the contents

  [Service]

  Environment="OPENSSL_CONF=/usr/local/ssl/openssl.cnf"

Run

$ sudo systemctl daemon-reload

$ sudo restart php8.1-fpm