An existing connection in the connection pool is reused unexpectedly even though it has a different access token.

[rikat-ms]

Please check the FAQ (frequently-asked questions) first. If you have other questions or something to report, please address the following (skipping questions might delay our responses):

PHP version
7.4.13 (I also confirmed with 7.1)

PHP SQLSRV or PDO_SQLSRV version
5.8

Microsoft ODBC Driver version
17.8

SQL Server version
Azure SQL Database

Client operating system
Windows Server 2016, Windows 10

Table schema
N/A

Problem description
After you open and close a connection to Azure SQL Database using the accessToken for a User-assigned managed ID, the second connection reuses the existing connection from the connection pool unexpectedly even though the accessToken of the new connection is different from the connection in the pool.

• This issue occurs when connection pooling is enabled.
• This issue doesn't occur if we call the same ODBC API in C++ application.
• As far as I saw the detail (Debugging), it looks that one of settings for the second connection (lpdbc in ODBC32) has the same value as the first connection and it causes the first connection to be reusable in ODBC32. As the setting comes from PHP, there might be something wrong, but I haven't determined, yet.

Expected behavior and actual behavior

• Expected: If the accessToken of a new connection is different from any connections in the connection pool, a new connection must be opened.

• Actual: Although the acceesToken of a new connection is different from any connections in the connection pool, existing connection is reused.

Repro code or steps to reproduce

1. Create Azure SQL Server and SQL Database.

2. Create a service principal in Azure AD.

3. Configure Azure Active Directory admin in SQL Server to enable Azure AD authentication with the service principal. https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?view=azuresql&tabs=azure-powershell

4. Create 2 User-assigned Managed ID (UMI).

5. Create 2 contained-database user in Azure SQL Database for UMI like this. ===== Install-Module MSAL.PS Import-Module MSAL.PS $tenantId = "Place your tenant ID" $clientId = "Place your service principal's id" $clientSecret = "Place your service principal's client secret" $scopes = "https://database.windows.net/.default" $result = Get-MsalToken -RedirectUri $uri -ClientId $clientId -ClientSecret (ConvertTo-SecureString $clientSecret -AsPlainText -Force) -TenantId $tenantId -Scopes $scopes $Tok = $result.AccessToken $SQLServerName = "Place your SQL Server name" $DatabaseName = "Place your database name" $conn = New-Object System.Data.SqlClient.SQLConnection $conn.ConnectionString = "Data Source=$SQLServerName.database.windows.net;Initial Catalog=$DatabaseName;Connect Timeout=30" $conn.AccessToken = $Tok $conn.Open() $ddlstmt = "CREATE USER [Place a new user name] FROM EXTERNAL PROVIDER;" $command = New-Object -TypeName System.Data.SqlClient.SqlCommand($ddlstmt, $conn) $command.ExecuteNonQuery() $conn.Close() =====

6. Create a Azure VM.

7. Install PHP, Microsoft ODBC Driver for SQL Server and Microsoft Drivers for PHP for SQL Server.

8. Configure IIS which can run PHP.

9. Get access token for each user-assigned managed id.

10. Create a .php file and place it under wwwroot folder. ===== `<?php function connectsql($pooling, $accToken) { $azureAdDatabase = "Place your database name"; $azureAdServer = "Place your SQL Server name"; try { $connectionInfo = "Database = $azureAdDatabase; AccessToken = $accToken; ConnectionPooling = $pooling;"; $conn = new PDO("sqlsrv:server = $azureAdServer; $connectionInfo"); echo "Connected successfully with Azure AD Access Token\n"; $tsql = "SELECT USER_NAME()"; try { $stmt = $conn->query($tsql); $result = $stmt->fetchall(PDO::FETCH_ASSOC); unset($stmt); } catch (PDOException $e) { echo "Failed to run the simple query (user-assigned).\n"; } unset($conn);

    } catch (PDOException $e) { echo "Could not connect with Azure AD Access Token.\n"; $result = $e->getMessage(); } return $result; } $token1 = "Place a token for User-assigned Managed ID 1"; $token2 = "Place a token for User-assigned Managed ID 2"; $ret = connectsql("true", $token1); print_r($ret); $ret = connectsql("true", $token2); print_r($ret); ?>` =====

[absci]

Thanks for the info, we'll look into it.