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 來取得該存取權杖。
在程式碼中，使用 REST API 搭配您慣用的程式庫來取得存取權杖。
針對使用者指派的身分識別和系統指派的身分識別，App Service 和容器應用程式提供可從內部存取的 REST 端點來擷取受控識別的權杖，方法是定義兩個環境變數：IDENTITY_ENDPOINT 和 IDENTITY_HEADER。 如需詳細資訊，請參閱 REST 端點參考 (部分機器翻譯)。
對身分識別端點提出 HTTP GET 要求，並在查詢中使用 https://ossrdbms-aad.database.windows.net 作為 resource，來取得存取權杖。 針對使用者指派的身分識別，請同時在查詢中包括服務連接器新增之環境變數的用戶端識別碼。
針對服務主體，請參閱 Azure AD 服務對服務存取權杖要求以查看如何取得存取權杖的詳細資料。 將 POST 要求的範圍設定為 https://ossrdbms-aad.database.windows.net/.default，並搭配服務連接器新增之環境變數中的服務主體租用戶識別碼、用戶端識別碼和用戶端密碼。
接下來，如果您在使用服務連接器之前已在 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 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 驗證的使用者已經是 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 識別碼。 如需詳細資訊，請參閱 Microsoft已加入 Entra 的裝置。
服務連接器必須存取 Microsoft Entra ID，才能取得您的帳戶和裝載服務的受控識別資訊。 您可以使用下列命令來檢查您的裝置是否可以存取 Microsoft Entra ID：
az ad signed-in-user show
如果您未以互動方式登入，也可能會收到錯誤 和 Interactive authentication is needed。 若要解決錯誤，請使用 az login 命令登入。