While running an Azure ML Experiement, I get "File Not found" error when attempting to find ODBC driver for python pyodbc.connect command

Question

Hello:

This is the python command. It works fine in my local Windows environment and I can connect to the remote Azure SQL DB that I want to connect to.

sql_conn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server}; ...

However, when I run my az ml submit-run CLI command in order to run an experiment in Azure Machine Learning, I get this error.:

[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

So, it seems that AML is running its containers in Linux. Ok....I have no problem with that, but how do I configure either my

Answer 1

@DanWellisch-1648 Are you using a Jupyter notebook from Azure ML portal to run the commands to connect to a SQL DB? If Yes, you can install the required library on your notebook VM and try to connect again.

In this case you should have enabled SSH access to your compute instance to login and install the required library.

=>Enable SSH access

=>Login to the terminal of the instance using the link after the compute is created. Install the library if not available already

     sudo dpkg -l|grep msodbcsql # If this command return blank the you know that the driver indeed is missing on your server and that you need to install it
                            
    #And to install this driver you can run the following:
         
    sudo ACCEPT_EULA=Y apt-get install msodbcsql17

=>The following files should be available which should help to configure and use the drivers.

• First a new directory located in /opt/microsoft/msodbcsql17/ this is the directory where the libraries are installed
• Second a file in /etc/ called odbcinst.ini that will host the paths for the libraries and that python will need
• Third the main library that is hosted in/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.1.1 and this library should have all the dynamic libraries attached by default.

You can then run the experiment again to check if the connection works.

Answer 2

Hello:

I am NOT running from a Jupiter Notebook. I am running on my laptop with the az ml submit-run CLI command which specifies my python code, run config, .yml file.

But, if I create an Azure notebook while logged in to the portal, and run through the steps you mentioned, that would configure my compute for me and my AZ ML Submit-Run CLI command should then find it's ODBC library, correct?

Dan

Answer 3

@DanWellisch-1648 You can always from within your train script use pyodbc to access on prem SQL server as long as there’s network connectivity.
You will need to define a custom docker image to configure the driver if you use on demand AML compute. It’s easier to use VM:

See this: https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-SQL-Server-from-Linux

or