Tutorial: Create a passwordless connection to a database service via Service Connector

Article
10/31/2023

Passwordless connections use managed identities to access Azure services. With this approach, you don't have to manually track and manage secrets for managed identities. These tasks are securely handled internally by Azure.

Service Connector enables managed identities in app hosting services like Azure Spring Apps, Azure App Service, and Azure Container Apps. Service Connector also configures database services, such as Azure Database for PostgreSQL, Azure Database for MySQL, and Azure SQL Database, to accept managed identities.

In this tutorial, you use the Azure CLI to complete the following tasks:

Check your initial environment with the Azure CLI.
Create a passwordless connection with Service Connector.
Use the environment variables or configurations generated by Service Connector to access a database service.

Prerequisites

Azure CLI version 2.48.1 or higher.
An Azure account with an active subscription. Create an Azure account for free.
An app deployed to Azure App Service in a region supported by Service Connector.

Set up your environment

Account

Sign in with the Azure CLI via az login. If you're using Azure Cloud Shell or are already logged in, confirm your authenticated account with az account show.

Install the Service Connector passwordless extension

Install the Service Connector passwordless extension for the Azure CLI:

az extension add --name serviceconnector-passwordless --upgrade

Create a passwordless connection

Next, we use Azure App Service as an example to create a connection using managed identity.

If you use:

Azure Spring Apps: use az spring connection create instead. For more examples, see Connect Azure Spring Apps to the Azure database.
Azure Container Apps: use az containerapp connection create instead. For more examples, see Create and connect a PostgreSQL database with identity connectivity.

Note

If you use the Azure portal, go to the Service Connector blade of Azure App Service, Azure Spring Apps, or Azure Container Apps, and select Create to create a connection. The Azure portal will automatically compose the command for you and trigger the command execution on Cloud Shell.

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create postgres-flexible -h to get the supported client types, and choose the one that matches your application.

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX \
    --client-type java

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --system-identity \
    --client-type java

az webapp connection create postgres-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $POSTGRESQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX\
    --client-type java

Azure Database for MySQL - Flexible Server requires a user-assigned managed identity to enable Microsoft Entra authentication. For more information, see Set up Microsoft Entra authentication for Azure Database for MySQL - Flexible Server. You can use the following command to create a user-assigned managed identity:

USER_IDENTITY_NAME=<YOUR_USER_ASSIGNED_MANAGEMED_IDENTITY_NAME>
IDENTITY_RESOURCE_ID=$(az identity create \
    --name $USER_IDENTITY_NAME \
    --resource-group $RESOURCE_GROUP \
    --query id \
    --output tsv)

Important

After creating the user-assigned managed identity, ask your Global Administrator or Privileged Role Administrator to grant the following permissions for this identity:

User.Read.All
GroupMember.Read.All
Application.Read.All

For more information, see the Permissions section of Active Directory authentication.

Then, connect your app to a MySQL database with a system-assigned managed identity using Service Connector.

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create mysql-flexible -h to get the supported client types, and choose the one that matches your application.

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --system-identity mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

az webapp connection create mysql-flexible \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $MYSQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX mysql-identity-id=$IDENTITY_RESOURCE_ID \
    --client-type java

The following Azure CLI command uses a --client-type parameter. Run the az webapp connection create sql -h to get the supported client types, and choose the one that matches your application.

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --user-identity client-id=XX subs-id=XX \
    --client-type dotnet

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --system-identity \
    --client-type dotnet

az webapp connection create sql \
    --resource-group $RESOURCE_GROUP \
    --name $APPSERVICE_NAME \
    --target-resource-group $RESOURCE_GROUP \
    --server $SQL_HOST \
    --database $DATABASE_NAME \
    --service-principal client-id=XX secret=XX \
    --client-type dotnet

This Service Connector command completes the following tasks in the background:

