2 回答
![?](http://img1.sycdn.imooc.com/533e4c9c0001975102200220-100-100.jpg)
TA贡献1784条经验 获得超9个赞
您可以尝试使用下面的代码使用 MSI 访问令牌连接到您的 Azure SQL(在运行此代码之前,请确保您的功能 MSI 已启用并且它有权访问您的 Azure SQL):
import logging
import os
import azure.functions as func
import pyodbc
import requests
import struct
msi_endpoint = os.environ["MSI_ENDPOINT"]
msi_secret = os.environ["MSI_SECRET"]
def main(req: func.HttpRequest) -> func.HttpResponse:
token_auth_uri = f"{msi_endpoint}?resource=https%3A%2F%2Fdatabase.windows.net%2F&api-version=2017-09-01"
head_msi = {'Secret':msi_secret}
resp = requests.get(token_auth_uri, headers=head_msi)
access_token = resp.json()['access_token']
accessToken = bytes(access_token, 'utf-8');
exptoken = b"";
for i in accessToken:
exptoken += bytes({i});
exptoken += bytes(1);
tokenstruct = struct.pack("=i", len(exptoken)) + exptoken;
conn = pyodbc.connect("Driver={ODBC Driver 17 for SQL Server};Server=tcp:andyserver.database.windows.net,1433;Database=database2", attrs_before = { 1256:bytearray(tokenstruct) });
cursor = conn.cursor()
cursor.execute("select @@version")
row = cursor.fetchall()
return func.HttpResponse(str(row))
请使用您赢得的服务器名称和数据库名称编辑连接字符串
这是我这边的测试结果:
![?](http://img1.sycdn.imooc.com/5458478b0001f01502200220-100-100.jpg)
TA贡献1789条经验 获得超8个赞
使用 SDK 和 ODBC 驱动程序直接连接到 Azure SQL 有一种更好的新方法。
你需要:
启用 Azure 函数托管服务标识 (MSI)
为 Azure SQL Server 启用 AAD 集成
将 Azure Function MSI 用户添加到数据库
在
Authentication=ActiveDirectoryMsi
你的pyodbc.connect
.
要将 MSI 用户添加到数据库,您必须使用 AAD 管理员连接,然后运行此查询:
CREATE USER "<MSI user display name>" FROM EXTERNAL PROVIDER;
ALTER ROLE db_datareader ADD MEMBER "<MSI user display name>" -- grant permission to read to database
ALTER ROLE db_datawriter ADD MEMBER "<MSI user display name>" -- grant permission to write to database
<MSI user display name>通常是 Azure 函数名称。您也可以 Get-AzureADObjectByObjectId -ObjectIds在 PowerShell 中使用它
这是一个 hello-world 函数的源代码:
import logging
import azure.functions as func
# Sql driver
import pyodbc
def main(req: func.HttpRequest) -> func.HttpResponse:
try:
logging.info('Python HTTP trigger function processed a request.')
# Connecting to Azure SQl the standard way
server = 'tcp:<servername>.database.windows.net'
database = '<dbname>'
driver = '{ODBC Driver 17 for SQL Server}' # Driver 13 did not work for me
with pyodbc.connect(
"Driver="
+ driver
+ ";Server="
+ server
+ ";PORT=1433;Database="
+ database
+ ";Authentication=ActiveDirectoryMsi", # This is important :)
) as conn:
logging.info("Successful connection to database")
with conn.cursor() as cursor:
#Sample select query
cursor.execute("SELECT Name FROM People;")
peopleNames = ''
row = cursor.fetchone()
while row:
peopleNames += str(row[0]).strip() + " "
row = cursor.fetchone()
return func.HttpResponse(f"Hello {peopleNames}!")
except Exception as e:
return func.HttpResponse(str(e))
这里有一个完整的项目,您可以作为示例:https ://github.com/crgarcia12/azure-function-msi-python
添加回答
举报