How to access Azure SQL database with managed identity in PHP in App Service
Published May 08 2024 06:58 PM 1,012 Views
Microsoft

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

Joe_Chen_0-1714621249949.png

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

Joe_Chen_1-1714621249953.png

 

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

Joe_Chen_2-1714621249956.png

2. Open the Azure SQL database resource, click "Query editor (preview)" and use "Active Directory authentication" to login

Joe_Chen_3-1714621249958.png

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

 

Joe_Chen_4-1714621249961.png

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:

Joe_Chen_5-1714621249963.png

Reference:

- Tutorial: Access Azure databases with managed identity - Azure App Service | Microsoft Learn

- Azure Active Directory - PHP drivers for SQL Server | Microsoft Learn

Co-Authors
Version history
Last update:
‎May 01 2024 08:49 PM
Updated by: