question

DanWellisch-1648 avatar image
0 Votes"
DanWellisch-1648 asked DanWellisch-1648 commented

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

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 <filename>.runconfig or my .yaml file to tell the Linux machine where to find the driver?

I can't really connect to a virtual machine and install this driver if I use AML, right? It's needs to be done in one of the aforementioned files, right?

Dan


azure-sql-databaseazure-machine-learning
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

romungi-MSFT avatar image
0 Votes"
romungi-MSFT answered DanWellisch-1648 commented

@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


11999-enable-ssh-access.jpg



=>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.





    · 1
    5 |1600 characters needed characters left characters exceeded

    Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

    Hello:

    What are the extra parameters to "az ml computetarget create amlcompute" cli command in order to enable me to ssh into the compute once created?

    0 Votes 0 ·
    DanWellisch-1648 avatar image
    0 Votes"
    DanWellisch-1648 answered

    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

    5 |1600 characters needed characters left characters exceeded

    Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

    ramr-msft avatar image
    0 Votes"
    ramr-msft answered

    @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
    12041-screenshot-172.png




    screenshot-172.png (35.0 KiB)
    5 |1600 characters needed characters left characters exceeded

    Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.