Enable system-assigned managed identity, or assign a user identity for the app $APPSERVICE_NAME hosted by Azure App Service/Azure Spring Apps/Azure Container Apps.
Set the Microsoft Entra admin to the current signed-in user.
Add a database user for the system-assigned managed identity, user-assigned managed identity, or service principal. Grant all privileges of the database $DATABASE_NAME to this user. The username can be found in the connection string in preceding command output.
Set configurations named AZURE_MYSQL_CONNECTIONSTRING, AZURE_POSTGRESQL_CONNECTIONSTRING, or AZURE_SQL_CONNECTIONSTRING to the Azure resource based on the database type.
- For App Service, the configurations are set in the App Settings blade.
- For Spring Apps, the configurations are set when the application is launched.
- For Container Apps, the configurations are set to the environment variables. You can get all configurations and their values in the Service Connector blade in the Azure portal.

Connect to a database with Microsoft Entra authentication

After creating the connection, you can use the connection string in your application to connect to the database with Microsoft Entra authentication. For example, you can use the following solutions to connect to the database with Microsoft Entra authentication.

For .NET, there's not a plugin or library to support passwordless connections. You can get an access token for the managed identity or service principal using client library like Azure.Identity. Then you can use the access token as the password to connect to the database. When using the code below, uncomment the part of the code snippet for 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");
//     }
// );

// For service principal.
// var tenantId = Environment.GetEnvironmentVariable("AZURE_POSTGRESQL_TENANTID");
// var clientId = Environment.GetEnvironmentVariable("AZURE_POSTGRESQL_CLIENTID");
// var clientSecret = Environment.GetEnvironmentVariable("AZURE_POSTGRESQL_CLIENTSECRET");
// var sqlServerTokenProvider = new ClientSecretCredential(tenantId, clientId, clientSecret);

// Acquire the access token. 
AccessToken accessToken = await sqlServerTokenProvider.GetTokenAsync(
    new TokenRequestContext(scopes: new string[]
    {
        "https://ossrdbms-aad.database.windows.net/.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:

<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
    <version>42.3.6</version>
</dependency>
<dependency>
    <groupId>com.azure</groupId>
    <artifactId>azure-identity-extensions</artifactId>
    <version>1.1.5</version>
</dependency>

Get the connection string from environment variables and add the plugin name to connect to the database:

import java.sql.*;

String url = System.getenv("AZURE_POSTGRESQL_CONNECTIONSTRING");
String pluginName = "com.Azure.identity.extensions.jdbc.postgresql.AzurePostgresqlAuthenticationPlugin";  
Connection connection = DriverManager.getConnection(url + "&authenticationPluginClassName=" + pluginName);

For more information, see the following resources:

For a Spring application, if you create a connection with option --client-type springboot, Service Connector sets the properties spring.datasource.azure.passwordless-enabled, spring.datasource.url, and spring.datasource.username to Azure Spring Apps.

Update your application following the tutorial Bind an Azure Database for PostgreSQL to your application in Azure Spring Apps. Remember to remove the spring.datasource.password configuration property if it was set before and add the correct dependencies to your Spring application.

For more tutorials, see Use Spring Data JDBC with Azure Database for PostgreSQL and Tutorial: Deploy a Spring application to Azure Spring Apps with a passwordless connection to an Azure database.

Install dependencies.

pip install azure-identity
pip install psycopg2-binary

Get access token using azure-identity library and use the token as password. Get connection information from the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for 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)   

# For service principal.
# tenant_id = os.getenv('AZURE_POSTGRESQL_TENANTID')
# client_id = os.getenv('AZURE_POSTGRESQL_CLIENTID')
# client_secret = os.getenv('AZURE_POSTGRESQL_CLIENTSECRET')
# cred = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret)

# Acquire the access token
accessToken = cred.get_token('https://ossrdbms-aad.database.windows.net/.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)

Install dependencies.
```
pip install azure-identity
```

Get access token using azure-identity library using environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for 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.
# credential = DefaultAzureCredential()

# For user-assigned identity.
# managed_identity_client_id = os.getenv('AZURE_POSTGRESQL_CLIENTID')
# cred = ManagedIdentityCredential(client_id=managed_identity_client_id)    

# For service principal.
# tenant_id = os.getenv('AZURE_POSTGRESQL_TENANTID')
# client_id = os.getenv('AZURE_POSTGRESQL_CLIENTID')
# client_secret = os.getenv('AZURE_POSTGRESQL_CLIENTSECRET')
# cred = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret)

# Acquire the access token.
accessToken = cred.get_token('https://ossrdbms-aad.database.windows.net/.default')

In setting file, get Azure PostgreSQL database information from environment variables added by Service Connector service. Use accessToken acquired in previous step to access the database.

# In your setting file, eg. settings.py
host = os.getenv('AZURE_POSTGRESQL_HOST')
user = os.getenv('AZURE_POSTGRESQL_USER')
password = accessToken.token # this is accessToken acquired from above step.
database = os.getenv('AZURE_POSTGRESQL_NAME')

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': database,
        'USER': user,
        'PASSWORD': password,
        'HOST': host,
        'PORT': '5432',  # Port is 5432 by default 
        'OPTIONS': {'sslmode': 'require'},
    }
}

Install dependencies.

go get github.com/lib/pq
go get "github.com/Azure/azure-sdk-for-go/sdk/azidentity"
go get "github.com/Azure/azure-sdk-for-go/sdk/azcore"

In code, get access token using azidentity, then use it as password to connect to Azure PostgreSQL along with connection information provided by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.

import (
"database/sql"
"fmt"
"os"

"context"

"github.com/Azure/azure-sdk-for-go/sdk/azcore/policy"
"github.com/Azure/azure-sdk-for-go/sdk/azidentity"

 _ "github.com/lib/pq"
)    

// Uncomment the following lines according to the authentication type.
// For system-assigned identity.
// cred, err := azidentity.NewDefaultAzureCredential(nil)

// For user-assigned identity.
// clientid := os.Getenv("AZURE_POSTGRESQL_CLIENTID")
// azidentity.ManagedIdentityCredentialOptions.ID := clientid
// options := &azidentity.ManagedIdentityCredentialOptions{ID: clientid}
// cred, err := azidentity.NewManagedIdentityCredential(options)

// For service principal.
// clientid := os.Getenv("AZURE_POSTGRESQL_CLIENTID")
// tenantid := os.Getenv("AZURE_POSTGRESQL_TENANTID")
// clientsecret := os.Getenv("AZURE_POSTGRESQL_CLIENTSECRET")
// cred, err := azidentity.NewClientSecretCredential(tenantid, clientid, clientsecret, &azidentity.ClientSecretCredentialOptions{})

if err != nil {
    // error handling
}

// Acquire the access token
ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
token, err := cred.GetToken(ctx, policy.TokenRequestOptions{
    Scopes: []string("https://ossrdbms-aad.database.windows.net/.default"),
})

// Combine the token with the connection string from the environment variables added by Service Connector to establish the connection.
connectionString := os.Getenv("AZURE_POSTGRESQL_CONNECTIONSTRING") + " password=" + token.Token

conn, err := sql.Open("postgres", connectionString)
 if err != nil {
 	panic(err)
 }

 conn.Close()

Install dependencies.

npm install --save @azure/identity
npm install --save pg

In code, get the access token using @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, uncomment the part of the code snippet for 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
// });

// For service principal.
// const tenantId = process.env.AZURE_POSTGRESQL_TENANTID;
// const clientId = process.env.AZURE_POSTGRESQL_CLIENTID;
// const clientSecret = process.env.AZURE_POSTGRESQL_CLIENTSECRET;

// Acquire the access token.
var accessToken = await credential.getToken('https://ossrdbms-aad.database.windows.net/.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();
})();

For PHP, there's not a plugin or library for passwordless connections. You can get an access token for the managed identity or service principal and use it as the password to connect to the database. The access token can be acquired using Azure REST API.

In code, get the access token using REST API with your favorite library.

