topic Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL in Data Warehouse

Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

yulione — Tue, 11 Mar 2025 09:07:18 GMT

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' => 'xxxxx@xxxxxx.com',
        '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 !

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

lbendlin — Tue, 11 Mar 2025 13:11:22 GMT

SSMS on the same machine works?

Are you using encrypted connections?

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

yulione — Tue, 11 Mar 2025 13:25:21 GMT

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

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

AndyDDC — Tue, 11 Mar 2025 22:21:23 GMT

Hi yulione can you please consult these 2 MS docs pages and see if the PHP / Doctrine DBAL process supports the currenyl methods for connecting to Fabric Warehouse:

Connectivity to data warehousing - Microsoft Fabric | Microsoft Learn

Microsoft Entra Authentication in Fabric Data Warehouse - Microsoft Fabric | Microsoft Learn

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

yulione — Wed, 12 Mar 2025 08:06:08 GMT

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

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

lbendlin — Wed, 12 Mar 2025 11:29:02 GMT

If you have a Pro license you can open a Pro ticket at https://admin.powerplatform.microsoft.com/newsupportticket/powerbi
Otherwise you can raise an issue at https://community.fabric.microsoft.com/t5/Issues/idb-p/Issues .

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

v-saisrao-msft — Thu, 13 Mar 2025 06:33:53 GMT

Hi yulione,
Thank you for reaching out to the Microsoft Forum Community.

Sorry for the inconvenience caused. As suggested by lbendlin, please raise a support ticket. To create a support ticket for Fabric and Power BI, refer to the steps outlined in the following guide:

How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

Thank you.

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

yulione — Thu, 13 Mar 2025 08:14:55 GMT

Thank you all for your time and support.

As suggested I will open a ticket.

If possible I will try to post solution in this topic.

Re: Query Microsoft Fabrics Warehouse using PHP / Doctrine DBAL

yulione — Wed, 16 Apr 2025 10:35:48 GMT

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' => xxx@xxx.com',

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