Can't connect to Sqlserver with Openssl 1.1.1c (Error code 0x2746)

[fabiang]

PHP Driver version or file name

5.6.1

SQL Server version

Microsoft SQL Server 2014 (SP3-CU3) (KB4491539) - 12.0.6259.0 (X64)
Apr 1 2019 22:19:54
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 (Build 9600: )

Client operating system

Debian GNU/Linux 10 (buster)

PHP version

PHP 7.3.8

Microsoft ODBC Driver version

17.4.1.1-1

Problem description

The base images of PHP for Docker just got upgraded to Debian 10, which includes OpenSSL 1.1.1c. I am extending those base images and install pdo_sqlsrv as PHP extensions in the latest versions.

I can't connect any longer to an Sqlserver 2014 server, which seems related to OpenSSL. The error I get is:

SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746

When I downgrade OpenSSL to version 1.1.0k the issue is gone:

wget http://security.debian.org/debian-security/pool/updates/main/o/openssl/openssl_1.1.0k-1~deb9u1_amd64.deb
dpkg -i openssl_1.1.0k-1~deb9u1_amd64.deb

The issue also doesn't occur when connecting to Sqlserver 2017 (not tested with 2019). Issue #252 seems unrelated to this one.

I guess this issue has an impact on all OS using newer OpenSSL together with Sqlserver 2014. I've also noticed problems when connecting via JDBC to Sqlserver 2012 from my local Fedora (OpenSSL 1.1.1c) in the last few days.

[yitam]

hi @fabiang I can reproduce the problem, except that SQL Server version does not seem to matter. I can't connect to SQL Server 2017 or 2014 without first downgrading openssl to 1.1.0k.

We will investigate and get back to you on this.

[yitam]

@fabiang

This is a workaround for now:

Modify /etc/ssl/openssl.cnf config file as follows (fyi see known issues with OpenSSL 1.1.1 in Debian 10):

Change the last line from CipherString = DEFAULT@SECLEVEL=2 to CipherString = DEFAULT@SECLEVEL=1

I can connect to SQL Server 2017 or 2014 without the need to downgrade OpenSSL.

[ioistired]

@yitam that worked for me as well. Waiting on a proper upstream fix.

[fabiang]

@yitam Thanks for the workaround!

Is it even possible to fix this upstream? I understand, that OpenSSL removed some older and unsecure ciphers. Doesn't instead the config of the Sqlserver needs to be changed?

[yitam]

Glad to hear the workaround, i.e. the temporary solution, works for you both, @fabiang and @bmintz

As indicated in known issues with OpenSSL 1.1.1 in Debian 10:

the SECLEVEL 2 setting the security level to 112 bit. This means that RSA and DHE keys need to be at least 2048 bit long. SHA-1 is no longer supported for signatures in certificates and you need at least SHA-256

Hence, Debian 10 has disabled SHA1 by default -- became more secure but less compatible. Those with older certificates with SHA1 hash or signatures <2K bit will be affected. In other words, this is actually a server / environment configuration issue.

[danailkh]

hi, i'v same error
ubuntu server v19.04
sql server 2017
What settings should be made for ssl?

[ioistired]

@danailkh re-read this thread. There's a configuration change you can make in openssl.cnf. Or you can upgrade your SQL Server's certificate.

[fabiang]

I can confirming the workaround is working too.

For those using Docker (Debian-based PHP image), you can try this:

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \
    && sed -i -E 's/(CipherString\s*=\s*DEFAULT@SECLEVEL=)2/\11/' /etc/ssl/openssl.cnf \
    && rm -rf /var/lib/apt/lists/*

[yitam]

Thanks @fabiang for your confirmation and tips for others.

Hi @danailkh, as @bmintz said, please read the comments above, since this is a configuration issue.

[ioistired]

@yitam can you point us to directions on how to upgrade the security of our SQL Server certificates?

[yitam]

@fabiang you mentioned in your original problem description that you could connect to SQL Server 2017 but not 2014. I can confirm this case now.

When I first tested this, I attempted to connect to a SQL Server 2017 instance, an upgrade from an older sql server. That connection attempt failed. However, when I tried another SQL Server 2017 instance (a fresh install), it works, just as you said.

This article nailed it. Changes to hashing algorithm for self-signed certificate in SQL Server 2017

@bmintz I hope the following articles help. If not, please post your feedback/questions to sql server forum directly.

SHA-256 Self Signed Certificate for Windows Server 2012 R2

Enable Encrypted Connections to the Database Engine

[fabiang]

@yitam I guess 2017 and up generates better certs by default. Thats why we couldnt reproduce this.

[fabiang]

I'm closing this issue now, since this can't be fixed in pdo_sqlsrv or msodbcsql.

[avfigueredo]

I can confirming the workaround is working too.

For those using Docker (Debian-based PHP image), you can try this:

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \
    && sed -i -E 's/(CipherString\s*=\s*DEFAULT@SECLEVEL=)2/\11/' /etc/ssl/openssl.cnf \
    && rm -rf /var/lib/apt/lists/*

Works for me! Thanks!

[fabiang]

@ALL @avfigueredo Caution: the above workaround will downgrade your OpenSSL to allow older, deprecated and insecure ciphers and can harm your security! Instead consider updating the certificates of your SQLServer instance.

On most other Linux systems (e.g. Fedora, RHEL, CentOS) you can "downgrade" your cipher suite with the command update-crypto-policies.

[virusphp]

@fabiang

This is a workaround for now:

Modify /etc/ssl/openssl.cnf config file as follows (fyi see known issues with OpenSSL 1.1.1 in Debian 10):

Change the last line from CipherString = DEFAULT@SECLEVEL=2 to CipherString = DEFAULT@SECLEVEL=1

I can connect to SQL Server 2017 or 2014 without the need to downgrade OpenSSL.

thanks very much,
for help me to
with this issue

thanks a lot of

[sbreakey]

Just for the record if someone hits this on Ubuntu 22.04, the solution is to set CipherString = DEFAULT@SECLEVEL=0.
As I found here https://askubuntu.com/questions/1423215/ubuntu-22-04-how-to-set-lower-ssl-security-level

No idea what that really does, seems pretty dangerous.

[fabiang]

No idea what that really does, seems pretty dangerous.

It lowers all possible ciphers that can be used by OpenSSL on your system for all SSL/TLS connections, so it is highly dangerous. Unfortunately it's not possible to define the ciphers per host/connection with this driver/openssl. This driver does support this, so I'll create issue here.