For user-assigned identity and system-assigned identity, App Service and Container Apps provides an internally accessible REST endpoint to retrieve tokens for managed identities by defining two environment variables: IDENTITY_ENDPOINT and IDENTITY_HEADER. For more information, see REST endpoint reference. Get the access token by making an HTTP GET request to the identity endpoint, and use https://ossrdbms-aad.database.windows.net as resource in the query. For user-assigned identity, please include the client ID from the environment variables added by Service Connector in the query as well.

For service principal, refer to the Azure AD service-to-service access token request to see the details of how to acquire access token. Make the POST request the scope of https://ossrdbms-aad.database.windows.net/.default and with the tenant ID, client ID and client secret of the service principal from the environment variables added by Service Connector.
Combine the access token and the PostgreSQL connection sting from environment variables added by Service Connector service to establish the connection.
```
<?php
$conn_string = sprintf("%s password=", getenv('AZURE_POSTGRESQL_CONNECTIONSTRING'), $access_token);
$dbconn = pg_connect($conn_string);
?>
```

For Ruby, there's not a plugin or library for passwordless connections. You can get an access token for the managed identity or service principal and use it as the password to connect to the database. The access token can be acquired using Azure REST API.

Install dependencies.
```
gem install pg
```

In code, get the access token using REST API and PostgreSQL connection information from environment variables added by Service Connector service. Combine them to establish the connection. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.

App service and container Apps provides an internally accessible REST endpoint to retrieve tokens for managed identities. For more information, see REST endpoint reference.

require 'pg'
require 'dotenv/load'
require 'net/http'
require 'json'

# Uncomment the following lines according to the authentication type.
# For system-assigned identity.
# uri = URI(ENV['IDENTITY_ENDPOINT'] + '?resource=https://ossrdbms-aad.database.windows.net&api-version=2019-08-01')
# res = Net::HTTP.get_response(uri, {'X-IDENTITY-HEADER' => ENV['IDENTITY_HEADER'], 'Metadata' => 'true'})  

# For user-assigned identity.
# uri = URI(ENV[IDENTITY_ENDPOINT] + '?resource=https://ossrdbms-aad.database.windows.net&api-version=2019-08-01&client-id=' + ENV['AZURE_POSTGRESQL_CLIENTID'])
# res = Net::HTTP.get_response(uri, {'X-IDENTITY-HEADER' => ENV['IDENTITY_HEADER'], 'Metadata' => 'true'})  

# For service principal
# uri = URI('https://login.microsoftonline.com/' + ENV['AZURE_POSTGRESQL_TENANTID'] + '/oauth2/v2.0/token')
# params = {
#     :grant_type => 'client_credentials',
#     :client_id: => ENV['AZURE_POSTGRESQL_CLIENTID'],
#     :client_secret => ENV['AZURE_POSTGRESQL_CLIENTSECRET'],
#     :scope => 'https://ossrdbms-aad.database.windows.net/.default'
# }
# req = Net::HTTP::POST.new(uri)
# req.set_form_data(params)
# req['Content-Type'] = 'application/x-www-form-urlencoded'
# res = Net::HTTP.start(uri.hostname, uri.port, :use_ssl => true) do |http|
#   http.request(req)

parsed = JSON.parse(res.body)
access_token = parsed["access_token"]

# Use the token and the connection string from the environment variables added by Service Connector to establish the connection.
conn = PG::Connection.new(
    connection_string: ENV['AZURE_POSTGRESQL_CONNECTIONSTRING'] + " password="  + access_token,
)

Refer to the Azure AD service-to-service access token request to see more details of how to acquire access token for service principal.

Next, if you have created tables and sequences in PostgreSQL flexible server before using Service Connector, you need to connect as the owner and grant permission to <aad-username> created by Service Connector. The username from the connection string or configuration set by Service Connector should look like aad_<connection name>. If you use the Azure portal, select the expand button next to the Service Type column and get the value. If you use Azure CLI, check configurations in the CLI command output.

Then, execute the query to grant permission

az extension add --name rdbms-connect

