Tutorial: Connect to Azure databases from App Service without secrets using a managed identity
Article
App Service provides a highly scalable, self-patching web hosting service in Azure. It also provides a managed identity for your app, which is a turn-key solution for securing access to Azure databases, including:
Managed identities in App Service make your app more secure by eliminating secrets from your app, such as credentials in the connection strings. This tutorial shows you how to connect to the above-mentioned databases from App Service using managed identities.
What you will learn:
Configure a Microsoft Entra user as an administrator for your Azure database.
Connect to your database as the Microsoft Entra user.
Configure a system-assigned or user-assigned managed identity for an App Service app.
Grant database access to the managed identity.
Connect to the Azure database from your code (.NET Framework 4.8, .NET 6, Node.js, Python, Java) using a managed identity.
Connect to the Azure database from your development environment using the Microsoft Entra user.
If you don't have an Azure subscription, create a trial account before you begin.
Prerequisites
Create an app in App Service based on .NET, Node.js, Python, or Java.
Create a database server with Azure SQL Database, Azure Database for MySQL, or Azure Database for PostgreSQL.
You should be familiar with the standard connectivity pattern (with username and password) and be able to connect successfully from your App Service app to your database of choice.
Prepare your environment for the Azure CLI.
You can use the local Azure CLI.
If you prefer, install the Azure CLI to run CLI reference commands.
Sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For other sign-in options, see Sign in with the Azure CLI.
When you're prompted, install the Azure CLI extension on first use. For more information about extensions, see Use extensions with the Azure CLI.
Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.
1. Grant database access to Microsoft Entra user
First, enable Microsoft Entra authentication to the Azure database by assigning a Microsoft Entra user as the administrator of the server. For the scenario in the tutorial, you'll use this user to connect to your Azure database from the local development environment. Later, you set up the managed identity for your App Service app to connect from within Azure.
Note
This user is different from the Microsoft account you used to sign up for your Azure subscription. It must be a user that you created, imported, synced, or invited into Microsoft Entra ID. For more information on allowed Microsoft Entra users, see Microsoft Entra features and limitations in SQL Database.
Add this Microsoft Entra user as an Active Directory administrator using az sql server ad-admin create command in the Azure CLI. In the following command, replace <group-name> and <server-name> with your own parameters.
az sql server ad-admin create --resource-group <group-name> --server-name <server-name> --display-name ADMIN --object-id $azureaduser
Add this Microsoft Entra user as an Active Directory administrator using az mysql server ad-admin create command in the Azure CLI. In the following command, replace <group-name> and <server-name> with your own parameters.
az mysql server ad-admin create --resource-group <group-name> --server-name <server-name> --display-name <user-principal-name> --object-id $azureaduser
Note
The command is currently unavailable for Azure Database for MySQL Flexible Server.
Add this Microsoft Entra user as an Active Directory administrator using az postgres server ad-admin create command in the Azure CLI. In the following command, replace <group-name> and <server-name> with your own parameters.
az postgres server ad-admin create --resource-group <group-name> --server-name <server-name> --display-name <user-principal-name> --object-id $azureaduser
Note
The command is currently unavailable for Azure Database for PostgreSQL Flexible Server.
2. Configure managed identity for app
Next, you configure your App Service app to connect to SQL Database with a managed identity.
Enable a managed identity for your App Service app with the az webapp identity assign command in the Azure CLI. In the following command, replace <app-name>.
az webapp identity assign --resource-group <group-name> --name <app-name>
az webapp identity assign --resource-group <group-name> --name <app-name> --output tsv --query principalId
az ad sp show --id <output-from-previous-command> --output tsv --query appId
The output of az ad sp show is the application ID of the system-assigned identity. You'll need it later.
az webapp identity assign --resource-group <group-name> --name <app-name> --output tsv --query principalId
az ad sp show --id <output-from-previous-command> --output tsv --query appId
The output of az ad sp show is the application ID of the system-assigned identity. You'll need it later.
# Create a user-assigned identity and get its client ID
az identity create --name <identity-name> --resource-group <group-name> --output tsv --query "id"
# assign identity to app
az webapp identity assign --resource-group <group-name> --name <app-name> --identities <output-of-previous-command>
# get client ID of identity for later
az webapp identity show --name <identity-name> --resource-group <group-name> --output tsv --query "clientId"
The output of az webapp identity show is the client ID of the user-assigned identity. You'll need it later.
Note
To enable managed identity for a deployment slot, add --slot <slot-name> and use the name of the slot in <slot-name>.
The identity needs to be granted permissions to access the database. In the Azure CLI, sign in to your database with the following command. Replace <server-name> with your server name, <database-name> with the database name your app uses, and <aad-user-name> and <aad-password> with your Azure AD user's credentials from 1. Grant database access to Azure AD user.
# Sign into Azure using the Azure AD user from "1. Grant database access to Azure AD user"
az login --allow-no-subscriptions
# Get access token for MySQL with the Azure AD user
az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken
# Sign into the MySQL server using the token
mysql -h <server-name>.mysql.database.chinacloudapi.cn --user <aad-user-name>@<server-name> --enable-cleartext-plugin --password=<token-output-from-last-command> --ssl
The full username <aad-user-name>@<server-name> looks like admin1@contoso.partner.onmschina.cn@mydbserver1.
# Sign into Azure using the Azure AD user from "1. Grant database access to Azure AD user"
az login --allow-no-subscriptions
# Get access token for PostgreSQL with the Azure AD user
az account get-access-token --resource-type oss-rdbms --output tsv --query accessToken
# Sign into the Postgres server
psql "host=<server-name>.postgres.database.chinacloudapi.cn port=5432 dbname=<database-name> user=<aad-user-name>@<server-name> password=<token-output-from-last-command>"
The full username <aad-user-name>@<server-name> looks like admin1@contoso.partner.onmschina.cn@mydbserver1.
Run the following database commands to grant the permissions your app needs. For example,
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
For a deployment slot, use <app-name>/slots/<slot-name> instead of <app-name>.
CREATE USER [<identity-name>] FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER [<identity-name>];
ALTER ROLE db_datawriter ADD MEMBER [<identity-name>];
ALTER ROLE db_ddladmin ADD MEMBER [<identity-name>];
GO
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '<mysql-user-name>' IDENTIFIED BY '<application-id-of-system-assigned-identity>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO '<mysql-user-name>'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Whatever name you choose for <mysql-user-name>, it's the MySQL user you'll use to connect to the database later from your code in App Service.
SET aad_auth_validate_oids_in_tenant = OFF;
CREATE AADUSER '<mysql-user-name>' IDENTIFIED BY '<client-id-of-user-assigned-identity>';
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, ALTER ON *.* TO '<mysql-user-name>'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
Whatever name you choose for <mysql-user-name>, it's the MySQL user you'll use to connect to the database later from your code in App Service.
SET aad_validate_oids_in_tenant = off;
CREATE ROLE <postgresql-user-name> WITH LOGIN PASSWORD '<application-id-of-system-assigned-identity>' IN ROLE azure_ad_user;
Whatever name you choose for <postgresql-user-name>, it's the PostgreSQL user you'll use to connect to the database later from your code in App Service.
SET aad_validate_oids_in_tenant = off;
CREATE ROLE <postgresql-user-name> WITH LOGIN PASSWORD '<application-id-of-user-assigned-identity>' IN ROLE azure_ad_user;
Whatever name you choose for <postgresql-user-name>, it's the PostgreSQL user you'll use to connect to the database later from your code in App Service.
Get the Azure SQL Database connection string from the environment variable added by Service Connector.
using Microsoft.Data.SqlClient;
// AZURE_SQL_CONNECTIONSTRING should be one of the following:
// For system-assigned managed identity:"Server=tcp:<server-name>.database.chinacloudapi.cn;Database=<database-name>;Authentication=Active Directory Default;TrustServerCertificate=True"
// For user-assigned managed identity: "Server=tcp:<server-name>.database.chinacloudapi.cn;Database=<database-name>;Authentication=Active Directory Default;User Id=<client-id-of-user-assigned-identity>;TrustServerCertificate=True"
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
Get the Azure SQL Database connection configurations from the environment variable added by Service Connector. Uncomment the part of the code snippet for the authentication type you want to use.
import os;
import pyodbc
server = os.getenv('AZURE_SQL_SERVER')
port = os.getenv('AZURE_SQL_PORT')
database = os.getenv('AZURE_SQL_DATABASE')
authentication = os.getenv('AZURE_SQL_AUTHENTICATION') # The value should be 'ActiveDirectoryMsi'
# Uncomment the following lines according to the authentication type.
# For system-assigned managed identity.
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server={server},{port};Database={database};Authentication={authentication};Encrypt=yes;'
# For user-assigned managed identity.
# client_id = os.getenv('AZURE_SQL_USER')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server={server},{port};Database={database};UID={client_id};Authentication={authentication};Encrypt=yes;'
conn = pyodbc.connect(connString)
Get the Azure SQL Database connection configurations from the environment variables added by Service Connector. Uncomment the part of the code snippet for the authentication type you want to use.
Connectivity to the Azure Database for MySQL in your code follows the DefaultAzureCredential pattern for all language stacks. DefaultAzureCredential is flexible enough to adapt to both the development environment and the Azure environment. When running locally, it can retrieve the logged-in Azure user from the environment of your choice (Visual Studio, Visual Studio Code, Azure CLI, or Azure PowerShell). When running in Azure, it retrieves the managed identity. So it's possible to have connectivity to database both at development time and in production. The pattern is as follows:
Instantiate a DefaultAzureCredential from the Azure Identity client library. If you're using a user-assigned identity, specify the client ID of the identity.
Get an access token for Azure Database for MySQL: https://ossrdbms-aad.database.chinacloudapi.cn/.default.
For .NET, get an access token for the managed identity using a client library such as Azure.Identity. Then use the access token as a password to connect to the database. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
using Azure.Core;
using Azure.Identity;
using MySqlConnector;
// Uncomment the following lines according to the authentication type.
// For system-assigned managed identity.
// var credential = new DefaultAzureCredential();
// For user-assigned managed identity.
// var credential = new DefaultAzureCredential(
// new DefaultAzureCredentialOptions
// {
// ManagedIdentityClientId = Environment.GetEnvironmentVariable("AZURE_MYSQL_CLIENTID");
// });
var tokenRequestContext = new TokenRequestContext(
new[] { "https://ossrdbms-aad.database.chinacloudapi.cn/.default" });
AccessToken accessToken = await credential.GetTokenAsync(tokenRequestContext);
// Open a connection to the MySQL server using the access token.
string connectionString =
$"{Environment.GetEnvironmentVariable("AZURE_MYSQL_CONNECTIONSTRING")};Password={accessToken.Token}";
using var connection = new MySqlConnection(connectionString);
Console.WriteLine("Opening connection using access token...");
await connection.OpenAsync();
// do something
Add the following dependencies in your pom.xml file:
Authenticate with an access token from the azure-identity library. Get the connection information from the environment variable added by Service Connector. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
from azure.identity import ManagedIdentityCredential, ClientSecretCredential
import mysql.connector
import os
# Uncomment the following lines according to the authentication type.
# For system-assigned managed identity.
# cred = ManagedIdentityCredential()
# For user-assigned managed identity.
# managed_identity_client_id = os.getenv('AZURE_MYSQL_CLIENTID')
# cred = ManagedIdentityCredential(client_id=managed_identity_client_id)
# acquire token
accessToken = cred.get_token('https://ossrdbms-aad.database.chinacloudapi.cn/.default')
# open connect to Azure MySQL with the access token.
host = os.getenv('AZURE_MYSQL_HOST')
database = os.getenv('AZURE_MYSQL_NAME')
user = os.getenv('AZURE_MYSQL_USER')
password = accessToken.token
cnx = mysql.connector.connect(user=user,
password=password,
host=host,
database=database)
cnx.close()
Get an access token using @azure/identity and the Azure MySQL database information from the environment variables added by Service Connector. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
import { DefaultAzureCredential,ClientSecretCredential } from "@azure/identity";
const mysql = require('mysql2');
// Uncomment the following lines according to the authentication type.
// for system-assigned managed identity
// const credential = new DefaultAzureCredential();
// for user-assigned managed identity
// const clientId = process.env.AZURE_MYSQL_CLIENTID;
// const credential = new DefaultAzureCredential({
// managedIdentityClientId: clientId
// });
// acquire token
var accessToken = await credential.getToken('https://ossrdbms-aad.database.chinacloudapi.cn/.default');
const connection = mysql.createConnection({
host: process.env.AZURE_MYSQL_HOST,
user: process.env.AZURE_MYSQL_USER,
password: accessToken.token,
database: process.env.AZURE_MYSQL_DATABASE,
port: process.env.AZURE_MYSQL_PORT,
ssl: process.env.AZURE_MYSQL_SSL
});
connection.connect((err) => {
if (err) {
console.error('Error connecting to MySQL database: ' + err.stack);
return;
}
console.log('Connected to MySQL database');
});
Connectivity to the Azure Database for PostgreSQL in your code follows the DefaultAzureCredential pattern for all language stacks. DefaultAzureCredential is flexible enough to adapt to both the development environment and the Azure environment. When running locally, it can retrieve the logged-in Azure user from the environment of your choice (Visual Studio, Visual Studio Code, Azure CLI, or Azure PowerShell). When running in Azure, it retrieves the managed identity. So it's possible to have connectivity to database both at development time and in production. The pattern is as follows:
Instantiate a DefaultAzureCredential from the Azure Identity client library. If you're using a user-assigned identity, specify the client ID of the identity.
Get an access token for Azure Database for PostgreSQL: https://ossrdbms-aad.database.chinacloudapi.cn/.default.
For .NET, get an access token for the managed identity using a client library such as Azure.Identity. Then use the access token as a password to connect to the database. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
using Azure.Identity;
using Azure.Core;
using Npgsql;
// Uncomment the following lines according to the authentication type.
// For system-assigned identity.
// var sqlServerTokenProvider = new DefaultAzureCredential();
// For user-assigned identity.
// var sqlServerTokenProvider = new DefaultAzureCredential(
// new DefaultAzureCredentialOptions
// {
// ManagedIdentityClientId = Environment.GetEnvironmentVariable("AZURE_POSTGRESQL_CLIENTID");
// }
// );
// Acquire the access token.
AccessToken accessToken = await sqlServerTokenProvider.GetTokenAsync(
new TokenRequestContext(scopes: new string[]
{
"https://ossrdbms-aad.database.chinacloudapi.cn/.default"
}));
// Combine the token with the connection string from the environment variables provided by Service Connector.
string connectionString =
$"{Environment.GetEnvironmentVariable("AZURE_POSTGRESQL_CONNECTIONSTRING")};Password={accessToken.Token}";
// Establish the connection.
using (var connection = new NpgsqlConnection(connectionString))
{
Console.WriteLine("Opening connection using access token...");
connection.Open();
}
Add the following dependencies in your pom.xml file:
Authenticate with an access token from the azure-identity library and use the token as password. Get the connection information from the environment variables added by Service Connector. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
from azure.identity import DefaultAzureCredential
import psycopg2
# Uncomment the following lines according to the authentication type.
# For system-assigned identity.
# cred = DefaultAzureCredential()
# For user-assigned identity.
# managed_identity_client_id = os.getenv('AZURE_POSTGRESQL_CLIENTID')
# cred = ManagedIdentityCredential(client_id=managed_identity_client_id)
# Acquire the access token
accessToken = cred.get_token('https://ossrdbms-aad.database.chinacloudapi.cn/.default')
# Combine the token with the connection string from the environment variables added by Service Connector to establish the connection.
conn_string = os.getenv('AZURE_POSTGRESQL_CONNECTIONSTRING')
conn = psycopg2.connect(conn_string + ' password=' + accessToken.token)
In code, get the access token via @azure/identity and PostgreSQL connection information from environment variables added by Service Connector service. Combine them to establish the connection. When using the code below, make sure you uncomment the part of the code snippet that corresponds to the authentication type you want to use.
import { DefaultAzureCredential, ClientSecretCredential } from "@azure/identity";
const { Client } = require('pg');
// Uncomment the following lines according to the authentication type.
// For system-assigned identity.
// const credential = new DefaultAzureCredential();
// For user-assigned identity.
// const clientId = process.env.AZURE_POSTGRESQL_CLIENTID;
// const credential = new DefaultAzureCredential({
// managedIdentityClientId: clientId
// });
// Acquire the access token.
var accessToken = await credential.getToken('https://ossrdbms-aad.database.chinacloudapi.cn/.default');
// Use the token and the connection information from the environment variables added by Service Connector to establish the connection.
(async () => {
const client = new Client({
host: process.env.AZURE_POSTGRESQL_HOST,
user: process.env.AZURE_POSTGRESQL_USER,
password: accesstoken.token,
database: process.env.AZURE_POSTGRESQL_DATABASE,
port: Number(process.env.AZURE_POSTGRESQL_PORT) ,
ssl: process.env.AZURE_POSTGRESQL_SSL
});
await client.connect();
await client.end();
})();
4. Set up your dev environment
This sample code uses DefaultAzureCredential to get a useable token for your Azure database from Microsoft Entra ID and then adds it to the database connection. While you can customize DefaultAzureCredential, it's already versatile by default. It gets a token from the signed-in Microsoft Entra user or from a managed identity, depending on whether you run it locally in your development environment or in App Service.
Without any further changes, your code is ready to be run in Azure. To debug your code locally, however, your develop environment needs a signed-in Microsoft Entra user. In this step, you configure your environment of choice by signing in with your Microsoft Entra user.
Visual Studio for Windows is integrated with Microsoft Entra authentication. To enable development and debugging in Visual Studio, add your Microsoft Entra user in Visual Studio by selecting File > Account Settings from the menu, and select Sign in or Add.
To set the Microsoft Entra user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. Select the Microsoft Entra user you added and select OK.
Visual Studio for Mac is not integrated with Microsoft Entra authentication. However, the Azure Identity client library that you'll use later can also retrieve tokens from Azure CLI. To enable development and debugging in Visual Studio, install Azure CLI on your local machine.
Sign in to Azure CLI with the following command using your Microsoft Entra user:
az login --allow-no-subscriptions
Visual Studio Code is integrated with Microsoft Entra authentication through the Azure extension. Install the Azure Tools extension in Visual Studio Code.
In Visual Studio Code, in the Activity Bar, select the Azure logo.
In the App Service explorer, select Sign in to Azure... and follow the instructions.
The Azure Identity client library that you'll use later can use tokens from Azure CLI. To enable command-line based development, install Azure CLI on your local machine.
Sign in to Azure with the following command using your Microsoft Entra user:
az login --allow-no-subscriptions
The Azure Identity client library that you'll use later can use tokens from Azure PowerShell. To enable command-line based development, install Azure PowerShell on your local machine.
Sign in to Azure CLI with the following cmdlet using your Microsoft Entra user:
You're now ready to develop and debug your app with the SQL Database as the back end, using Microsoft Entra authentication.
5. Test and publish
Run your code in your dev environment. Your code uses the signed-in Microsoft Entra user in your environment to connect to the back-end database. The user can access the database because it's configured as a Microsoft Entra administrator for the database.
Publish your code to Azure using the preferred publishing method. In App Service, your code uses the app's managed identity to connect to the back-end database.
Microsoft Entra ID and managed identities aren't supported for on-premises SQL Server.
I get the error Login failed for user '<token-identified principal>'.
The managed identity you're attempting to request a token for is not authorized to access the Azure database.
I made changes to App Service authentication or the associated app registration. Why do I still get the old token?
The back-end services of managed identities also maintain a token cache that updates the token for a target resource only when it expires. If you modify the configuration after trying to get a token with your app, you don't actually get a new token with the updated permissions until the cached token expires. The best way to work around this is to test your changes with a new InPrivate (Edge)/private (Safari)/Incognito (Chrome) window. That way, you're sure to start from a new authenticated session.
How do I add the managed identity to a Microsoft Entra group?
If you want, you can add the identity to an Microsoft Entra group, then grant access to the Microsoft Entra group instead of the identity. For example, the following commands add the managed identity from the previous step to a new group called myAzureSQLDBAccessGroup:
groupid=$(az ad group create --display-name myAzureSQLDBAccessGroup --mail-nickname myAzureSQLDBAccessGroup --query objectId --output tsv)
msiobjectid=$(az webapp identity show --resource-group <group-name> --name <app-name> --query principalId --output tsv)
az ad group member add --group $groupid --member-id $msiobjectid
az ad group member list -g $groupid
To grant database permissions for a Microsoft Entra group, see documentation for the respective database type.
I get the error SSL connection is required. Please specify SSL options and retry.
Connecting to the Azure database requires additional settings and is beyond the scope of this tutorial. For more information, see one of the following links: