Azure Function pyODBC Error: MAX_PROVS Issue When Connecting to SQL Server

I am working on a Python script to retrieve, manipulate, and insert data into a SQL database. The script works perfectly on my local machine, but when deployed to Azure, I encounter the following error:

Result: Failure
Exception: Error: ('HY000', '[HY000] [Microsoft][ODBC Driver 17 for SQL Server]MAX_PROVS: Error code 0x57 (87) (SQLDriverConnect)')
Stack: 
File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/dispatcher.py", line 313, in _handle__invocation_request
call_result = await self._loop.run_in_executor(
File "/usr/local/lib/python3.8/concurrent/futures/thread.py", line 57, in run
result = self.fn(*self.args, **self.kwargs)
File "/azure-functions-host/workers/python/3.8/LINUX/X64/azure_functions_worker/dispatcher.py", line 434, in __run_sync_func
return func(**params)
File "/home/site/wwwroot/HttpExample/init.py", line 157, in main
conn = pyodbc.connect(con_str)

Solution

The MAX_PROVS error in Azure Functions occurs due to missing ODBC drivers or misconfiguration. Follow these steps:

1- Install ODBC Driver: Ensure the ODBC Driver 17 for SQL Server is installed. Add the following in a startup

apt-get update
apt-get install -y apt-transport-https curl
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/20.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
apt-get update
ACCEPT_EULA=Y apt-get install -y msodbcsql17 unixodbc-dev

2- Verify Driver Installation:

import pyodbc
print(pyodbc.drivers())

3- Connection String: Use the correct format:

con_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=<server>;DATABASE=<database>;"
    "UID=<username>;PWD=<password>"
)
conn = pyodbc.connect(con_str)

4- Environment Variables: Set ODBCSYSINI to /etc/odbc.

5- Increase Memory: Allocate more memory to your Azure Function.

6- Enable Managed Identity: Use Azure AD authentication:

con_str = (
    "DRIVER={ODBC Driver 17 for SQL Server};"
    "SERVER=<server>.database.windows.net,1433;"
    "DATABASE=<database>;Authentication=ActiveDirectoryMsi;"
)

7- Firewall Rules: Add Function App outbound IPs to Azure SQL firewall.