az postgres flexible-server execute -n <postgres-name> -u <owner-username> -p "<owner-password>" -d <database-name> --querytext "GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO \"<aad-username>\";GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO \"<aad username>\";"

The <owner-username> and <owner-password> is the owner of the existing table that can grant permissions to others. <aad-username> is the user created by Service Connector. Replace them with the actual value.

Validate the result with the command:

az postgres flexible-server execute -n <postgres-name> -u <owner-username> -p "<owner-password>" -d <database-name> --querytext "SELECT distinct(table_name) FROM information_schema.table_privileges WHERE grantee='<aad-username>' AND table_schema='public';" --output table

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");
//     });

// For service principal.
// var tenantId = Environment.GetEnvironmentVariable("AZURE_MYSQL_TENANTID");
// var clientId = Environment.GetEnvironmentVariable("AZURE_MYSQL_CLIENTID");
// var clientSecret = Environment.GetEnvironmentVariable("AZURE_MYSQL_CLIENTSECRET");
// var credential = new ClientSecretCredential(tenantId, clientId, clientSecret);

var tokenRequestContext = new TokenRequestContext(
    new[] { "https://ossrdbms-aad.database.windows.net/.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:

<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
    <version>8.0.30</version>
</dependency>
<dependency>
    <groupId>com.azure</groupId>
    <artifactId>azure-identity-extensions</artifactId>
    <version>1.1.5</version>
</dependency>

Get the connection string from the environment variable, and add the plugin name to connect to the database:

String url = System.getenv("AZURE_MYSQL_CONNECTIONSTRING");  
String pluginName = "com.azure.identity.extensions.jdbc.mysql.AzureMysqlAuthenticationPlugin";
Connection connection = DriverManager.getConnection(url + "&defaultAuthenticationPlugin=" +
    pluginName + "&authenticationPlugins=" + pluginName);

For more information, see Use Java and JDBC with Azure Database for MySQL - Flexible Server.

Update your application following the tutorial Connect an Azure Database for MySQL instance to your application in Azure Spring Apps. Remember to remove the spring.datasource.password configuration property if it was set before and add the correct dependencies to your Spring application.

For more tutorials, see Use Spring Data JDBC with Azure Database for MySQL

Install dependencies

pip install azure-identity
# install Connector/Python https://dev.mysql.com/doc/connector-python/en/connector-python-installation.html
pip install mysql-connector-python

Authenticate with access token get via azure-identity library and get connection information from the environment variable added by Service Connector. When using the code below, uncomment the part of the code snippet for 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)

# For service principal
# tenant_id = os.getenv('AZURE_MYSQL_TENANTID')
# client_id = os.getenv('AZURE_MYSQL_CLIENTID')
# client_secret = os.getenv('AZURE_MYSQL_CLIENTSECRET')
# cred = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret)

# acquire token
accessToken = cred.get_token('https://ossrdbms-aad.database.windows.net/.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()

Install dependencies.
```
pip install azure-identity
```

Get access token via azure-identity library with the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.

from azure.identity import ManagedIdentityCredential, ClientSecretCredential
import os

# Uncomment the following lines according to the authentication type.
# system-assigned managed identity
# cred = ManagedIdentityCredential()

# user-assigned managed identity
# managed_identity_client_id = os.getenv('AZURE_MYSQL_CLIENTID')
# cred = ManagedIdentityCredential(client_id=managed_identity_client_id)

# service principal
# tenant_id = os.getenv('AZURE_MYSQL_TENANTID')
# client_id = os.getenv('AZURE_MYSQL_CLIENTID')
# client_secret = os.getenv('AZURE_MYSQL_CLIENTSECRET')
# cred = ClientSecretCredential(tenant_id=tenant_id, client_id=client_id, client_secret=client_secret)

# acquire token
accessToken = cred.get_token('https://ossrdbms-aad.database.windows.net/.default')

In setting file, get Azure MySQL database information from environment variables added by Service Connector service. Use accessToken acquired in previous step to access the database.

# in your setting file, eg. settings.py
host = os.getenv('AZURE_MYSQL_HOST')
database = os.getenv('AZURE_MYSQL_NAME')
user = os.getenv('AZURE_MYSQL_USER')
password = accessToken.token # this is accessToken acquired from above step.

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.mysql',
        'NAME': database,
        'USER': user,
        'PASSWORD': password,
        'HOST': host
    }
}

Install dependencies.

go get "github.com/go-sql-driver/mysql"
go get "github.com/Azure/azure-sdk-for-go/sdk/azidentity"
go get "github.com/Azure/azure-sdk-for-go/sdk/azcore"

In code, get access token via azidentity, then connect to Azure MySQL with the token. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.

import (
  "context"

  "github.com/Azure/azure-sdk-for-go/sdk/azcore/policy"
  "github.com/Azure/azure-sdk-for-go/sdk/azidentity"
  "github.com/go-sql-driver/mysql"
)


func main() {

  // Uncomment the following lines according to the authentication type.
  // for system-assigned managed identity
  // cred, err := azidentity.NewDefaultAzureCredential(nil)

  // for user-assigned managed identity
  // clientid := os.Getenv("AZURE_MYSQL_CLIENTID")
  // azidentity.ManagedIdentityCredentialOptions.ID := clientid
  // options := &azidentity.ManagedIdentityCredentialOptions{ID: clientid}
  // cred, err := azidentity.NewManagedIdentityCredential(options)

  // for service principal
  // clientid := os.Getenv("AZURE_MYSQL_CLIENTID")
  // tenantid := os.Getenv("AZURE_MYSQL_TENANTID")
  // clientsecret := os.Getenv("AZURE_MYSQL_CLIENTSECRET")
  // cred, err := azidentity.NewClientSecretCredential(tenantid, clientid, clientsecret, &azidentity.ClientSecretCredentialOptions{})

  if err != nil {
  }

  ctx, cancel := context.WithTimeout(context.Background(), 30*time.Second)
  token, err := cred.GetToken(ctx, policy.TokenRequestOptions{
    Scopes: []string("https://ossrdbms-aad.database.windows.net/.default"),
  })

  connectionString := os.Getenv("AZURE_MYSQL_CONNECTIONSTRING") + ";Password=" + token.Token
  db, err := sql.Open("mysql", connectionString)
}

Install dependencies

npm install --save @azure/identity
npm install --save mysql2

Get access token using @azure/identity and Azure MySQL database information from environment variables added by Service Connector service. When using the code below, uncomment the part of the code snippet for 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
// });

// for service principal
// const tenantId = process.env.AZURE_MYSQL_TENANTID;
// const clientId = process.env.AZURE_MYSQL_CLIENTID;
// const clientSecret = process.env.AZURE_MYSQL_CLIENTSECRET;
// const credential = new ClientSecretCredential(tenantId, clientId, clientSecret);

// acquire token
var accessToken = await credential.getToken('https://ossrdbms-aad.database.windows.net/.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');
});

For more code samples, see Connect to Azure databases from App Service without secrets using a managed identity.

Install dependencies.

dotnet add package Microsoft.Data.SqlClient

Get the Azure SQL Database connection string from the environment variable added by Service Connector.

using Microsoft.Data.SqlClient;

string connectionString = 
    Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;

using var connection = new SqlConnection(connectionString);
connection.Open();

For more information, see Using Active Directory Managed Identity authentication.

Add the following dependencies in your pom.xml file:

<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
    <version>10.2.0.jre11</version>
</dependency>
<dependency>
    <groupId>com.azure</groupId>
    <artifactId>azure-identity</artifactId>
    <version>1.7.0</version>
</dependency>

Get the Azure SQL Database connection string from the environment variable added by Service Connector.

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

import com.microsoft.sqlserver.jdbc.SQLServerDataSource;

