接下來，如果您在使用服務連接器之前已在 PostgreSQL 彈性伺服器中建立資料表和序列，則必須以擁有者身分連線，並將權限授與 Service Connector 所建立的 <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)
在程式碼中，從 Service Connector 服務新增的環境變數中，透過 @azure/identity 和 PostgreSQL 連線資訊取得存取權杖。 結合它們以建立連線。 使用下列程式代碼時，請確保您取消註解您想要使用的對應至驗證類型代碼段部分。
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();
})();
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。
