服务连接器可在 Azure Spring Apps、Azure 应用服务和 Azure 容器应用等应用托管服务中启用托管标识。 服务连接器还可将数据库服务（例如 Azure Database for PostgreSQL、Azure Database for MySQL 和 Azure SQL 数据库）配置为接受托管标识。
GRANT ALL PRIVILEGES ON DATABASE "$DATABASE_NAME" TO "username";
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO "username";
GRANT ALL PRIVILEGES ON ALL SEQUENCES IN SCHEMA public TO "username";
GRANT ALL PRIVILEGES ON $DATABASE_NAME.* TO 'username'@'%';
GRANT CONTROL ON DATABASE::"$DATABASE_NAME" TO "username";
通过 Microsoft Entra 身份验证连接到数据库
创建连接后，可以使用应用程序中的连接字符串通过 Microsoft Entra 身份验证连接到数据库。 例如，可使用以下解决方案通过 Microsoft Entra 身份验证连接到数据库。
using Azure.Identity;
using Azure.Core;
using Npgsql;
// Uncomment the following lines corresponding to the authentication type you want to use.
// 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();
}
from azure.identity import DefaultAzureCredential
import psycopg2
# Uncomment the following lines corresponding to the authentication type you want to use.
# 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)
import { DefaultAzureCredential, ClientSecretCredential } from "@azure/identity";
const { Client } = require('pg');
// Uncomment the following lines corresponding to the authentication type you want to use.
// 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();
})();
对于 PHP，没有用于无密码连接的插件或库。 可以获取托管标识或服务主体的访问令牌，并将其用作连接到数据库的密码。 可使用 Azure REST API 获取访问令牌。
有关服务主体，请参阅 Azure AD 服务到服务访问令牌请求，来查看如何获取访问令牌的详细信息。 发出范围为 https://ossrdbms-aad.database.windows.net/.default 的 POST 请求，并使用服务连接器添加的环境变量中的服务主体的租户 ID、客户端 ID 和客户端密码。
接下来，如果你在使用服务连接器之前已经在 PostgreSQL 灵活服务器中创建了表和序列，则你需要以所有者身份进行连接，并向服务连接器创建的 <aad-username> 授予权限。 服务连接器设置的连接字符串或配置中的用户名应类似于 aad_<connection name>。 如果使用 Azure 门户，请选择 Service Type 列旁边的展开按钮并获取值。 如果使用 Azure CLI，请检查 CLI 命令输出中的 configurations。
然后，执行用于授予权限的查询。
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>\";"
using Azure.Core;
using Azure.Identity;
using MySqlConnector;
// Uncomment the following lines corresponding to the authentication type you want to use.
// 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
from azure.identity import ManagedIdentityCredential, ClientSecretCredential
import os
# Uncomment the following lines corresponding to the authentication type you want to use.
# 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')
在设置文件中，从服务连接器服务添加的环境变量中获取 Azure MySQL 数据库信息。 使用上一步中获取的 accessToken 来访问数据库。
# 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
}
}
安装依赖项。
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"
using Microsoft.Data.SqlClient;
string connectionString =
Environment.GetEnvironmentVariable("AZURE_SQL_CONNECTIONSTRING")!;
using var connection = new SqlConnection(connectionString);
connection.Open();
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();
}
}
}
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 corresponding to the authentication type you want to use.
# 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)
需要此权限才能将 Azure CLI 登录用户添加为数据库服务器 Microsoft Entra 管理员
权限
操作
Microsoft.Sql/servers/read
需要此权限才能获取数据库服务器信息
Microsoft.Sql/servers/firewallRules/write
需要此权限才能创建防火墙规则以防本地 IP 地址被阻止
Microsoft.Sql/servers/firewallRules/delete
需要此权限才能还原服务连接器创建的防火墙规则以避免安全问题
Microsoft.Sql/servers/administrators/read
需要此权限才能检查 Azure CLI 登录用户是否为数据库服务器 Microsoft Entra 管理员
Microsoft.Sql/servers/administrators/write
需要此权限才能将 Azure CLI 登录用户添加为数据库服务器 Microsoft Entra 管理员
在某些情况下，不需要权限。 例如，如果经过 Azure CLI 身份验证的用户已经是 SQL Server 上的 Active Directory 管理员，则无需具有 Microsoft.Sql/servers/administrators/write 权限。
Microsoft Entra ID
如果收到 ERROR: AADSTS530003: Your device is required to be managed to access this resource. 错误，请向 IT 部门寻求将此设备加入 Microsoft Entra ID 的帮助。 有关详细信息，请参阅已建立 Microsoft Entra 联接的设备。
服务连接器需要访问 Microsoft Entra ID 才能获取帐户的信息以及托管服务的托管标识。 可使用以下命令检查设备是否能够访问 Microsoft Entra ID：
az ad signed-in-user show
如果无法以交互方式登录，你可能还会收到错误和 Interactive authentication is needed。 若要解决此错误，请使用 az login 命令进行登录。