接下来，如果你在使用服务连接器之前已经在 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 Microsoft.Data.SqlClient;
// AZURE_SQL_CONNECTIONSTRING should be one of the following:
// For system-assigned managed identity:"Server=tcp:<server-name>.database.windows.net;Database=<database-name>;Authentication=Active Directory Default;TrustServerCertificate=True"
// For user-assigned managed identity: "Server=tcp:<server-name>.database.windows.net;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();
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)
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.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
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.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 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.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 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.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();
})();
此代码示例使用 DefaultAzureCredential 从 Microsoft Entra ID 为 SQL 数据库获取可使用的令牌，然后将其添加到数据库连接。 虽然可以自定义 DefaultAzureCredential，但默认情况下已广泛适用。 它从已登录的 Microsoft Entra 用户或托管标识获取令牌，具体取决于是在开发环境中还是在应用服务本地运行。
无需进一步更改，代码即可在 Azure 中运行。 但是，若要在本地调试代码，开发环境需要已登录 Microsoft Entra 的用户。 在此步骤中，你将以 Microsoft Entra 用户身份登录来配置所选环境。
Visual Studio for Windows 集成了 Microsoft Entra 身份验证。 若要在 Visual Studio 中启用开发和调试，请在 Visual Studio 中添加 Microsoft Entra 用户，方法是从菜单中依次选择“文件”>“帐户设置”，然后选择“登录”或“添加”。
若要设置进行 Azure 服务身份验证的 Microsoft Entra 用户，请从菜单中依次选择“工具”>“选项”，然后依次选择“Azure 服务身份验证”>“帐户选择”。 选择已添加的 Microsoft Entra 用户，然后选择“确定”。
Visual Studio for Mac 未集成 Microsoft Entra 身份验证。 但是，稍后将使用的 Azure 标识客户端库可以使用来自 Azure CLI 的令牌。 若要在 Visual Studio 中启用开发和调试，请在本地计算机上安装 Azure CLI。
以 Microsoft Entra 用户身份通过以下命令登录到 Azure CLI：
az login --allow-no-subscriptions
Visual Studio Code 已通过 Azure 扩展与 Microsoft Entra 身份验证集成。 在 Visual Studio Code 中安装 Azure Tools 扩展。
如果需要，可将该标识添加到 Microsoft Entra 组，然后将访问权限授予 Microsoft Entra 组，而不是授予该标识。 例如，以下命令将上一步中的托管标识添加到名为 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
若要为 Microsoft Entra 组授予数据库权限，请参阅相应数据库类型的文档。
我收到错误 SSL connection is required. Please specify SSL options and retry。
