Forum Discussion

yulione's avatar
yulione
Frequent Visitor
1 year ago
Solved

Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

My company is moving all our data to Microsoft Fabrics, which is great, however we have some PHP app who needs to access those datas.

All those apps are using Doctrine DBAL, and we can't find how to get the connection.

PHP 8.3.16 Doctrine DBAL : 4.2.2 ODBC DRIVER : 18 sqlsrv et pdo_sqlsrv drivers : 5.12

 

I'm using the following code to get the connection :

    $connectionParams = [
        'dbname' => 'My_DB_Name',
        'user' => '[email protected]',
        'password' => 'MyPassword',
        'host' => 'xxxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com',
        'driver' => 'pdo_sqlsrv', //doesn't work with sqlsrv either
        'port' => 1433,
        'driverOptions' => [ // tried few additional parameters, but are not accepeted or working
            //'Authentification' => 'ActiveDirectoryPassword',
            //"Trustedconnection"=>"true"
            //'TrustServerCertificate' => true
        ]
    ];
    $this->conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

And this code to test

    try{
        $a = self::instance()->fetchAssociative('SELECT * FROM Production_Data');
            //self:instance() return the connection from above
        echo('OK<br>');
        Toolbox::var_dump($a,false,0);
    }catch(\Throwable $e){
        echo($e->getMessage());
    }

As a results, I have a blank page. No results at all, not even an error.... and 'OK' doesn't show up.... We also tried a classic PDO connection, but doesn't work either. We an reach and query the warehouse correctly using SQL Server Management studio though.

Thank you for your help !

  • Hey again. 

     

    After several weeks of discussion with Microsoft, it appear that this is because Warehouse doesn't support Multiple Active Result Sets (MARS). Setting MultipleActiveResultSets=0 in option resolve the problem. 

     

    so, the final method for me was :  

     

            $connectionParams = [
                'dbname' => 'my_DBname',
                'user' => [email protected]',
                'password' => 'mypassword',
                'host' => 'xxxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com',
                'driver' => 'pdo_sqlsrv',
                'port' => 1433,
                'driverOptions' => [
                    'Authentication' => 'ActiveDirectoryPassword',
                    'MultipleActiveResultSets' => 0,
                    'Encrypt' => 1,
                    'TrustServerCertificate' => true
                ]
            ];
            $this->conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);

     

8 Replies

  • SSMS on the same machine works?

     

    Are you using encrypted connections?

    • yulione's avatar
      yulione
      Frequent Visitor

      Hi Ibendlin, 

       

      I'm using a laragon setup with encryption to make this test, and Yes SSMS on the same machine is working just fine

       

    • yulione's avatar
      yulione
      Frequent Visitor

      HI AndyDDC 

      Thank you for your help ^^

       

      There is nothing specifically written in Doctrine documentation about support of MFA method. However, doctrine supports ODBC connexion through 2 PHP drivers : sqlsrv and pdo_sqlsrv (doc here) and we are using them for a while now. As ODBC connexion is supported by fabrics, I assumed (maybe I'm wrong) that it should work too. 

      Microsoft doc says that we should use ODBC 18 or greater and sqlsrv and pdo_sqlsrv 5.11 or greater, which I have.  (PHP is 8.3 and i'm using 64-bit php_sqlsrv_83_ts.dll and 64-bit php_pdo_sqlsrv_83_ts.dll found from Microsoft dedicated page)

      With this in mind, a classic PDO connexion (not using doctrine) gives the same results and same issue.

      As for SSMS, maybe there is some instruction to give to the driver to says this is a entra MFA connexion ? I haven't seen anything related to that in driver doc.

       

      I've spent the last 2-3 month to try to figure it out... What I don't understant though is there is absolutly no error anywhere, even in system logs or anywhere else... 

  • yulione's avatar
    yulione
    Frequent Visitor

    Hey again. 

     

    After several weeks of discussion with Microsoft, it appear that this is because Warehouse doesn't support Multiple Active Result Sets (MARS). Setting MultipleActiveResultSets=0 in option resolve the problem. 

     

    so, the final method for me was :  

     

            $connectionParams = [
                'dbname' => 'my_DBname',
                'user' => [email protected]',
                'password' => 'mypassword',
                'host' => 'xxxxxxxxxxxxxx.datawarehouse.fabric.microsoft.com',
                'driver' => 'pdo_sqlsrv',
                'port' => 1433,
                'driverOptions' => [
                    'Authentication' => 'ActiveDirectoryPassword',
                    'MultipleActiveResultSets' => 0,
                    'Encrypt' => 1,
                    'TrustServerCertificate' => true
                ]
            ];
            $this->conn = \Doctrine\DBAL\DriverManager::getConnection($connectionParams);