App Service (PHP) access to Azure SQL database with managed identity
You can connect to your Azure SQL Database or Azure Database for MySQL using a Managed Identity in PHP. This approach removes the necessity of storing usernames and passwords in your code. Authentication is managed by Entra ID, previously known as Azure Active Directory (AAD).
Enable Managed Identity for Your App Service
If using Azure App Service (Web App): Go to Azure Portal → Your App Service → Identity → Enable System-assigned identity.
Assign Database Permissions
After enabling Managed Identity, you need to grant it access to your Azure database.
For Azure SQL Database, connect to your SQL Server using Azure Data Studio or SQL Server Management Studio (SSMS).
Run the following SQL commands to create an AAD user and assign roles:
1-- Create the Managed Identity as an Azure AD user
2CREATE USER [your-managed-identity-name] FROM EXTERNAL PROVIDER;
3
4-- Grant permissions (adjust based on needs)
5ALTER ROLE db_datareader ADD MEMBER [your-managed-identity-name];
6ALTER ROLE db_datawriter ADD MEMBER [your-managed-identity-name];
7ALTER ROLE db_owner ADD MEMBER [your-managed-identity-name]; -- Only if full access is needed
To dispaly external providers created:
1--SID to OBJECTID
2SELECT
3 DP.name
4 ,DP.principal_id
5 ,DP.type
6 ,DP.type_desc
7 ,DP.SID
8 ,OBJECTID = CONVERT(uniqueidentifier, DP.SID)
9FROM SYS.database_principals DP
10WHERE DP.type IN ('S','X','E')
Replace your-managed-identity-name with the actual name of your managed identity.
Connect to Azure SQL Database with PDO
PDO (PHP Data Objects) is a database access layer in PHP that provides a uniform and secure way to interact with different databases (MySQL, PostgreSQL, SQL Server, SQLite, etc.).
1 $azureServer = 'myazureserver.database.windows.net';
2 $azureDatabase = 'myazuredatabase';
3 $connectionInfo = array('Database'=>$azureDatabase,
4 'Authentication'=>'ActiveDirectoryMsi');
5 $conn = sqlsrv_connect($azureServer, $connectionInfo);
6
7 if ($conn === false) {
8 echo "Could not connect with Authentication=ActiveDirectoryMsi (system-assigned).\n";
9 print_r(sqlsrv_errors());
10 } else {
11 echo "Connected successfully with Authentication=ActiveDirectoryMsi (system-assigned).\n";
12
13 $tsql = "SELECT @@Version AS SQL_VERSION";
14 $stmt = sqlsrv_query($conn, $tsql);
15 if ($stmt === false) {
16 echo "Failed to run the simple query (system-assigned).\n";
17 print_r(sqlsrv_errors());
18 } else {
19 while ($row = sqlsrv_fetch_array($stmt, SQLSRV_FETCH_ASSOC)) {
20 echo $row['SQL_VERSION'] . PHP_EOL;
21 }
22
23 sqlsrv_free_stmt($stmt);
24 }
25 sqlsrv_close($conn);
26 }
To conclude
Using a managed identity to connect your frontend application (WebApp) to your backend SQL database eliminates the need for hardcoded usernames and passwords. This approach enhances security by leveraging Azure's identity management, ensuring that credentials are automatically managed and rotated.
🔥 Advantages of Using Managed Identity
✅ No hardcoded credentials in code.
✅ Automatically rotates tokens for security.
✅ Works across Azure services (VMs, App Service, Functions,etc).