Introduction
After Microsoft ODBC Driver version 17.3.1.1, we will able to use the system-assigned and user-assigned identities (Using Azure Active Directory with the ODBC Driver - ODBC Driver for SQL Server | Microsoft Learn) to access the Azure SQL Server
Microsoft ODBC driver version in Azure Web App for PHP 8.1~ in Linux is after this version. We could run below command to check:
- odbcinst -j
- cat /etc/odbcinst.ini
The result could see we have both ODBC 17 and ODBC 18 driver. And both version is greater than the 17.3.1.1. So it shall support to use the system-assigned managed identity to access the Azure SQL database
Step by Step Setup
Prerequisites:
- Make sure Azure SQL database can use "Active Directory authentication" to login DB since we need the run the database commands to grant the permissions for Azure App Service
- Make sure Microsoft ODBC Driver version is after 17.3.1.1. Could follow the command in the above article content to check
Instruction:
1. Enable the system-assigned managed identity for the Azure App Service
2. Open the Azure SQL database resource, click "Query editor (preview)" and use "Active Directory authentication" to login
3. In the Editor, please run the following databased command: *Remember to replace the <app-name> with the target app service name *If it's a deployment slot, use <app-name>/slots/<slot-name> instead of <app-name>
CREATE USER [<app-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<app-name>];
ALTER ROLE db_datawriter ADD MEMBER [<app-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<app-name>];
GO
4. In Azure App Service application code, please follow the sample code is this document (Azure Active Directory - PHP drivers for SQL Server | Microsoft Learn) to connect to the target Azure SQL database. *Remember to replace the "$azureServer" and "$azureDatabase" parameter.
<?php
$azureServer = 'myazureserver.database.windows.net';
$azureDatabase = 'myazuredatabase';
$connectionInfo = array('Database'=>$azureDatabase,
'Authentication'=>'ActiveDirectoryMsi');
$conn = sqlsrv_connect($azureServer, $connectionInfo);
if ($conn === false) {
echo "Could not connect with Authentication=ActiveDirectoryMsi (system-assigned).\n";
print_r(sqlsrv_errors());
} else {
echo "Connected successfully with Authentication=ActiveDirectoryMsi (system-assigned).\n";
$tsql = "SELECT @@Version AS SQL_VERSION";
$stmt = sqlsrv_query($conn, $tsql);
if ($stmt === false) {
echo "Failed to run the simple query (system-assigned).\n";
print_r(sqlsrv_errors());
} else {
while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
echo $row['SQL_VERSION'] . PHP_EOL;
}
sqlsrv_free_stmt($stmt);
}
sqlsrv_close($conn);
}
?>
5. Now the connection would be successful without any error. In my lab, I have 2 records in the "Person" table and my application could use the system-assigned managed identity to connect to the Azure SQL database and query the data out successfully
- Output:
Reference:
- Tutorial: Access Azure databases with managed identity - Azure App Service | Microsoft Learn
- Azure Active Directory - PHP drivers for SQL Server | Microsoft Learn