Question 228444 in Azure Question posted in
The official documentation to get you started can be found here.

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

I’ve written a Python script that connects to SQL Server housed in a Virtual Machine hosted in our Azure Environment.

I’ve been able to successfully connect and run the query locally within the Virtual Machine but when I deploy to Azure Functions I’m getting the following error:

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

I successfully ran the script and connected to the database a few days ago, but for some reason, it stopped working and this error now appears.

import pyodbc


DatabaseServer = 'Server'
DatabaseName = 'databasename'
conn_str = "Driver={ODBC Driver 17 for SQL Server };Server="+str(DatabaseServer)+';Database='+str(DatabaseName)+";'Trusted_Connection=yes;"

try:
    # Connect to the SQL Server
    conn = pyodbc.connect(conn_str)
    cursor = conn.cursor()

    # Execute the query
    cursor.execute("SELECT TOP 10 hmy FROM Table")

    # Fetch and print the results
    rows = cursor.fetchall()
    results_str = ""
    for row in rows:
        results_str += str(row) + "n"

    # Close the cursor and connection
    cursor.close()
    conn.close()
    print("Connection to SQL Server Succesful")



except pyodbc.Error as e:
    print(f"Error connecting to SQL Server {str(e)}")

Pyodbc is included in the requirements.txt file which is deployed to Azure Functions.

If somebody could help that would be great.

I believe it could be something to do with Azure functions not having the correct ODBC library but I’ve read that it is pre-installed so this shouldn’t be a problem.

Tags:

2

Answers


  1. 0

    Azure Functions Python will have PYODBC module installed by default. Make sure to add pyodbc in requirements.txt.

    I used the below code to connect and query Select statement with Azure SQL using Azure Functions and it worked successfully, Refer below:-

    My init.py:-

    import logging
from multiprocessing import connection
import pyodbc
import os
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"
  }
}

    I deployed the above function code in my Azure Function app created with Runtime set to Python 3.10 and Linux OS like below:-

    Commands to deploy the Function:-

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

    Added connectionstring setting in the configuration as settings from local.settings.json is not added in the Function app while deployment.

    enter image description here

    Make sure you check the Function Outbound Ip’s and whitelist these Ip’s in your SQL as mentioned in this SO thread answer.

    enter image description here

    enter image description here

    One alternative is to deploy your Function app in a dedicated plan, Either Premium plan or App Service plan and then run the commands from this Document to install ODBC Driver manually in your Function app.

    Commands:-

    Checked the OS version that was Debian and than ran the script from the document specific to Debian.

    cat /etc/os-release

    
sudo su curl https://packages.microsoft.com/keys/microsoft.asc |
apt-key add -

#Download appropriate package for the OS version
#Choose only ONE of the following, corresponding to your OS version

#Debian 9 curl https://packages.microsoft.com/config/debian/9/prod.list >
/etc/apt/sources.list.d/mssql-release.list

#Debian 10 curl https://packages.microsoft.com/config/debian/10/prod.list >
/etc/apt/sources.list.d/mssql-release.list

#Debian 11 curl https://packages.microsoft.com/config/debian/11/prod.list >
/etc/apt/sources.list.d/mssql-release.list

exit sudo apt-get update sudo ACCEPT_EULA=Y apt-get install -y
msodbcsql17
# optional: for bcp and sqlcmd sudo ACCEPT_EULA=Y apt-get install -y mssql-tools echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >>
~/.bashrc source ~/.bashrc
# optional: for unixODBC development headers sudo apt-get install -y unixodbc-dev
# optional: kerberos library for debian-slim distributions sudo apt-get install -y libgssapi-krb5-2

    enter image description here

    Login or Signup to reply.
  2. 0

    This happened to me when I deployed a python 3.11 azure function using azure functions core tools: func azure functionapp publish <name_of_azure_function>. When I switched back to python 3.10, the ODBC Driver 17 for SQL Server driver was installed. Support for Python 3.11 in azure functions is in public preview: https://azure.microsoft.com/en-us/updates/public-preview-support-for-python-311-in-azure-functions/

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search