Msphpsql: Error information: SQLSTATE: 01000 Code: 0 Message: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found

[mauricio0015]

hello, today I bring this new error, produced on a server with SO Debian 9.2 php7.1 in the function of connections to SQL SERVER on WINDOWS USING THE BOOKSTORES
"extension = sqlsrv.so" >> /etc/php/7.1/apache2/php.ini
, but at the beginning the connection to the sql server I receive the following error:
Error information:

SQLSTATE: 01000
Código: 0
Mensaje: [unixODBC] [Driver Manager] No se puede abrir lib 'Controlador ODBC 13 para SQL Server': archivo no encontrado

[lilgreenbird]

hi @mauricio0015,

Looks like the ODBC driver was not installed properly. The error message is from the Driver Manager, complaining that it can't find the ODBC driver. Can you please check your odbc.ini and odbcinst.ini to make sure the ODBC driver entries are there? You should see the path of the ODBC driver in odbcinst.ini. Please make sure the path is valid and then verify your connection using isql first before attempting to connect with PHP. If isql succeeds and you're still having connection issues, please list the entries you have in your config files and we can debug further. Thanks.

[nicdnepr]

I got same problem
I use mssql on ubuntu 16.04
I removed package mssql-tools
and not can't install
I got error
mssql-tools : Depends: msodbcsql (< 13.2.0.0) but 17.0.1.1-1 is to be installed
also php cant connect to mssql

[lilgreenbird]

@nicdnepr your problem looks completely different than the one above. Are you trying to install mssql-tools? If you are using msodbcsql 17 preview then you will need to manually install the mssql-tools 17 preview which is also available in the preview folder
If you have further problems with this can you please open a new issue so we can look into this further and not hijack this one.
Thanks..

[neoacevedo]

To me, odbcinst.ini has the following:

[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql/lib64/libmsodbcsql-17.0.so.1.1
UsageCount=1

But I'm getting exactly the same issue on openSUSE, running PHP CLI commands.

[david-puglielli]

@neoacevedo Could you provide a repro script and the output you are getting?

[neoacevedo]

good, I am just using _yii migrate_ with the following configuration:

'db' => [
            'class' => 'yii\db\Connection',
            'dsn' => "sqlsrv:Server=external-or-local-server,1433;Database=mydatabase",
            'username' => 'SA',
            'password' => 'the-password',
            'charset' => 'utf8',
            'enableQueryCache' => false
        ],

On Server I use localhost with exactly the same results as if it were Amazon or Azure RDS.
The output is just like

$ php yii migrate
PHP Fatal error:  Uncaught PDOException: SQLSTATE[01000]: [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 13 for SQL Server' : file not found in /home/nestor/public_html/myproject/vendor/yiisoft/yii2/db/Connection.php:660

Although in local I can't connect via sqlcmd, on remote I can do it without problems.

[david-puglielli]

@neoacevedo The latest version of the pdo_sqlsrv driver defaults to ODBC driver 17, so if you are seeing an error message saying that 'ODBC Driver 13 for SQL Server' is not found, you are probably using an older version of the pdo_sqlsrv driver. Please try the latest preview by running pecl install pdo_sqlsrv-5.2.0RC1 or getting the binary from the release page and let us know if the problem is fixed. Thanks!

[neoacevedo]

Following this comment I uninstalled the latest pdo_sqlsrv and sqlsrv drivers and installed the 5.2.0RC1 and now the error changes to SSL Provider: [error:140A90A1:SSL routines:SSL_CTX_new:library has no ciphers]' so I will check for any issue related to this.

[dhawkshaw]

Hi, I've been having the same problems on this using Leap 42.3. In my case I reverted back to to the previous versions of the driver and tools in zypper. ie msodbcsql.13.1.9.2-1 and mssql-tools.14.0.6.0-1 and that at least allows me to keep working. In the meantime, I've disabled the ms repo so that I don't get any further updates.

[AnnoyingTechnology]

I had this issue too, as the file libmsodbcsql did exist, I looked at its dependencies

root@Mespilus:/opt/microsoft/msodbcsql/lib64# readelf -d libmsodbcsql-13.1.so.9.2 | grep NEEDED
 0x0000000000000001 (NEEDED)             Shared library: [libdl.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libodbcinst.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libcrypto.so.1.0.0]
 0x0000000000000001 (NEEDED)             Shared library: [libkrb5.so.3]
 0x0000000000000001 (NEEDED)             Shared library: [libgssapi_krb5.so.2]
 0x0000000000000001 (NEEDED)             Shared library: [libcurl.so.4]
 0x0000000000000001 (NEEDED)             Shared library: [libssl.so.1.0.0]
 0x0000000000000001 (NEEDED)             Shared library: [libuuid.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libstdc++.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libm.so.6]
 0x0000000000000001 (NEEDED)             Shared library: [libgcc_s.so.1]
 0x0000000000000001 (NEEDED)             Shared library: [libpthread.so.0]
 0x0000000000000001 (NEEDED)             Shared library: [libc.so.6]

And checking if each library was present on the system. This one was missing
0x0000000000000001 (NEEDED) Shared library: [libssl.so.1.0.0]
The package build by microsoft doesn't seem to reference/require/install the proper libssl.
It references libssl1.0.0 while debian 9 seems to have >1.0.0 installed.
Installing libssl1.0.0 from debian jessie fixed it. for me

[artemik]

@AnnoyingTechnology Where could I find the libssl.so.1.0.0 for Ubuntu?
I installed it via wget and dpkg. Is there a better way of doing this?

Many thanks for the fix!

[AnnoyingTechnology]

I guess on packages.ubuntu.com https://packages.ubuntu.com/artful/libssl1.0.0
But I'm not sure

[nenetto]

I leave this scripts that worked for me.

My problem was pretty similar yours and I tested all the options such as changing the driver location, making a symbolic link, modify /etc/*.ini files, etc... nothing worked.

My problem, running python 3.6, pyodbc package in a docker container from alpine was the library libssl1.0.0

Here you will find my installation script for pyodbc Debian 8 (alpine) docker image using the driver v13

DRIVER={ODBC Driver 13 for SQL Server}

The command I run for database connection was:

import pyodbc
connection_string = 'DRIVER={ODBC Driver 13 for SQL Server};'
connection_string += 'SERVER={0};DATABASE={1};UID={2};PWD={3};'.format(host,dbname,user,pwd)
connection = pyodbc.connect(connection_string)

[J4VMC]

Hello, I have the same issue running a Debian 8 docker container with PHP7.2 and Nginx. I've installed the MSSQL ODBC 13.1 drivers but I still get the

SQLSTATE [01000, 0]: [unixODBC][Driver Manager]Can't open lib '/opt/microsoft/msodbcsql/lib64/libmsodbcsql-13.1.so.9.2' : file not found

As others have suggested, I ran readelf -d libmsodbcsql-13.1.so.9.2 | grep NEEDED and then ldconfig -p | grep libraryname for each of the libraries, but to my surprise, all the needed libraries are available.

My odbcinst.ini contains the right path for the libmsodbcsql file, which also has the right permissions.

My Dockerfile looks like:

FROM php:fpm

RUN apt-get update \
    && apt-get install -y --no-install-recommends vim curl libssl libc6 debconf subversion git apt-transport-https apt-utils \
    build-essential locales acl mailutils wget zip unzip \
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/8/prod.list \
        > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get install -y --no-install-recommends \
        locales \
        apt-transport-https \
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && apt-get update \
    && apt-get -y --no-install-recommends install \
        msodbcsql \
        unixodbc-dev

RUN docker-php-ext-install mbstring pdo pdo_mysql \
    && pecl install sqlsrv pdo_sqlsrv xdebug \
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && \
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

What could be the problem if the libraries exist and the path is correct?

[nenetto]

Hey!

Did you try this line?
RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

I think this a key problem when installing MSSql Drivers
Take a look and let me know, please

[J4VMC]

@nenetto where would you put that line?

[nenetto]

@J4VMC Anywhere after apt-get update? Just add it to after your RUN command for MSQL:

# Microsoft SQL Server Prerequisites
...
RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

;)

[J4VMC]

@nenetto this is what I get when I run docker-compose up -d:

E: Package 'libssl1.0.0' has no installation candidate

And my Dockerfile looks like:

FROM php:fpm

RUN apt-get update \
    && apt-get install -y --no-install-recommends vim curl libc6 debconf subversion git apt-transport-https apt-utils \
    build-essential locales acl mailutils wget zip unzip \
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update \
    && curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add - \
    && curl https://packages.microsoft.com/config/debian/8/prod.list \
        > /etc/apt/sources.list.d/mssql-release.list \
    && apt-get install -y --no-install-recommends \
        locales \
        apt-transport-https \
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && apt-get update \
    && apt-get -y --no-install-recommends install \
        msodbcsql \
        unixodbc-dev

RUN DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

RUN docker-php-ext-install mbstring pdo pdo_mysql \
    && pecl install sqlsrv pdo_sqlsrv xdebug \
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && \
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

[nenetto]

@J4VMC ,

I check your docker image and you built it from php:fpm link here. This image is built from debian:stretch-slim link here.

This means that your image is debian based on the version 9.4 link here.

In summary, you should install following the instructions for Debian 9. However, you are installing drivers for debian 8
curl https://packages.microsoft.com/config/debian/8/prod.list -> From your Dockerfile.

So, my suggestion is the following Dockerfile for your problem

FROM php:fpm

RUN apt-get update \
    && apt-get install -y --no-install-recommends vim curl libssl libc6 debconf subversion git apt-transport-https apt-utils \
    build-essential locales acl mailutils wget zip unzip \
    gnupg gnupg1 gnupg2

ENV ACCEPT_EULA=Y

# Microsoft SQL Server Prerequisites
RUN apt-get update \
    && 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 install -y --no-install-recommends \
        locales \
        apt-transport-https \
    && echo "en_GB.UTF-8 UTF-8" > /etc/locale.gen \
    && locale-gen \
    && apt-get update \
    && apt-get -y --no-install-recommends install \
        msodbcsql \
        unixodbc-dev

RUN echo "deb http://security.debian.org/debian-security jessie/updates main" >> /etc/apt/sources.list && apt-get update && DEBIAN_FRONTEND=noninteractive apt-get install libssl1.0.0

RUN docker-php-ext-install mbstring pdo pdo_mysql \
    && pecl install sqlsrv pdo_sqlsrv xdebug \
    && docker-php-ext-enable sqlsrv pdo_sqlsrv xdebug

COPY php.ini /etc/php/latest/php.ini
COPY php-fpm-pool.conf /etc/php/latest/pool.d/www.conf

RUN curl -sSk https://getcomposer.org/installer | php -- --disable-tls && \
   mv composer.phar /usr/local/bin/composer

RUN groupadd dev -g 999
RUN useradd dev -g dev -d /home/dev -m

RUN rm -rf /var/lib/apt/lists/*

WORKDIR /var/www/symfony

EXPOSE 9000
CMD ["php-fpm"]

[J4VMC]

@nenetto what php-fpm image can I use that is based on Debian 8? I think that will help with the issue.

[nenetto]

I have no idea right now, take a look at dockerhub - php.

Anyway, in my last response I changed the debian repositories already. Test that image first and cross the fingers.

[J4VMC]

@nenetto still throws the E: Unable to locate package libssl error. I'll try and build a custom image based on Ubuntu 16.04. Thanks for your help

[sprankle]

Microsoft changed the package name

change msodbcsql to msodbcsql17

https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-2017&viewFallbackFrom=ssdt-18vs2017

[yitam]

@sprankle the name change applies to ODBC 17+
For those who want ODBC 13.1 the name remains the same