public class Main {
    public static void main(String[] args) {
        // AZURE_SQL_CONNECTIONSTRING should be one of the following:
        // For system-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};authentication=ActiveDirectoryMSI;"
        // For user-assigned managed identity: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};msiClientId={UserAssignedMiClientId};authentication=ActiveDirectoryMSI;"
        // For service principal: "jdbc:sqlserver://{SQLName}.database.windows.net:1433;databaseName={SQLDbName};user={ServicePrincipalClientId};password={spSecret};authentication=ActiveDirectoryServicePrincipal;"
        String connectionString = System.getenv("AZURE_SQL_CONNECTIONSTRING");
        SQLServerDataSource ds = new SQLServerDataSource();
        ds.setURL(connectionString);
        try (Connection connection = ds.getConnection()) {
            System.out.println("Connected successfully.");
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}

For more information, see Connect to Azure databases from App Service without secrets using a managed identity.

For a Spring application, if you create a connection with option --client-type springboot, Service Connector sets the properties spring.datasource.url with value format jdbc:sqlserver://<sql-server>.database.windows.net:1433;databaseName=<sql-db>;authentication=ActiveDirectoryMSI; to Azure Spring Apps.

Update your application following the tutorial Migrate a Java application to use passwordless connections with Azure SQL Database. Remember to remove the spring.datasource.password configuration property if it was set before and add the correct dependencies.

Install dependencies.
```
python -m pip install pyodbc
```

Get the Azure SQL Database connection configurations from the environment variable added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use. If you are using Azure Container Apps as compute service or the connection string in the code snippet doesn't work, refer to Migrate a Python application to use passwordless connections with Azure SQL Database to connect to Azure SQL Database using an access token.

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')

# Uncomment the following lines according to the authentication type.
# For system-assigned managed identity.
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

# For user-assigned managed identity.
# clientID = os.getenv('AZURE_SQL_USER')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={clientID};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

# For service principal.
# user = os.getenv('AZURE_SQL_USER')
# password = os.getenv('AZURE_SQL_PASSWORD')
# connString = f'Driver={{ODBC Driver 18 for SQL Server}};Server=tcp:{server},{port};Database={database};UID={user};PWD={password};Authentication={authentication};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30'

conn = pyodbc.connect(connString)

Install dependencies.
```
npm install mssql
```

Get the Azure SQL Database connection configurations from the environment variables added by Service Connector. When using the code below, uncomment the part of the code snippet for the authentication type you want to use.

import sql from 'mssql';

const server = process.env.AZURE_SQL_SERVER;
const database = process.env.AZURE_SQL_DATABASE;
const port = parseInt(process.env.AZURE_SQL_PORT);
const authenticationType = process.env.AZURE_SQL_AUTHENTICATIONTYPE;

// Uncomment the following lines according to the authentication type.
// For system-assigned managed identity.
// const config = {
//     server,
//     port,
//     database,
//     authentication: {
//         type: authenticationType
//     },
//     options: {
//        encrypt: true
//     }
// };  

// For user-assigned managed identity.
// const clientId = process.env.AZURE_SQL_CLIENTID;
// const config = {
//     server,
//     port,
//     database,
//     authentication: {
//         type: authenticationType
//     },
//     options: {
//         encrypt: true,
//         clientId: clientId
//     }
// };  

// For service principal.
// const clientId = process.env.AZURE_SQL_CLIENTID;
// const clientSecret = process.env.AZURE_SQL_CLIENTSECRET;
// const tenantId = process.env.AZURE_SQL_TENANTID;
// const config = {
//     server,
//     port,
//     database,
//     authentication: {
//         type: authenticationType
//     },
//     options: {
//         encrypt: true,
//         clientId: clientId,
//         clientSecret: clientSecret,
//         tenantId: tenantId
//     }
// };  

this.poolconnection = await sql.connect(config);

For more information, see Homepage for client programming to Microsoft SQL Server.

Deploy the application to an Azure hosting service

Finally, deploy your application to an Azure hosting service. That source service can use a managed identity to connect to the target database on Azure.

For Azure App Service, you can deploy the application code via the az webapp deploy command. For more information, see Quickstart: Deploy an ASP.NET web app.

Then you can check the log or call the application to see if it can connect to the Azure database successfully.

Troubleshooting

Permission

If you encounter any permission-related errors, confirm the Azure CLI signed-in user with the command az account show. Make sure you log in with the correct account. Next, confirm that you have the following permissions that might be required to create a passwordless connection with Service Connector.

Permission	Operation
`Microsoft.DBforPostgreSQL/flexibleServers/read`	Required to get information of database server
`Microsoft.DBforPostgreSQL/flexibleServers/write`	Required to enable Microsoft Entra authentication for database server
`Microsoft.DBforPostgreSQL/flexibleServers/firewallRules/write`	Required to create firewall rule in case the local IP address is blocked
`Microsoft.DBforPostgreSQL/flexibleServers/firewallRules/delete`	Required to revert the firewall rule created by Service Connector to avoid security issue
`Microsoft.DBforPostgreSQL/flexibleServers/administrators/read`	Required to check if Azure CLI login user is a database server Microsoft Entra administrator
`Microsoft.DBforPostgreSQL/flexibleServers/administrators/write`	Required to add Azure CLI login user as database server Microsoft Entra administrator

Permission	Operation
`Microsoft.DBforMySQL/flexibleServers/read`	Required to get information of database server
`Microsoft.DBforMySQL/flexibleServers/write`	Required to add the provided User assigned managed identity to database server
`Microsoft.DBforMySQL/flexibleServers/firewallRules/write`	Required to create firewall rule in case the local IP address is blocked
`Microsoft.DBforMySQL/flexibleServers/firewallRules/delete`	Required to revert the firewall rule created by Service Connector to avoid security issue
`Microsoft.DBforMySQL/flexibleServers/administrators/read`	Required to check if Azure CLI login user is a database server Microsoft Entra administrator
`Microsoft.DBforMySQL/flexibleServers/administrators/write`	Required to add Azure CLI login user as database server Microsoft Entra administrator

Permission	Operation
`Microsoft.Sql/servers/read`	Required to get information of database server
`Microsoft.Sql/servers/firewallRules/write`	Required to create firewall rule in case the local IP address is blocked
`Microsoft.Sql/servers/firewallRules/delete`	Required to revert the firewall rule created by Service Connector to avoid security issue
`Microsoft.Sql/servers/administrators/read`	Required to check if Azure CLI login user is a database server Microsoft Entra administrator
`Microsoft.Sql/servers/administrators/write`	Required to add Azure CLI login user as database server Microsoft Entra administrator

In some cases, the permissions aren't required. For example, if the Azure CLI-authenticated user is already an Active Directory Administrator on SQL server, you don't need to have the Microsoft.Sql/servers/administrators/write permission.

Microsoft Entra ID

If you get an error ERROR: AADSTS530003: Your device is required to be managed to access this resource., ask your IT department for help with joining this device to Microsoft Entra ID. For more information, see Microsoft Entra joined devices.

Service Connector needs to access Microsoft Entra ID to get information of your account and managed identity of hosting service. You can use the following command to check if your device can access Microsoft Entra ID:

az ad signed-in-user show

If you don't log in interactively, you might also get the error and Interactive authentication is needed. To resolve the error, log in with the az login command.

Network connectivity

If your database server is in Virtual Network, ensure your environment that runs the Azure CLI command can access the server in the Virtual Network.

If your database server disallows public access, ensure your environment that runs the Azure CLI command can access the server through the private endpoint.

Next steps

For more information about Service Connector and passwordless connections, see the following resources:

Service Connector documentation

Passwordless connections for Azure services

Tutorial: Create a passwordless connection to a database service via Service Connector

Prerequisites

Set up your environment

Account

Install the Service Connector passwordless extension

Create a passwordless connection

Connect to a database with Microsoft Entra authentication

Deploy the application to an Azure hosting service

Troubleshooting

Permission

Microsoft Entra ID

Network connectivity

Next steps

Feedback

Additional resources