Python使用Managed Identity免密连接SQL Server

1:Prerequisites

1):Create Managed Identity
Name: msi-vm-01
Object ID: 476f4bc2-32f0-4e3d-8280-99de7745d159
 
2):Assign Managed Identity to VM
 
3):Grant db role to Managed Identity
CREATE USER "msi-vm-01" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "msi-vm-01";
ALTER ROLE db_datawriter ADD MEMBER "msi-vm-01";
2:Python Environment Set Up

pip install pyodbc
pip install azure-identity

3:Connect to SQL Server using Python Demo

from azure.identity import DefaultAzureCredential
import pyodbc, struct

#create credential with user-assigned identity
credential = DefaultAzureCredential(managed_identity_client_id='476f4bc2-xxxx-4e3d-8280-9xxxxxx') 

#get token
token = credential.get_token("https://database.windows.net/.default").token.encode("UTF-16-LE")
token_struct = struct.pack(f'<I{len(token)}s', len(token), token)

# Connect SQL Server with the token
SQL_COPT_SS_ACCESS_TOKEN = 1256
server_name="xxxx"
database_name="xxxxx"
connString = f"Driver={{ODBC Driver 17 for SQL Server}};SERVER="+server_name+".database.windows.net;DATABASE="+database_name
conn = pyodbc.connect(connString, attrs_before={SQL_COPT_SS_ACCESS_TOKEN: token_struct})
cursor = conn.cursor()

#execute sql
sql_command="SELECT TOP (1000) [EntityID],[Name],[BaseCurrency],[BU] FROM [dbo].[tblEntities]"
cursor.execute(sql_command)

rows = cursor.fetchall()
for row in rows:
    print(row)