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

Question

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.

Answer 1

You can use cat /etc/odbcinst.ini command to identify the odbc driver details.

root@vm1: cat /etc/odbcinst.ini
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.3.so.2.1
UsageCount=1

I also faced same issue, I realized the SQL driver version is 18. I updated the driver details and it worked like a charm.

Note : You can use odbcinst -j to find the odbc installation source details.

root@vm1:odbcinst -j
unixODBC 2.3.11
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources
USER DATA SOURCES..: /root/.odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

Answer 2

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.

Now that python 3.11 is fully supported for azure functions, I've discovered that they come with "ODBC Driver 18 for SQL Server" installed, instead of the version 17 which is installed on python 3.10 azure functions. So in your conn_str, you need to replace 17 with 18 in order for it to work on python 3.11.

Answer 3

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.

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

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