我正在嘗試使用 ActiveDirectoryMSI 身份驗證從用于 python 的 Azure 函數(shù)連接 Azure SQL 數(shù)據(jù)庫。請檢查以下代碼：-import loggingfrom . import hy_paramimport sysimport pyodbcimport azure.functions as funcdef main(req: func.HttpRequest) -> func.HttpResponse: logging.info('Python HTTP trigger function processed a request.') try: connection = pyodbc.connect('driver={%s};server=%s;database=%s;Authentication=ActiveDirectoryMSI' % (hy_param.sql_driver, hy_param.server_name, hy_param.database_name)) sql_db = connection.cursor() logging.info("MSSQL Database Connected") except Exception as e: return func.HttpResponse(f"Error in sql database connection : {e}", status_code=400) sys.exit() return func.HttpResponse( "Database Connected", status_code=200 )請檢查以下錯誤：-Error in sql database connection : ('08001', '[08001] [Microsoft][ODBC Driver 17 for SQL Server]SSL Provider: [error:1416F086:SSL routines:tls_process_server_certificate:certificate verify failed:self signed certificate] (-1) (SQLDriverConnect)')有什么方法可以使用 ActiveDirectoryMSI 從 Azure 函數(shù)連接 Azure、SQL 數(shù)據(jù)庫？
2 回答
千萬里不及你
TA貢獻1784條經(jīng)驗 獲得超9個贊
您可以嘗試使用下面的代碼使用 MSI 訪問令牌連接到您的 Azure SQL（在運行此代碼之前，請確保您的功能 MSI 已啟用并且它有權(quán)訪問您的 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))
請使用您贏得的服務器名稱和數(shù)據(jù)庫名稱編輯連接字符串
這是我這邊的測試結(jié)果：
拉丁的傳說
TA貢獻1789條經(jīng)驗 獲得超8個贊
使用 SDK 和 ODBC 驅(qū)動程序直接連接到 Azure SQL 有一種更好的新方法。
你需要：
啟用 Azure 函數(shù)托管服務標識 (MSI)
為 Azure SQL Server 啟用 AAD 集成
將 Azure Function MSI 用戶添加到數(shù)據(jù)庫
在
Authentication=ActiveDirectoryMsi你的pyodbc.connect.
要將 MSI 用戶添加到數(shù)據(jù)庫，您必須使用 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 函數(shù)名稱。您也可以 Get-AzureADObjectByObjectId -ObjectIds在 PowerShell 中使用它
這是一個 hello-world 函數(shù)的源代碼：
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
添加回答
舉報
0/150
提交
取消