如何用Python在Synapse Notebook中通过AAD认证调用Serverless SQL Pool外部表/视图

问题背景 #

我拥有一个Serverless SQL Pool，其中包含基于ADLS Gen2 Parquet文件创建的外部表和视图。该Serverless SQL Pool采用Azure Active Directory身份认证。目前已通过Scala代码在Synapse Notebook中实现了相关调用，但需要对应的Python实现方案，通过AAD认证来调用这些外部表/视图。

已实现的Scala代码：

// Define connection:
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver")

val hostname = "demo-da-synapse-test-ondemand.sql.azuresynapse.net"
val port = 1433
val database = "Demo" // If needed, change the database 
val jdbcUrl = s"jdbc:sqlserver://${hostname}:${port};database=${database}"

// Define connection properties:
import java.util.Properties

val props = new Properties()
props.put("user", "testuser")
props.put("password", "test202XXX!")

// Assign driver to connection:
val driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver"
props.setProperty("Driver", driverClass)

val tsqlQuery ="select top 100 * from  [demo].[tblEmp]"

val df = spark.read.jdbc(jdbcUrl, s"(${tsqlQuery}) res", props)

df.show(10)

Python实现方案 #

1. AAD用户名密码认证（与Scala实现对应）

直接复用用户名密码凭证，通过JDBC连接Serverless SQL Pool：

# 配置连接参数
hostname = "demo-da-synapse-test-ondemand.sql.azuresynapse.net"
port = 1433
database = "Demo"
jdbc_url = f"jdbc:sqlserver://{hostname}:{port};database={database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;"

# 配置AAD认证属性
connection_properties = {
    "user": "testuser",
    "password": "test202XXX!",
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "authentication": "ActiveDirectoryPassword"  # 指定AAD密码认证方式
}

# 执行查询
tsql_query = "select top 100 * from [demo].[tblEmp]"
df = spark.read.jdbc(url=jdbc_url, table=f"({tsql_query}) res", properties=connection_properties)

# 查看结果
df.show(10)

2. AAD服务主体认证

使用服务主体认证时，需提前获取服务主体的客户端ID、客户端密钥和租户ID：

# 配置连接参数
hostname = "demo-da-synapse-test-ondemand.sql.azuresynapse.net"
port = 1433
database = "Demo"
jdbc_url = f"jdbc:sqlserver://{hostname}:{port};database={database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;"

# 服务主体认证参数
tenant_id = "your-tenant-id"
client_id = "your-client-id"
client_secret = "your-client-secret"

connection_properties = {
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "authentication": "ActiveDirectoryServicePrincipal",
    "user": client_id,
    "password": client_secret
}

# 执行查询
tsql_query = "select top 100 * from [demo].[tblEmp]"
df = spark.read.jdbc(url=jdbc_url, table=f"({tsql_query}) res", properties=connection_properties)

df.show(10)

3. Synapse托管身份（MSI）认证

在Synapse Notebook中可直接使用托管身份认证，无需手动配置凭证：

# 配置连接参数
hostname = "demo-da-synapse-test-ondemand.sql.azuresynapse.net"
port = 1433
database = "Demo"
jdbc_url = f"jdbc:sqlserver://{hostname}:{port};database={database};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;"

# 托管身份认证属性
connection_properties = {
    "driver": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "authentication": "ActiveDirectoryMSI"
}

# 执行查询
tsql_query = "select top 100 * from [demo].[tblEmp]"
df = spark.read.jdbc(url=jdbc_url, table=f"({tsql_query}) res", properties=connection_properties)

df.show(10)

内容的提问来源于stack exchange，提问作者Sunny