search

microsoft / msphpsql

Microsoft Drivers for PHP for SQL Server
MIT License
1.8k stars 374 forks source link

SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol] #1056

Closed crisleiria closed 4 years ago

crisleiria commented 5 years ago

PHP Driver version or file name

latest

SQL Server version

2008 R2

Client operating system

Ubuntu 16.04.6 LTS (Docker container)

PHP version

7.3

Microsoft ODBC Driver version

latest

Table schema

Any table

Problem description

I am start using Docker for PHP development, and I want to connect to SQL Server from Laravel Project. I am not able to setup SQL Server driver on PHP-FPM container.

on SQL query I am getting this error:

SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1425F102:SSL routines:ssl_choose_client_version:unsupported protocol] (SQL: select ...

Please take in account I am trying to connect to an SQL Server 2008 R2 and upgrade server is not an option.. at least for now.

Expected behavior and actual behavior

Connection to SQL Server successfully

Repro code or steps to reproduce

I am using laradoc project to setup my needed docker containers. https://laradock.io/

Simply follow steps from here: https://laradock.io/getting-started/#2-2-installation and execute: docker-composer up -d nginx mysql

this is the DockerFile script to install SQL Server driver on PHP-FPM container

###########################################################################

SQL SERVER:

###########################################################################

ARG INSTALL_MSSQL=false

RUN set -eux; \ if [ ${INSTALL_MSSQL} = true ]; then \ if [ $(php -r "echo PHP_MAJOR_VERSION;") = "5" ]; then \ apt-get -y install freetds-dev libsybdb5 \ && ln -s /usr/lib/x86_64-linux-gnu/libsybdb.so /usr/lib/libsybdb.so \ && docker-php-ext-install mssql pdo_dblib \ && php -m | grep -q 'mssql' \ && php -m | grep -q 'pdo_dblib' \ ;else \ ###########################################################################

Ref from https://github.com/Microsoft/msphpsql/wiki/Dockerfile-for-adding-pdo_sqlsrv-and-sqlsrv-to-official-php-image

  ###########################################################################
  # Add Microsoft repo for Microsoft ODBC Driver 13 for Linux
  apt-get install -y apt-transport-https gnupg \
  && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
  && curl https://packages.microsoft.com/config/debian/9/prod.list > /etc/apt/sources.list.d/mssql-release.list \
  && apt-get update -yqq \
  && ACCEPT_EULA=Y apt-get install -y unixodbc unixodbc-dev libgss3 odbcinst msodbcsql17 locales \
          && echo "en_US.UTF-8 zh_CN.UTF-8 UTF-8" > /etc/locale.gen \
          && locale-gen \
  # Install Dependencies
  && ACCEPT_EULA=Y apt-get install -y unixodbc unixodbc-dev libgss3 odbcinst msodbcsql17 locales \
  && echo "en_US.UTF-8 UTF-8" > /etc/locale.gen \
  # link local aliases
  && ln -sfn /etc/locale.alias /usr/share/locale/locale.alias \
  && locale-gen \
  # Install pdo_sqlsrv and sqlsrv from PECL. Replace pdo_sqlsrv-4.1.8preview with preferred version.
  && if [ $(php -r "echo PHP_MINOR_VERSION;") = "0" ]; then \
    pecl install pdo_sqlsrv-5.3.0 sqlsrv-5.3.0 \
  ;else \
    pecl install pdo_sqlsrv sqlsrv \
  ;fi \
  && docker-php-ext-enable pdo_sqlsrv sqlsrv \
  && php -m | grep -q 'pdo_sqlsrv' \
  && php -m | grep -q 'sqlsrv' \
;fi \

;fi

david-puglielli commented 5 years ago

@crisleiria There is a known issue with older versions of SQL Server not supporting TLS 1.2 without an update - this may be the cause of the error. See #1023. If your SQL Server has not been updated to support TLS 1.2, I suggest you try updating it first - instructions here. Another option is downgrade the minimum supported TLS version on the client as suggested in #1023, but obviously this is not advised.

crisleiria commented 5 years ago

This is an development enviroment on an save local network, so I dont mind to downgrade TLS version, at least for now.

I already saw #1023 and try that option with no luck.

On my case I dont have [system_default_sect] section on /etc/ssl/openssl.cnf I would like to go to @afroty solution on issue #1023:

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \ 
    && sed -i 's,^\(MinProtocol[ ]*=\).*,\1'TLSv1.0',g' /etc/ssl/openssl.cnf \
    && sed -i 's,^\(CipherString[ ]*=\).*,\1'DEFAULT@SECLEVEL=1',g' /etc/ssl/openssl.cnf\
    && rm -rf /var/lib/apt/lists/*

But when I build PHP-FPM container with this code I have an error. Maybe the order on the script is not correct... I honestly dont know.

Note: I have 2 other PCs running vagrant with Homestead machine, I have installed SQL Server driver manually and connect to the same SQL Server and never found this issue..

I will investigate TLS configuration on vagrant homestead client machine.

david-puglielli commented 5 years ago

I have not been able to get your Dockerfile to work, so I can't reproduce the issue. I'm also not sure I'm using Laradock correctly - I assume you are running docker-compose up -d nginx mssqlrather than docker-compose up -d nginx mysql? Could you provide more detail, along with the Dockerfile you are using?

crisleiria commented 5 years ago

Hi @david-puglielli there is a very good question... ;) I was not clear, sorry for that.

Laradock is very complex project with a lot of diferent container setup mainly for Laravel developers. On this case it is nginx container that leverages also php-fpm and workspace containers (where docker file has the mssql script).

It happens that my Laravel web application connects to mysql and mssql. mysql container was irrelevant for this case...

Assuming that you have laradoc setup (follow docs or videos on Youtube). To consume mssql you need to edit .env file on laradoc folder and set this 2 variables to true: WORKSPACE_INSTALL_MSSQL=true PHP_FPM_INSTALL_MSSQL=true

this will install mssql requirement on php-fpm and workspace containers. docker files can be found on [laradock folder]/php-fpm and [laradock folder]/workspace If you search for SQL Server you will see batch script to install.

I have attached both docker files dockerFiles.zip

My main question is: What should I change to not use TLS on connection, or to overpass this error.. I can also downgrade mssql driver.. I dont know..

I think this is very usefull for anyone that uses PHP development with Docker and connects to mssql server. Docker is becoming very popular.

Thank you for helping.

david-puglielli commented 5 years ago

Thanks for the additional detail. I have not been able to reproduce the issue yet but will continue to investigate.

crisleiria commented 5 years ago

Dear @david-puglielli,

An update on this.. we manage to update SQL Server with SP package. Our current version is 10.50.6560.0. Before it was 10.50.6220.0

I have test with this update and the error code is diferent now... SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (SQL:...

A quick search on this error code and I see other with same error: https://stackoverflow.com/questions/57265913/error-tcp-provider-error-code-0x2746-during-the-sql-setup-in-linux-through-te/57343207#57343207

https://github.com/Microsoft/msphpsql/issues/252

I have made other tests and I can confirm no problems on firewall and network ports. If I close port I receive timeout errors. Connections is established, but there are some handshake or protocol problem(s).

Any sugestion? Tomorrow I will investigate more.

crisleiria commented 5 years ago

Dear @david-puglielli,

Other update on this... I have made a connection test to an SQL Server 2012 instance, version 12.0.6108.1

Error is the same... SQLSTATE[08001]: [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: Error code 0x2746 (SQL

So, we can discard SQL Server settings or configuration. It is something related with TLS settings on client side. We need to check apt-get scripts on docker files.

Thanks in advance

david-puglielli commented 5 years ago

The SQL Server versions you have listed include TLS 1.2 support, so you are correct that it is now a client side issue. The reason that this 

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \ 
    && sed -i 's,^\(MinProtocol[ ]*=\).*,\1'TLSv1.0',g' /etc/ssl/openssl.cnf \
    && sed -i 's,^\(CipherString[ ]*=\).*,\1'DEFAULT@SECLEVEL=1',g' /etc/ssl/openssl.cnf\
    && rm -rf /var/lib/apt/lists/*

errored out is because rm -rf /var/lib/apt/lists/* removes repo data used by apt, so after installing php-fpm none of the installs following it in the Dockerfile worked. But in any case, openssl.cnf on Debian 9 does not have the MinProtocol and CipherString entries in Debian 9, so these commands wouldn't do anything. You could instead try adding them manually - openssl.cnf doesn't change much between Debian 9 and 10, except for the following changes:

[ssl_sect] system_default = system_default_sect

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


Note that `MinProtocol` and `CipherString` are normally set to `TLSv1.2` and `DEFAULT@SECLEVEL=2`.
crisleiria commented 5 years ago

Hi @david-puglielli,

OK, i will give it a try.

In the meanwhyle and because I cannot stop development proccess, I have installed vagrant and homestead virtual machine and setup SQL Server driver manually. All works fine with Microsoft installation instructions with PHP 7.3 and latest PHP SQL Server driver on Ubuntu 18.04. I will also check openssl.conf from homestead machine.

I guess the problem is php-fpm and workspace containers on laradock are based on ubuntu 16.04 and not on 18.04. I also have opened an issue on laradoc github project.

I think the main question here remains... this solution is not a proper solution... if I build php-fpm container all changes on openssl.conf will be lost. If I want to have docker on production server, I will also have problems. Maybe I cannot use laradoc project on this case and start looking to other containers. Is there any official php-fpm / php-cli container with PHP SQL Server driver already installed?

Thank you for you analyse.

david-puglielli commented 5 years ago

There is one here but it is quite outdated and also based on Ubuntu 16.04. I will tag this issue as a feature request, as a few people have asked for a Docker container and we need to decide how to prioritise it in the near future.

In the meantime, please let us know if changing the openssl.cnf file as above solves the problem - if it does, we may be able to wrangle a workaround.

david-puglielli commented 4 years ago

@crisleiria Do you have any updates for us on this issue?

crisleiria commented 4 years ago

Hi @david-puglielli,

I am using now Homestead and vagrant virtual machine with no SQL connection issues. I havent tested anything else.

I will start a new project on January, and I would like to have docker environment properly setup this time on my machine.

So I will give it another try only then. If you want you can close this issue, if needed I will open a new topic.

Thank you for your support.

Best regards, Cristovao Oliveira

david-puglielli commented 4 years ago

OK, thanks for the update. I will close this issue.

leoqbc commented 4 years ago

The SQL Server versions you have listed include TLS 1.2 support, so you are correct that it is now a client side issue. The reason that this

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \ 
    && sed -i 's,^\(MinProtocol[ ]*=\).*,\1'TLSv1.0',g' /etc/ssl/openssl.cnf \
    && sed -i 's,^\(CipherString[ ]*=\).*,\1'DEFAULT@SECLEVEL=1',g' /etc/ssl/openssl.cnf\
    && rm -rf /var/lib/apt/lists/*

errored out is because rm -rf /var/lib/apt/lists/* removes repo data used by apt, so after installing php-fpm none of the installs following it in the Dockerfile worked. But in any case, openssl.cnf on Debian 9 does not have the MinProtocol and CipherString entries in Debian 9, so these commands wouldn't do anything. You could instead try adding them manually - openssl.cnf doesn't change much between Debian 9 and 10, except for the following changes:

  • Add ess_cert_id_alg = sha1 under the [tsa_config1] heading
  • Add openssl_conf = default_conf near the top
  • Add 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

Note that MinProtocol and CipherString are normally set to TLSv1.2 and DEFAULT@SECLEVEL=2.

This worked for me! thank you so much guys!!

asantibanez commented 4 years ago

The SQL Server versions you have listed include TLS 1.2 support, so you are correct that it is now a client side issue. The reason that this

RUN apt-get update -yqq \
    && apt-get install -y --no-install-recommends openssl \ 
    && sed -i 's,^\(MinProtocol[ ]*=\).*,\1'TLSv1.0',g' /etc/ssl/openssl.cnf \
    && sed -i 's,^\(CipherString[ ]*=\).*,\1'DEFAULT@SECLEVEL=1',g' /etc/ssl/openssl.cnf\
    && rm -rf /var/lib/apt/lists/*

errored out is because rm -rf /var/lib/apt/lists/* removes repo data used by apt, so after installing php-fpm none of the installs following it in the Dockerfile worked. But in any case, openssl.cnf on Debian 9 does not have the MinProtocol and CipherString entries in Debian 9, so these commands wouldn't do anything. You could instead try adding them manually - openssl.cnf doesn't change much between Debian 9 and 10, except for the following changes:

  • Add ess_cert_id_alg = sha1 under the [tsa_config1] heading
  • Add openssl_conf = default_conf near the top
  • Add 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

Note that MinProtocol and CipherString are normally set to TLSv1.2 and DEFAULT@SECLEVEL=2.

Worked for me also on a php:7.3-fpm docker image. Thanks!

crisleiria commented 2 years ago

@Ronnie-J I am still using Homestead. in the meanwhile Laravel 8 have sail... you do not need laradock anymore.

And I have also upgrated PHP to version 7.4. I believe this will fix the issues.. but I haven t tried yet.