Connection Issue with Microsoft Fabric Data Warehouse using MSODBC 18, sqlsrv 5.12.0, and pdo_sqlsrv 5.12.0

[salih-applab]

PHP version
PHP 8.1.12

PHP SQLSRV or PDO_SQLSRV version
sqlsrv 5.12.0
pdo_sqlsrv 5.12.0

Microsoft ODBC Driver version
Microsoft ODBC Driver 18 for SQL Server

SQL Server version
Microsoft Fabric Data Warehouse (SQL analytics endpoint)

Client operating system
Ubuntu 22.04 LTS

Table schema
Not applicable (issue occurs during connection attempt, no specific table involved).

Problem description
When attempting to connect to a Microsoft Fabric Data Warehouse using the sqlsrv or pdo_sqlsrv drivers with Microsoft ODBC Driver 18, the connection fails with an error indicating invalid authentication or connection timeout. The issue occurs with both Microsoft Entra ID (formerly Azure Active Directory) username/password authentication and service principal authentication using client_id, client_secret, and tenant_id (ActiveDirectoryServicePrincipal). The connection string uses the SQL analytics endpoint and port 1433, as specified in Microsoft’s documentation. The issue persists despite following Microsoft’s guidelines for ODBC connections and ensuring correct endpoint details.

Expected behavior and actual behavior
Expected behavior: The connection should succeed, allowing queries to be executed against the Fabric Data Warehouse using either sqlsrv or pdo_sqlsrv drivers with both username/password and service principal authentication methods.
Actual behavior: The connection attempt fails with errors such as:

• [Microsoft][ODBC Driver 18 for SQL Server][SQL Server]Login failed due to invalid authentication methods.
• Or a timeout error: [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68.

Repro code or steps to reproduce

1. Install Microsoft ODBC Driver 18 for SQL Server on Ubuntu 22.04 following Microsoft’s official instructions.
2. Install PHP 8.1.12 and the sqlsrv and pdo_sqlsrv drivers (version 5.12.0) via PECL.
3. Use the following sample PHP code to attempt a connection to the Fabric Data Warehouse:

Username/Password Authentication (ActiveDirectoryPassword):

<?php
$serverName = "your-workspace-name.datawarehouse.fabric.microsoft.com,1433";
$database = "your-warehouse-name";
$connectionOptions = [
    "Database" => $database,
    "Authentication" => "ActiveDirectoryPassword",
    "UID" => "user@domain.com",
    "PWD" => "user-password",
    "Encrypt" => "yes",
    "TrustServerCertificate" => "no"
];

try {
    $conn = sqlsrv_connect($serverName, $connectionOptions);
    if ($conn === false) {
        die(print_r(sqlsrv_errors(), true));
    }
    echo "Connection successful!";
    sqlsrv_close($conn);
} catch (Exception $e) {
    echo "Error: " . $e->getMessage();
}
?>

4. Alternatively, for PDO_SQLSRV with username/password:

<?php
$serverName = "your-workspace-name.datawarehouse.fabric.microsoft.com,1433";
$database = "your-warehouse-name";
$dsn = "sqlsrv:Server=$serverName;Database=$database;Authentication=ActiveDirectoryPassword;Encrypt=yes;TrustServerCertificate=no";

try {
    $conn = new PDO($dsn, "user@domain.com", "user-password");
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

5. For PDO_SQLSRV with service principal:

<?php
$serverName = "your-workspace-name.datawarehouse.fabric.microsoft.com,1433";
$database = "your-warehouse-name";
$dsn = "sqlsrv:Server=$serverName;Database=$database;Authentication=ActiveDirectoryServicePrincipal;Encrypt=yes;TrustServerCertificate=no";

try {
    $conn = new PDO($dsn, "your-client-id", "your-client-secret", ["TenantId" => "your-tenant-id"]);
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    echo "Connection successful!";
} catch (PDOException $e) {
    echo "Connection failed: " . $e->getMessage();
}
?>

6. Run the scripts. The connection attempts fail with the errors mentioned above for both authentication methods.

Additional notes

• The SQL endpoint and database name are correctly copied from the Microsoft Fabric workspace.
• The user has contributor permissions on the workspace, and the service principal has necessary permissions (e.g., SQL Endpoint Connect).
• Firewall settings allow TCP port 1433, and Power BI service tags are enabled.
• The same connection string works with Python’s pyodbc library using ODBC Driver 18 for both ActiveDirectoryPassword and ActiveDirectoryServicePrincipal authentication, suggesting the issue is specific to the PHP drivers.
• Reference: Microsoft Fabric connectivity documentation indicates ODBC 18 is supported, and Entra ID authentication (both username/password and service principal) is required.

Please advise on any known issues with sqlsrv/pdo_sqlsrv 5.12.0 and Fabric Data Warehouse for either authentication method or suggest a workaround for establishing a successful connection.

[pawel-omniaz]

@salih-applab Hi, did you managed to solve it?