Error in Azure SQL Server Database connection using Azure Function for python with ActiveDirectoryMSI Authentication

Question

I am trying to connect the Azure SQL Database from Azure functions for python using ActiveDirectoryMSI Authentication.

Please check the below code:-

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
    )

Please check the below Error :-

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)')

Is there any way to connect Azure, SQL Database from Azure functions using ActiveDirectoryMSI?

Answer 1

You can try the code below to use MSI access token to connect to your Azure SQL(Before you run this code , pls make sure your function MSI has been enabled and it has permission to access your 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))

Pls edit the connection string with your won server name and db name

This is the test result on my side :

Answer 2

There is a new better way to connect to Azure SQL directly by using the SDK and ODBC driver.

You need to:

1. Enable Azure Function Managed Service Identity (MSI)
2. Enable AAD integration for Azure SQL Server
3. Add The Azure Function MSI User to the DB
4. Use Authentication=ActiveDirectoryMsi in your pyodbc.connect.

To add the MSI user to the DB you must connect using the AAD admin and then run this query:

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> is usually the Azure Function Name. You can also get it using Get-AzureADObjectByObjectId -ObjectIds in PowerShell

This is the source code of a hello-world function:

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))

Here there is a full project that you can take as example: https://github.com/crgarcia12/azure-function-msi-python