phpsql-serverodbcplesksqlsrv

PHP 7.2 + sqlsrv + string(237) "SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server


I need to connect to SQL Server from PHP 7.2 on PLESK CentOS 7 with sqlsrv.

I have all I need - as far as I know.

1 - I have installed msodbcsql package

2 - I have installed packages:

unixODBC-devel make gcc-c++ gcc autoconf automake plesk-php7.2-devel libtool-ltdl.x86_64 libtool-ltdl-devel.x86_64

3 - I have added pdo_sqlsrv and sqlsrv modules

4 - I reread PHP handlers

5 - I reread the components properties to make modules visible in GUI

6 - I have checked: /opt/plesk/php/7.2/bin/php -m | grep sqlsrv

and I see:

pdo_sqlsrv
sqlsrv

7 - I have restarted the PHP-FPM master process to apply changes to FPM sites

8 - I have checked I have the ODBC driver in latest version:

Package unixODBC-2.3.11-1.rh.x86_64 already installed and latest version

unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini

9 - I have checked and I have ini files for php7.2 in path /opt/plesk/php/7.2/etc/php.d: odbc.ini

but still when trying to connect from script I see error:

string(237) "SQLSTATE[IMSSP]: This extension requires the Microsoft ODBC Driver for SQL Server to communicate with SQL Server. Access the following URL to download the ODBC Driver for SQL Server for x64: https://go.microsoft.com/fwlink/?LinkId=163712"

My code to connect:


$params = [
    "UID" => $username,
    "PWD" => $password,
    "Database" => $database,
    "TrustServerCertificate" => true
];
if(sqlsrv_connect($host, $params)) {
    echo "connected..";
} else {
    echo "Connection could not be established. DO not know why..sick!<br />";
    echo "<pre>";
    die( print_r( sqlsrv_errors(), true));
    echo "</pre>";
}

That works on PHP8.0 on the same server (different webspace).

What am I missing?

Update - further info:

For debugging I have locked remote access to MS SQL server - to make sure if the problem is on Apache/www side or MS SQL. The error doesn't change - so I am sure the problem is somewhere on web server - but no idea what is wrong..


Solution

  • I finally made it work.

    What was wrong?

    step 1 - I have installed msodbcsql package

    and I did - I took info about it from here .

    # curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
    # yum remove unixODBC-utf16 unixODBC-utf16-devel
    # ACCEPT_EULA=Y yum install msodbcsql18
    

    There is info to install Microsoft ODBC 18 (msodbcsql18) and sqlsrv-5.8.1 for PHP 7.2 - and that will not work together.

    It was ver 18 - Microsoft ODBC 18 . In my research I went again here - and find out that php driver 5.8.1 will not work with Microsoft ODBC 18. Have missed that before.

    So I have installed previous Microsoft ODBC 17

    # curl https://packages.microsoft.com/config/rhel/7/prod.repo | sudo tee /etc/yum.repos.d/mssql-release.repo
    
    # sudo yum remove unixODBC-utf16 unixODBC-utf16-devel #to avoid conflicts
    # sudo ACCEPT_EULA=Y yum install -y msodbcsql17
    
    # sudo ACCEPT_EULA=Y yum install -y mssql-tools
    # echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
    # source ~/.bashrc
    
    # sudo yum install -y unixODBC-devel
    

    as MS advised here, then restart of apache and I finally got connected ! :)

    Conclusion:

    Double check versions of drivers to be sure that they can work together. As it turned out - it is very important.