使用带有 ActiveDirectoryMSI 身份验证的 Python 的 Azure 函数连接

Question

我正在尝试使用 ActiveDirectoryMSI 身份验证从用于 python 的 Azure 函数连接 Azure SQL 数据库。

请检查以下代码：-

import logging

from . import hy_param

import sys

import pyodbc

import azure.functions as func

def 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 函数连接 Azure、SQL 数据库？

Answer 1

您可以尝试使用下面的代码使用 MSI 访问令牌连接到您的 Azure SQL（在运行此代码之前，请确保您的功能 MSI 已启用并且它有权访问您的 Azure SQL）：import loggingimport osimport azure.functions as funcimport pyodbcimport requests import structmsi_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))请使用您赢得的服务器名称和数据库名称编辑连接字符串这是我这边的测试结果：

Answer 2

使用 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 databaseALTER 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 loggingimport azure.functions as func# Sql driverimport pyodbcdef main(req: func.HttpRequest) -> func.HttpResponse:&nbsp; &nbsp; try:&nbsp; &nbsp; &nbsp; &nbsp; logging.info('Python HTTP trigger function processed a request.')&nbsp; &nbsp; &nbsp; &nbsp; # Connecting to Azure SQl the standard way&nbsp; &nbsp; &nbsp; &nbsp; server = 'tcp:<servername>.database.windows.net'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; database = '<dbname>'&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; driver = '{ODBC Driver 17 for SQL Server}' # Driver 13 did not work for me&nbsp; &nbsp; &nbsp; &nbsp; with pyodbc.connect(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; "Driver="&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + driver&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ";Server="&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + server&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ";PORT=1433;Database="&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + database&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; + ";Authentication=ActiveDirectoryMsi", # This is important :)&nbsp; &nbsp; &nbsp; &nbsp; ) as conn:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; logging.info("Successful connection to database")&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; with conn.cursor() as cursor:&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; #Sample select query&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; cursor.execute("SELECT Name FROM People;")&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; peopleNames = ''&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row = cursor.fetchone()&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; while row:&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; peopleNames += str(row[0]).strip() + " "&nbsp;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row = cursor.fetchone()&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return func.HttpResponse(f"Hello {peopleNames}!")&nbsp; &nbsp; except Exception as e:&nbsp; &nbsp; &nbsp; &nbsp; return func.HttpResponse(str(e))这里有一个完整的项目，您可以作为示例：https ://github.com/crgarcia12/azure-function-msi-python