Tutorial: Connect to Azure databases from App Service without secrets using a managed identity
Article
25 minutes to read
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 an Azure AD user as an administrator for your Azure database.
Connect to your database as the Azure AD 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 Azure AD user.
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.
If you prefer to run CLI reference commands locally, install the Azure CLI. If you're running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.
If you're using a local installation, 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 Azure AD user
First, enable Azure Active Directory authentication to the Azure database by assigning an Azure AD 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 Azure AD. For more information on allowed Azure AD users, see Azure AD features and limitations in SQL Database.
Add this Azure AD user as an Active Directory administrator using az sql server ad-admin create command in the Cloud Shell. 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 Azure AD user as an Active Directory administrator using az mysql server ad-admin create command in the Cloud Shell. 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 Azure AD user as an Active Directory administrator using az postgres server ad-admin create command in the Cloud Shell. 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 Cloud Shell. 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 Cloud Shell, 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.azure.com --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.onmicrosoft.com@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.azure.com 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.onmicrosoft.com@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.
3. Modify your code
In this section, connectivity to the Azure database 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 the resource URI respective to the database type.
For Azure SQL Database: https://database.windows.net/.default
For Azure Database for MySQL: https://ossrdbms-aad.database.windows.net
For Azure Database for PostgreSQL: https://ossrdbms-aad.database.windows.net
Add the token to your connection string.
Open the connection.
For Azure Database for MySQL and Azure Database for PostgreSQL, the database username that you created in 2. Configure managed identity for app is also required in the connection string.
// Uncomment one of the two lines depending on the identity type
//var credential = new Azure.Identity.DefaultAzureCredential(); // system-assigned identity
//var credential = new Azure.Identity.DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure SQL Database
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://database.windows.net/.default" }));
// Add the token to the SQL connection
var connection = new System.Data.SqlClient.SqlConnection("Server=tcp:<server-name>.database.windows.net;Database=<database-name>;TrustServerCertificate=True");
connection.AccessToken = token.Token;
// Open the SQL connection
connection.Open();
using Azure.Identity;
...
// Uncomment one of the two lines depending on the identity type
//var credential = new DefaultAzureCredential(); // system-assigned identity
//var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for MySQL
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" }));
// Set MySQL user depending on the environment
string user;
if (String.IsNullOrEmpty(Environment.GetEnvironmentVariable("IDENTITY_ENDPOINT")))
user = "<aad-user-name>@<server-name>";
else user = "<mysql-user-name>@<server-name>";
// Add the token to the MySQL connection
var connectionString = "Server=<server-name>.mysql.database.azure.com;" +
"Port=3306;" +
"SslMode=Required;" +
"Database=<database-name>;" +
"Uid=" + user+ ";" +
"Password="+ token.Token;
var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
connection.Open();
using Azure.Identity;
...
// Uncomment one of the two lines depending on the identity type
//var credential = new DefaultAzureCredential(); // system-assigned identity
//var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for PostgreSQL
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" }));
// Check if in Azure and set user accordingly
string postgresqlUser;
if (String.IsNullOrEmpty(Environment.GetEnvironmentVariable("IDENTITY_ENDPOINT")))
postgresqlUser = "<aad-user-name>@<server-name>";
else postgresqlUser = "<postgresql-user-name>@<server-name>";
// Add the token to the PostgreSQL connection
var connectionString = "Server=<server-name>.postgres.database.azure.com;" +
"Port=5432;" +
"Database=<database-name>;" +
"User Id=" + postgresqlUser + ";" +
"Password="+ token.Token;
var connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
Install the .NET packages you need into your .NET project:
using Microsoft.Data.SqlClient;
...
// Uncomment one of the two lines depending on the identity type
//SqlConnection connection = new SqlConnection("Server=tcp:<server-name>.database.windows.net;Database=<database-name>;Authentication=Active Directory Default;TrustServerCertificate=True"); // system-assigned identity
//SqlConnection connection = new SqlConnection("Server=tcp:<server-name>.database.windows.net;Database=<database-name>;Authentication=Active Directory Default;User Id=<client-id-of-user-assigned-identity>;TrustServerCertificate=True"); // user-assigned identity
// Open the SQL connection
connection.Open();
Microsoft.Data.SqlClient provides integrated support of Azure AD authentication. In this case, the Active Directory Default uses DefaultAzureCredential to retrieve the required token for you and adds it to the database connection directly.
using Azure.Identity;
...
// Uncomment one of the two lines depending on the identity type
//var credential = new DefaultAzureCredential(); // system-assigned identity
//var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for MySQL
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" }));
// Set MySQL user depending on the environment
string user;
if (String.IsNullOrEmpty(Environment.GetEnvironmentVariable("IDENTITY_ENDPOINT")))
user = "<aad-user-name>@<server-name>";
else user = "<mysql-user-name>@<server-name>";
// Add the token to the MySQL connection
var connectionString = "Server=<server-name>.mysql.database.azure.com;" +
"Port=3306;" +
"SslMode=Required;" +
"Database=<database-name>;" +
"Uid=" + user+ ";" +
"Password="+ token.Token;
var connection = new MySql.Data.MySqlClient.MySqlConnection(connectionString);
connection.Open();
The if statement sets the MySQL username based on which identity the token applies to. The token is then passed in to the MySQL connection as the password for the Azure identity. For more information, see Connect with Managed Identity to Azure Database for MySQL.
using Azure.Identity;
...
// Uncomment one of the two lines depending on the identity type
//var credential = new DefaultAzureCredential(); // system-assigned identity
//var credential = new DefaultAzureCredential(new DefaultAzureCredentialOptions { ManagedIdentityClientId = '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for PostgreSQL
var token = credential.GetToken(new Azure.Core.TokenRequestContext(new[] { "https://ossrdbms-aad.database.windows.net" }));
// Check if in Azure and set user accordingly
string postgresqlUser;
if (String.IsNullOrEmpty(Environment.GetEnvironmentVariable("IDENTITY_ENDPOINT")))
postgresqlUser = "<aad-user-name>@<server-name>";
else postgresqlUser = "<postgresql-user-name>@<server-name>";
// Add the token to the PostgreSQL connection
var connectionString = "Server=<server-name>.postgres.database.azure.com;" +
"Port=5432;" +
"Database=<database-name>;" +
"User Id=" + postgresqlUser + ";" +
"Password="+ token.Token;
var connection = new Npgsql.NpgsqlConnection(connectionString);
connection.Open();
The if statement sets the PostgreSQL username based on which identity the token applies to. The token is then passed in to the PostgreSQL connection as the password for the Azure identity. For more information, see Connect with Managed Identity to Azure Database for PostgreSQL.
Install the required npm packages you need into your Node.js project:
const mysql = require('mysql2');
const { DefaultAzureCredential } = require("@azure/identity");
// Uncomment one of the two lines depending on the identity type
//const credential = new DefaultAzureCredential(); // system-assigned identity
//const credential = new DefaultAzureCredential({ managedIdentityClientId: '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for MySQL
const accessToken = await credential.getToken("https://ossrdbms-aad.database.windows.net");
// Set MySQL user depending on the environment
if(process.env.IDENTITY_ENDPOINT) {
var mysqlUser = '<mysql-user-name>@<server-name>';
} else {
var mysqlUser = '<aad-user-name>@<server-name>';
}
// Add the token to the MySQL connection
var config =
{
host: '<server-name>.mysql.database.azure.com',
user: mysqlUser,
password: accessToken.token,
database: '<database-name>',
port: 3306,
insecureAuth: true,
authPlugins: {
mysql_clear_password: () => () => {
return Buffer.from(accessToken.token + '\0')
}
}
};
const conn = new mysql.createConnection(config);
// Open the database connection
conn.connect(
function (err) {
if (err) {
console.log("!!! Cannot connect !!! Error:");
throw err;
}
else
{
...
}
});
The if statement sets the MySQL username based on which identity the token applies to. The token is then passed in to the standard MySQL connection as the password of the Azure identity.
const pg = require('pg');
const { DefaultAzureCredential } = require("@azure/identity");
// Uncomment one of the two lines depending on the identity type
//const credential = new DefaultAzureCredential(); // system-assigned identity
//const credential = new DefaultAzureCredential({ managedIdentityClientId: '<client-id-of-user-assigned-identity>' }); // user-assigned identity
// Get token for Azure Database for PostgreSQL
const accessToken = await credential.getToken("https://ossrdbms-aad.database.windows.net");
// Set PosrgreSQL user depending on the environment
if(process.env.IDENTITY_ENDPOINT) {
var postgresqlUser = '<postgresql-user-name>@<server-name>';
} else {
var postgresqlUser = '<aad-user-name>@<server-name>';
}
// Add the token to the PostgreSQL connection
var config =
{
host: '<server-name>.postgres.database.azure.com',
user: postgresqlUser,
password: accessToken.token,
database: '<database-name>',
port: 5432
};
const client = new pg.Client(config);
// Open the database connection
client.connect(err => {
if (err) throw err;
else {
// Do something with the connection...
}
});
The if statement sets the PostgreSQL username based on which identity the token applies to. The token is then passed in to the standard PostgreSQL connection as the password of the Azure identity.
In your Python project, install the required packages.
The required ODBC Driver 17 for SQL Server is already installed in App Service. To run the same code locally, install it in your local environment too.
from azure.identity import DefaultAzureCredential
import pyodbc, struct
# Uncomment one of the two lines depending on the identity type
#credential = DefaultAzureCredential() # system-assigned identity
#credential = DefaultAzureCredential(managed_identity_client_id='<client-id-of-user-assigned-identity>') # user-assigned identity
# Get token for Azure SQL Database and convert to UTF-16-LE for SQL Server driver
token = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token)}s', len(token), token)
# Connect with the token
SQL_COPT_SS_ACCESS_TOKEN = 1256
connString = f"Driver={{ODBC Driver 17 for SQL Server}};SERVER=<server-name>.database.windows.net;DATABASE=<database-name>"
conn = pyodbc.connect(connString, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
The ODBC Driver 17 for SQL Server also supports an authentication type ActiveDirectoryMsi. You can connect from App Service without getting the token yourself, simply with the connection string Driver={{ODBC Driver 17 for SQL Server}};SERVER=<server-name>.database.windows.net;DATABASE=<database-name>;Authentication=ActiveDirectoryMsi. The difference with the above code is that it gets the token with DefaultAzureCredential, which works both in App Service and in your local development environment.
from azure.identity import DefaultAzureCredential
import mysql.connector
import os
# Uncomment one of the two lines depending on the identity type
#credential = DefaultAzureCredential() # system-assigned identity
#credential = DefaultAzureCredential(managed_identity_client_id='<client-id-of-user-assigned-identity>') # user-assigned identity
# Get token for Azure Database for MySQL
token = credential.get_token("https://ossrdbms-aad.database.windows.net")
# Set MySQL user depending on the environment
if 'IDENTITY_ENDPOINT' in os.environ:
mysqlUser = '<mysql-user-name>@<server-name>'
else:
mysqlUser = '<aad-user-name>@<server-name>'
# Connect with the token
os.environ['LIBMYSQL_ENABLE_CLEARTEXT_PLUGIN'] = '1'
config = {
'host': '<server-name>.mysql.database.azure.com',
'database': '<database-name>',
'user': mysqlUser,
'password': token.token
}
conn = mysql.connector.connect(**config)
print("Connection established")
The if statement sets the MySQL username based on which identity the token applies to. The token is then passed in to the standard MySQL connection as the password of the Azure identity.
from azure.identity import DefaultAzureCredential
import psycopg2
# Uncomment one of the two lines depending on the identity type
#credential = DefaultAzureCredential() # system-assigned identity
#credential = DefaultAzureCredential(managed_identity_client_id='<client-id-of-user-assigned-identity>') # user-assigned identity
# Get token for Azure Database for PostgreSQL
token = credential.get_token("https://ossrdbms-aad.database.windows.net")
# Set PostgreSQL user depending on the environment
if 'IDENTITY_ENDPOINT' in os.environ:
postgresUser = '<postgres-user-name>@<server-name>'
else:
postgresUser = '<aad-user-name>@<server-name>'
# Connect with the token
host = "<server-name>.postgres.database.azure.com"
dbname = "<database-name>"
conn_string = "host={0} user={1} dbname={2} password={3}".format(host, postgresUser, dbname, token.token)
conn = psycopg2.connect(conn_string)
The if statement sets the PostgreSQL username based on which identity the token applies to. The token is then passed in to the standard PostgreSQL connection as the password of the Azure identity.
import com.azure.identity.*;
import com.azure.core.credential.*;
import com.microsoft.sqlserver.jdbc.SQLServerDataSource;
import java.sql.*;
...
// Uncomment one of the two lines depending on the identity type
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().build(); // system-assigned identity
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().managedIdentityClientId('<client-id-of-user-assigned-identity>")'build(); // user-assigned identity
// Get the token
TokenRequestContext request = new TokenRequestContext();
request.addScopes("https://database.windows.net//.default");
AccessToken token=creds.getToken(request).block();
// Set token in your SQL connection
SQLServerDataSource ds = new SQLServerDataSource();
ds.setServerName("<server-name>.database.windows.net");
ds.setDatabaseName("<database-name>");
ds.setAccessToken(token.getToken());
// Connect
try {
Connection connection = ds.getConnection();
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT SUSER_SNAME()");
if (rs.next()) {
System.out.println("Signed into database as: " + rs.getString(1));
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
The [JDBC Driver for SQL Server] also has an authentication type ActiveDirectoryMsi, which is easier to use for App Service. The above code gets the token with DefaultAzureCredential, which works both in App Service and in your local development environment.
import com.azure.identity.*;
import com.azure.core.credential.*;
import java.sql.*;
...
// Uncomment one of the two lines depending on the identity type
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().build(); // system-assigned identity
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().managedIdentityClientId('<client-id-of-user-assigned-identity>")'build(); // user-assigned identity
// Get the token
TokenRequestContext request = new TokenRequestContext();
request.addScopes("https://ossrdbms-aad.database.windows.net");
AccessToken token=creds.getToken(request).block();
// Set MySQL user depending on the environment
String mysqlUser;
if (System.getenv("IDENTITY_ENDPOINT" != null)) {
mysqlUser = "<aad-user-name>@<server-name>";
}
else {
mysqlUser = "<mysql-user-name>@<server-name>";
}
// Set token in your SQL connection
try {
Connection connection = DriverManager.getConnection(
"jdbc:mysql://<server-name>.mysql.database.azure.com/<database-name>",
mysqlUser,
token.getToken());
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("SELECT USER();");
if (rs.next()) {
System.out.println("Signed into database as: " + rs.getString(1));
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
The if statement sets the MySQL username based on which identity the token applies to. The token is then passed in to the standard MySQL connection as the password of the Azure identity.
import com.azure.identity.*;
import com.azure.core.credential.*;
import java.sql.*;
...
// Uncomment one of the two lines depending on the identity type
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().build(); // system-assigned identity
//DefaultAzureCredential creds = new DefaultAzureCredentialBuilder().managedIdentityClientId('<client-id-of-user-assigned-identity>")'build(); // user-assigned identity
// Get the token
TokenRequestContext request = new TokenRequestContext();
request.addScopes("https://ossrdbms-aad.database.windows.net");
AccessToken token=creds.getToken(request).block();
// Set PostgreSQL user depending on the environment
String postgresUser;
if (System.getenv("IDENTITY_ENDPOINT") != null) {
postgresUser = "<aad-user-name>@<server-name>";
}
else {
postgresUser = "<postgresql-user-name>@<server-name>";
}
// Set token in your SQL connection
try {
Connection connection = DriverManager.getConnection(
"jdbc:postgresql://<server-name>.postgres.database.azure.com:5432/<database-name>",
postgresUser,
token.getToken());
Statement stmt = connection.createStatement();
ResultSet rs = stmt.executeQuery("select current_user;");
if (rs.next()) {
System.out.println("Signed into database as: " + rs.getString(1));
}
}
catch (Exception e) {
System.out.println(e.getMessage());
}
The if statement sets the PostgreSQL username based on which identity the token applies to. The token is then passed in to the standard PostgreSQL connection as the password of the identity. To see how you can do it similarly with specific frameworks, see:
This sample code uses DefaultAzureCredential to get a useable token for your Azure database from Azure Active Directory 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 Azure AD 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 Azure AD user. In this step, you configure your environment of choice by signing in with your Azure AD user.
Visual Studio for Windows is integrated with Azure AD authentication. To enable development and debugging in Visual Studio, add your Azure AD user in Visual Studio by selecting File > Account Settings from the menu, and select Sign in or Add.
To set the Azure AD user for Azure service authentication, select Tools > Options from the menu, then select Azure Service Authentication > Account Selection. Select the Azure AD user you added and select OK.
Visual Studio for Mac is not integrated with Azure AD 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 Azure AD user:
az login --allow-no-subscriptions
Visual Studio Code is integrated with Azure AD 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 Azure AD 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 Azure AD user:
You're now ready to develop and debug your app with the SQL Database as the back end, using Azure AD authentication.
5. Test and publish
Run your code in your dev environment. Your code uses the signed-in Azure AD user) in your environment to connect to the back-end database. The user can access the database because it's configured as an Azure AD 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.
Azure Active Directory 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 an Azure AD group?
If you want, you can add the identity to an Azure AD group, then grant access to the Azure AD 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 an Azure AD group, see documentation for the respective database type.
I get the error mysql: unknown option '--enable-cleartext-plugin'.
If you're using a MariaDB client, the --enable-cleartext-plugin option isn't required.
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:
