How to access Azure SQL database with managed identity in PHP in App Service

Posted on by — No Comments ↓

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.