ODBC Driver 18 Not Found in Azure Function Using Python 3.11

Question

I'm creating an Azure Function using Python 3.11 to write data to an Azure SQL database with the pyodbc library. However, I'm running into an issue where the ODBC Driver 18 is not found. The error message I get is:

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 18 for SQL Server ' : file not found (0) (SQLDriverConnect)")

I've tried the following:

1. Switching to Python 3.10: The problem persists with ODBC Driver 18 and also occurs when using ODBC Driver 17.
2. Checking connection string: Ensured that the connection string is correctly set up for the driver.

Environment:

1. The Azure Function is running on a Linux-based plan.
2. I haven't manually installed any ODBC drivers, and I'm unsure how to do this within the Azure environment. However, I have pyodbc and azure-functions in my requirements.txt file

Has anyone encountered a similar issue or can provide guidance on setting up ODBC drivers for Azure SQL in this environment?

Thanks!

Answer 1

I created a linux based consumption plan python function with version 3.11.

I was getting error for ODBC Driver 17 for SQL Server in python 3.11, but ODBC Driver 18 for SQL Server worked perfectly fine for me.

Python 3.11 does not support ODBC Driver 17 and 3.10 does not support ODBC Driver 18. Make sure version is correct with correct Driver version.

If it still does not work in 3.11, I would suggest try using 3.12

Below given function code worked for me:

import azure.functions as func
import logging
import pyodbc


app = func.FunctionApp()


@app.route(route="http_trigger", auth_level=func.AuthLevel.ANONYMOUS)
def http_trigger(req: func.HttpRequest) -> func.HttpResponse:
      
    try:
        Connectionstring = "Driver={ODBC Driver 18 for SQL Server};Server=tcp:sqlserver31aug.database.windows.net,1433;Database=sqldb31aug;Uid={your username};Pwd={your password};Encrypt=yes;TrustServerCertificate=no;Connection Timeout=30;"
        with pyodbc.connect(Connectionstring) as conn:
            with conn.cursor() as cursor:
                cursor.execute("SELECT SYSTEM_USER;")
                result = cursor.fetchone()
                
                logging.info('Python HTTP trigger function processed a request.')
    
                logging.info("SYSTEM_USER: %s", result)

                return func.HttpResponse(f"SYSTEM_USER: {result}", status_code=200)
                
    except pyodbc.Error as e:
        logging.error(f"SQL query failed: {e}")
        return func.HttpResponse(f"SQL query failed: {e}")  

OUPUT: