3

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?

Improve this question

2 Answers 2

Reset to default
4

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 : enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

16 Comments

Thanks a ton man. I was missing permission to SQL Database. Issue is resolved. I am able to connect Azure function to SQL Server without password.
Ah , Glad to know that @LetsLearn, pls mark me if it is helpful : )
@StanleyGong I am not able to understand below two things:- 1. msi_endpoint = os.environ["MSI_ENDPOINT"] 2. msi_secret = os.environ["MSI_SECRET"] Can you please share me more details on it?
Hi @AkshayGodase , Sure , that is the process getting access token using MSI in Azure function , you can see details in this doc : learn.microsoft.com/en-us/azure/app-service/…
Hi @AkshayGodase If you enabled MSI for your function, this two variables will be defined automatically , there is no need to config it in app settings, you can get it directly . If you print this two out , you will find that is a Azure internal endpoint to get token and a secret .
|
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

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.