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...
data warehouse
  • yulione's avatar
    yulione
    1 year ago

    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);

     

yulione's avatar
yulione
Frequent Visitor
1 year ago

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...