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

user_10752415 0
Viewed 1136

I've been tasked with troubleshooting why an Azure function executing Python is returning 500. I'm brand new Azure and could use some help. From what I can tell thus far the function is failing to connect to the database and I'm unsure why.

Connection Function:

def get_connection(location):
    
    # change directory
    os.getcwd()
    location = location
    os.chdir(location)
    
    # config.DATABASE_CONFIG['driver'] == 'ODBC Driver 17 for SQL Server'
    connection_string = 'DRIVER=' + config.DATABASE_CONFIG['driver'] + \
                        ';PORT=1433' + \
                        ';SERVER=' + config.DATABASE_CONFIG['server'] + \
                        ';PORT=1443' + \
                        ';DATABASE=' + config.DATABASE_CONFIG['database'] + \
                        ';UID=' + config.DATABASE_CONFIG['username'] + \
                        ';PWD=' + config.DATABASE_CONFIG['password'] 

    sql_conn = pyodbc.connect(connection_string, autocommit=True)
    
    return sql_conn

Stack Trace:

Executing 'Functions.runMetricsHTTP' (Reason='This function was programmatically called via the host APIs.', Id=21ca7ca1-d48a-4282-bcdb-3ed65fa91ff9)
Information
2021-08-12 16:27:26.227
Python HTTP trigger function processed a request.
Information

Error running the process at 2021-08-12T16:27:26.218518+00:00 Traceback (most recent call last): File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 31, in execute return_dict = main.main() File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/main.py", line 19, in main sql_conn = setup.get_connection(".") File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/setup.py", line 21, in get_connection sql_conn = pyodbc.connect(connection_string, autocommit=True) pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Error

Result: Failure Exception: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") Stack: File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 345, in _handle__invocation_request self.__run_sync_func, invocation_id, fi.func, args) File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run result = self.fn(*self.args, **self.kwargs) File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 480, in __run_sync_func return func(**params) File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 19, in entrypoint execute(logging) File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 52, in execute raise e File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 31, in execute return_dict = main.main() File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/main.py", line 19, in main sql_conn = setup.get_connection(".") File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/setup.py", line 21, in get_connection sql_conn = pyodbc.connect(connection_string, autocommit=True)
Error

Executed 'Functions.runMetricsHTTP' (Failed, Id=21ca7ca1-d48a-4282-bcdb-3ed65fa91ff9, Duration=42ms)
Error

Result: Failure Exception: Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)") Stack: File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 345, in _handle__invocation_request self.__run_sync_func, invocation_id, fi.func, args) File "/usr/local/lib/python3.7/concurrent/futures/thread.py", line 57, in run result = self.fn(*self.args, **self.kwargs) File "/azure-functions-host/workers/python/3.7/LINUX/X64/azure_functions_worker/dispatcher.py", line 480, in __run_sync_func return func(**params) File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 19, in entrypoint execute(logging) File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 52, in execute raise e File "/home/site/wwwroot/runMetricsHTTP/__init__.py", line 31, in execute return_dict = main.main() File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/main.py", line 19, in main sql_conn = setup.get_connection(".") File "/usr/local/lib/python3.7/site-packages/gp_portfolio_metrics-0.0.1-py3.7.egg/gp_portfolio_metrics/metrics/setup.py", line 21, in get_connection sql_conn = pyodbc.connect(connection_string, autocommit=True)
azure-functions azure sql-server python
1 Answers

could you try changing the driver part in the connection string to the following: connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};' + \

see also this post: Linux Open Suse "pyodbc.Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'SQL Server' : file not found (0) (SQLDriverConnect)")"

best practice is not to create your connection string in the code but set it in the application settings - and local setting for local dev. https://docs.microsoft.com/en-us/azure/azure-functions/functions-how-to-use-azure-function-app-settings?tabs=portal

you can set the the connection string like this connection_string = os.environ["connectionstringname"] (where connectionstringname is the name of the connectionstring defined in application our local settings)

user_71191670
Related