Connecting to mssql from windows 10 apache/php/cakephp3

Need Help
1

I am trying to connect to an existing mssql server on my network. I have successfully installed the php mssql extensions. At this point cake is giving me this error message:

CakePHP is NOT able to connect to the database.

Connection to
database could not be established: SQLSTATE[IMSSP]: This extension
requires the Microsoft ODBC Driver 11 for SQL Server to communicate with
SQL Server. Access the following URL to download the ODBC Driver 11 for
SQL Server for x86: System requirements - PHP drivers for SQL Server | Microsoft Learn

However this driver does not install on Windows 10 (“Installation of this product failed because it is not supported on this operating system”).

After some digging around, I found a preview version 13 of the ODBC driver that does work on windows 10 (https://www.microsoft.com/en-us/download/details.aspx?id=50420). It installs OK, but the cake error doesn’t change - I assume it doesn’t recognize it and is looking for version 11.

Is there anything I can do to get this to work? thanks

2

Hey Benzo, which version of SQL Server and PHP are you using? Assuming SQL Server 2008 or higher being accessed by PHP 5.6 or higher you can use the link provided below instead of the invalid one Microsoft recommends. I came across this exact issue two years ago when I first started working with SQL Server on PHP.

https://www.microsoft.com/en-us/download/details.aspx?id=36434

Let me know if you still need help with setting this up. I’ve been using CakePHP in Windows for a while now.

3

Hi TTorres, thanks for the reply. I’m afraid the download link you posted also fails to install on Windows 10, it seems like Microsoft just checks the OS and stops the installation immediately with an error message if it’s win10.

For anyone else with this problem, I did find a solution:

  1. install apache 2.4
  2. install php7
  3. Download and place into php7/ext the recently released Microsoft Drivers 4.0 for PHP for SQL Server:
    https://blogs.msdn.microsoft.com/sqlphp/2016/06/13/release-candidate-of-microsoft-drivers-4-0-for-php-for-sql-server-released/
  4. add the extension= line in php.ini for the thread safe (ts) version of the driver
  5. install the Microsoft ODBC 13 Preview:
    microsoft.com/en-us/download/details.aspx?id=50420
  6. test pdo connection in php
  7. try in cakephp (setup config/app.php database connection)

I don’t have the code with me, but I’ll try to post samples for 6 and 7 tomorrow.

4

  1. test pdo connection in php

    <?php

    try {
    $pdo = new PDO( “sqlsrv:server=$servername;Database=$db”, $username, $pw);
    } catch( PDOException $e ) {
    die( “Error connecting to SQL Server:” . $e->getMessage());
    }

    $stmt = $pdo->query(‘SELECT fieldname FROM tablename’);
    while ($row = $stmt->fetch())
    {
    echo $row[‘fieldname’] . “
    ”;
    }
    ?>

5

  1. cakephp DataSource Config (app.php)

    ‘Datasources’ => [
    ‘default’ => [
    ‘className’ => ‘Cake\Database\Connection’,
    ‘driver’ => ‘Cake\Database\Driver\SqlServer’,
    ‘persistent’ => false,
    ‘host’ => ‘hostname’,
    /**
    * CakePHP will use the default DB port based on the driver selected
    * MySQL on MAMP uses port 8889, MAMP users will want to uncomment
    * the following line and set the port accordingly
    */
    //‘port’ => ‘non_standard_port_number’,
    ‘username’ => ‘user’,
    ‘password’ => ‘pw’,
    ‘database’ => ‘db’,
    ‘encoding’ => PDO::SQLSRV_ENCODING_UTF8,
    ‘timezone’ => ‘UTC’,
    ‘flags’ => [],
    ‘cacheMetadata’ => true,
    ‘log’ => false,