Azure Functions: Can't open lib 'ODBC Driver 17 for SQL Server' – Azure

The Solutions:

Solution 1: Switch to Python 3.10 or Update ODBC Driver

When deploying a Python 3.11 Azure function, Azure Functions core tools install "ODBC Driver 18 for SQL Server." However, the given code uses "ODBC Driver 17 for SQL Server." To resolve this issue:

• Python 3.10: Switch to Python 3.10, which installs "ODBC Driver 17 for SQL Server."
• Python 3.11: Update the connection string in conn_str to use "ODBC Driver 18 for SQL Server" instead of "ODBC Driver 17 for SQL Server."

Solution 2: Use pyodbc with Azure Functions

Azure Functions Python environment comes with the pyodbc module pre-installed. To ensure successful connection, include pyodbc in your requirements.txt file.

Refer to the following code示例:

init.py:

import logging
import pyodbc
import azure.functions as func

def main(req: func.HttpRequest) -> func.HttpResponse:
    logging.info('Python HTTP trigger function processed a request.')

    connectionstring = os.environ["connectionstring"]
    conn = pyodbc.connect(connectionstring)
    cursor = conn.cursor()

    cursor.execute("SELECT * FROM StudentReviews")
    conn.commit()

    conn.commit()
    cursor.close()
    conn.close()

    # Prepare & Return the HTTP Response
    return func.HttpResponse(
        body="Your request is processed",
        status_code=202
    )

requirements.txt:

azure-functions
pyodbc

local.settings.json:

{
  "IsEncrypted": false,
  "Values": {
    "AzureWebJobsStorage": "DefaultEndpointsProtocol=https;AccountName=siliconrg8c29;AccountKey=xxxxxxxxxqvo9mCwMuHlTpFk5yzn/Wk/bu3Wy1rxlxxxxx==;EndpointSuffix=core.windows.net",
    "FUNCTIONS_WORKER_RUNTIME": "python",
    "connectionstring": "DRIVER={ODBC Driver 17 for SQL Server};SERVER=tcp:sqlserver.database.windows.net;PORT=1433;DATABASE=silicondb;UID=username;PWD=Password"
  }
}

Deploy the function code with the following commands:

az login
az account set --subscription "SID Subscription"
func azure functionapp publish siliconfunc430

Ensure that the Function app has the "connectionstring" setting configured. Additionally, verify that the Function outbound IPs are whitelisted in your SQL server.

If you encounter any issues, consider deploying your Function app in a dedicated plan (Premium or App Service plan) and manually installing the ODBC Driver using the commands provided in the documentation.

Solution 3: Check ODBC Driver Version

To troubleshoot the error related to opening the "ODBC Driver 17 for SQL Server" library, you can verify the installed ODBC driver version on the Azure Function VM.

Run the following command to display the ODBC driver details:

cat /etc/odbcinst.ini

If the driver version is not 17, it’s possible that the connection string in your Python script is referencing an incorrect version. Update the connection string to use the correct driver version.

You can also use the following command to obtain ODBC installation source details:

odbcinst -j

This will provide information about the installed ODBC drivers and their locations. Ensure that the required driver is installed and configured correctly.

Video Explanation: