Error requesting access token while Azure webapps (php) connect to Azure SQL server by UMI

[PHPandAZURE]

PHP version
php:8.2
PHP SQLSRV or PDO_SQLSRV version
5.11.1

Microsoft ODBC Driver version
ODBC Driver 17 for SQL Server
ODBC Driver 18 for SQL Server

Webapps Server
nginx Linux

SQL Server version
Microsoft SQL Azure (RTM) - 12.0.2000.8 Dec 6 2023 08:32:07 Copyright (C) 2022 Microsoft Corporation

Is your feature request related to a problem? Please describe.

while using the below test php Program connect to sql server,

<?php
    $serverName = 'xxxx.database.windows.net';
    $database = 'xxx';
    $umi = 'xxxxx';
    $conInfo=array('Database'=>$database,'UID' => $umi,'Authentication' => 'ActiveDirectoryMsi','Encrypt'=>'yes','CharacterSet' => 'UTF-8');
    $conn = sqlsrv_connect($serverName, $conInfo);

    if (!$conn){
        print_r(sqlsrv_errors());
        echo ("can not connect db");
    }
    $tsql= "SELECT TOP 20 COMPANY_CD 
         FROM [T_EXA_T_SESSION] pc";
    $getResults= sqlsrv_query($conn, $tsql);
    echo ("Reading data from table1" . PHP_EOL);
    if ($getResults == FALSE)
        echo (sqlsrv_errors());
    while ($row = sqlsrv_fetch_array($getResults, SQLSRV_FETCH_ASSOC)) {
     echo ($row['CategoryName'] . " " . $row['ProductName'] . PHP_EOL);
    }
    sqlsrv_free_stmt($getResults);
?>

I find below error

[Microsoft][ODBC Driver 17 for [Microsoft] Server]Login timeout expired 
[message] => [Microsoft][ODBC Driver 17 for [Microsoft] Server]Login timeout expired ) [1] => Array ( [0] => FA004 [
[Microsoft]STATE] => FA004 [1] => 0 [code] => 0 [2] => 
[Microsoft][ODBC Driver 17 for 
[Microsoft] Server][
[Microsoft] Server]Failed to authenticate the user 'xxxx' in Active Directory (Authentication option is 'ActiveDirectoryMSI'). Error code 0xA190; state 41360 
{"statusCode":400,"message":"Unable to load the proper Managed Identity.","correlationId":"xxx"}
{"statusCode":400,"message":"Unable to load the proper Managed Identity.","correlationId":"xxx"}
{"statusCode":400,"message":" [message] => [Microsoft][ODBC Driver 17 for  [Microsoft] Server][
[Microsoft] Server]Failed to authenticate the user 'xxxx' in Active Directory (Authentication option is 'ActiveDirectoryMSI'). Error code 0xA190; state 41360 
{"statusCode":400,"message":"Unable to load the proper Managed Identity.","correlationId":"xxxx"}
{"statusCode":400,"message":"Unable to load the proper Managed Identity.","correlationId":"xxxx"}
{"statusCode":400,"message":" ) [2] => Array ( [0] => CE275 [ [Microsoft]STATE] => CE275 [1] => 0 [code] => 0 [2] => [Microsoft][ODBC Driver 17 for 
[Microsoft] Server][ [Microsoft] Server]Error requesting access token, HTTP status 400, expected 200 [message] => [Microsoft][ODBC Driver 17 for 
[Microsoft] Server][ [Microsoft] Server]Error requesting access token, HTTP status 400, expected 200 ) [3] => Array ( [0] => 08001 [
[Microsoft]STATE] => 08001 [1] => 258 [code] => 258 [2] => [Microsoft][ODBC Driver 17 for  [Microsoft] Server]TCP Provider: Timeout error [258]. 
[message] => [Microsoft][ODBC Driver 17 for  [Microsoft] Server]TCP Provider: Timeout error [258]. ) [4] => Array ( [0] => 08001 
[[Microsoft]STATE] => 08001 [1] => 258 [code] => 258 [2] => 
[Microsoft][ODBC Driver 17 for [Microsoft] Server]Unable to complete login process due to delay in login response 
[message] => [Microsoft][ODBC Driver 17 for [Microsoft] Server]Unable to complete login process due to delay in login response ) )

Describe alternatives you've considered

1. Try Re-create a webapps on Azure (not work)
2. Try connect to Sql server with PHP program by UMI on local(still error)
3. Try connect to Sql server with ASP.net by UMI on local(Success)
4. Try connect to Sql server with PHP program by user/password on Auzre (Success)

Now I am going to try below

1. create a new UMI
2. Update the PHP test PGM , get token first then use token to connect DB
   if it works, I will feedback also

[v-maximko]

The log mentions Driver 17, have you tried it with the latest Driver 18? There were some potentially related fixes there

[PHPandAZURE]

thank you for your confrimation
I update the php PGM (using UMI to get token first and then using Token to connect DB) it works
This is my new code
I think this issue can be closed

<?php

echo "test get token";

//get environement variables
$identityEndpoint = getenv("IDENTITY_ENDPOINT");
echo 'IDENTITY_ENDPOINT>>';
echo $identityEndpoint;
$identityHeader = getenv("IDENTITY_HEADER");
echo 'IDENTITY_HEADER>>';
echo $identityHeader;
$tokenAuthURI = "$identityEndpoint?resource=https://database.windows.net&api-version=2019-08-01&client_id=[UMI]";

echo 'tokenAuthURI>>';
echo $tokenAuthURI;


//Create a stream
$opts = array(
'http'=> array(
'method'=>"GET",
'header'=>"X-IDENTITY-HEADER: $identityHeader"
)
);

$context = stream_context_create($opts);

echo 'context>>';
echo $context;

// Open the file using the HTTP headers set above
$file = file_get_contents($tokenAuthURI, false, $context);
echo 'file>>';
echo $file;

if($file)
{
$array = json_decode($file, true);
$accToken = $array['access_token'];

echo 'array>>';
echo $array;

echo 'accToken>>';
echo $accToken;
}

$azureServer = 'xxxx';
$azureDatabase = 'xxx';

$connectionInfo = array("Database"=>$azureDatabase, "AccessToken"=>$accToken);
$conn = sqlsrv_connect($azureServer, $connectionInfo);
if ($conn === false) {
echo "Could not connect with Azure AD Access Token.\n";
print_r(sqlsrv_errors());
} else {
echo "Connected successfully with Azure AD Access Token.\n";

    $tsql = "SELECT @@Version AS SQL_VERSION";
    $stmt = sqlsrv_query($conn, $tsql);
    if ($stmt === false) {
        echo "Failed to run the simple query .\n";
        print_r(sqlsrv_errors());
    } else {
    	       echo "Query successfully>>>";
        while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
            echo $row['SQL_VERSION'] . PHP_EOL;
        }

        sqlsrv_free_stmt($stmt);
    }

sqlsrv_close($conn);
}


?>

[PHPandAZURE]

The log mentions Driver 17, have you tried it with the latest Driver 18? There were some potentially related fixes there

Thank you for your suggestion!!
We define the ODBC Driver to 18 and it is effective.

    global $dbConn;
    $dbConn = false;
    $serverName = getenv('DB_SERVER_NAME');
    $database = getenv('DB_DATABASE');
    $umi = getenv('DB_UMI');
    $odbcDriver = getenv('ODBC_DRIVER');
    $conInfo=array('Driver'=>$odbcDriver,'Database'=>$database,'UID' => $umi,'Authentication' => 'ActiveDirectoryMsi','Encrypt'=>'yes','CharacterSet' => 'UTF-8');
    $dbConn = sqlsrv_connect($serverName, $conInfo);

    if (!$dbConn) {
        error_log('['.date('Y-m-d H:i:s', time()).'][SysErr] '.'can not connect db.'.PHP_EOL);
        exit;
    }

    return true;

[RamirezSzabo]

Updating to 18 worked for me too. Thank you !