如何用Python连接/查询Azure SQL Always Encrypted并操作加密列表

Alright, let's tackle this problem with Azure SQL's encrypted columns (I'm assuming you're using Always Encrypted here—Azure's primary column-level encryption feature). The regular pyodbc connection you're using doesn't handle encryption/decryption automatically, so we need to adjust a few key settings to make this work.

1. Use a Compatible ODBC Driver First #

You'll need the ODBC Driver 17 for SQL Server (or newer)—older versions don't support Always Encrypted. Verify your available drivers with this quick check:

import pyodbc
print(pyodbc.drivers())

Look for {ODBC Driver 17 for SQL Server} in the output; that's what we'll use for the connection.

2. Update the Connection String for Encryption #

The core change is adding ColumnEncryption=Enabled to your connection string. If your encryption keys are stored in Azure Key Vault (the most common production setup), you'll also need to configure authentication for Key Vault access.

Option A: Azure Key Vault Integration (Recommended) #

First, install required packages:

pip install pyodbc azure-identity

Then modify your connection code to authenticate with Key Vault:

import pyodbc
from azure.identity import DefaultAzureCredential

# Your Azure SQL details
driver = '{ODBC Driver 17 for SQL Server}'
server = 'your-server-name.database.windows.net'
database = 'your-db-name'
username = 'your-username'
password = 'your-password'

# Get a credential for Key Vault access (uses environment variables, managed identity, or local Azure CLI auth)
credential = DefaultAzureCredential()
kv_token = credential.get_token("https://vault.azure.net/.default").token

# Establish connection with column encryption enabled
cnxn = pyodbc.connect(
    f'DRIVER={driver};SERVER={server};PORT=1443;DATABASE={database};UID={username};PWD={password}',
    attrs_before={
        pyodbc.SQL_COPT_SS_COLUMN_ENCRYPTION: pyodbc.SQL_COLUMN_ENCRYPTION_ENABLED,
        pyodbc.SQL_COPT_SS_KEY_STORE_AUTH: pyodbc.SQL_KEY_STORE_AUTH_AZURE_KEY_VAULT,
        pyodbc.SQL_COPT_SS_KEY_STORE_PRINCIPAL_ID: kv_token
    }
)

Option B: Local Key Store (For Testing) #

If your encryption keys are stored locally (e.g., a certificate file on your machine), you can simplify the connection string:

cnxn = pyodbc.connect(
    f'DRIVER={driver};SERVER={server};PORT=1443;DATABASE={database};UID={username};PWD={password};ColumnEncryption=Enabled'
)

Just make sure the required certificate is installed on the machine running your code.

3. Writing Data to the Encrypted Column #

Critical Rule: Always use parameterized queries—never concatenate values directly into SQL strings. Always Encrypted requires this because the driver needs to encrypt values before sending them to the database.

Example insert code:

cursor = cnxn.cursor()

# Parameterized query (the driver auto-encrypts the 'value' parameter)
insert_query = "INSERT INTO your_table (id, value) VALUES (?, ?)"
cursor.execute(insert_query, (1, "your-sensitive-data-here"))

cnxn.commit()
cursor.close()

4. Reading Data from the Encrypted Column #

Reading is seamless once the connection is configured correctly—the driver automatically decrypts the value column when you fetch results:

cursor = cnxn.cursor()

select_query = "SELECT id, value FROM your_table WHERE id = ?"
cursor.execute(select_query, (1,))

row = cursor.fetchone()
if row:
    print(f"ID: {row[0]}, Decrypted Value: {row[1]}")  # row[1] is already decrypted

cursor.close()
cnxn.close()

Key Tips to Avoid Headaches #

• Parameterized Queries Are Non-Negotiable: String concatenation will throw errors because the driver can't intercept and encrypt raw values.
• Key Vault Permissions: Ensure your Azure AD identity has the Key Vault Crypto User role—this lets it access the encryption keys for decryption/encryption.
• Double-Check Driver Version: Outdated drivers are the #1 cause of Always Encrypted failures. Use the pyodbc.drivers() check to confirm you're on v17+.

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