topic Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun in Data Engineering

UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not found

Benni — Tue, 05 Nov 2024 21:42:37 GMT

Hey Databricks!

Trying to use the pyodbc init script in a Volume in UC on a shared compute cluster but receive error: "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)"). I followed the instructions here which seem straightforward: Shared Clusters in Unity Catalog | Databricks Blog

1) pyodbc-install.sh is uploaded in a Volume 2) the shared cluster is able to navigate to the Volume to select the init script 3) the Databricks runtime is 13.3 LTS 4) the Allowlist has been updated to allow the init script 5) the cluster spins up with no problem 6) the pyodbc==5.2.0 library has been installed (although it made no difference) but 7) it errors with "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")

Seems like it can't read or find the pyodbc file so doesn't find any drivers. Although the cluster can navigate to it.

Is it a permissions issue? What am I doing wrong?

Thanks!

Benedetta

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

agallard2 — Wed, 06 Nov 2024 16:23:55 GMT

Hi @Benni ,

Ensure that the ODBC Driver 17 for SQL Server is installed on the Databricks cluster, you can verify this by running the following command in a notebook cell on the cluster to check if the driver is available in the system:

!ls /opt/microsoft/msodbcsql17/lib64/

if the msodbcsql17 library is missing, it indicates the driver was not installed properly.

Let me know if you need further assistance!

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

Benni — Wed, 06 Nov 2024 19:14:35 GMT

Hi @agallard2

Thanks for you reply - but it's not installed on the cluster.....because it seems like it's not reading the pyodbc init script (which also has the msodbcsql17 driver in it) in the Volume in the Unity Catalog. Why can't it read the file from the Volume?. If I install the init script globally, and use a single use cluster that works. I'm trying to make a shared cluster that can connect to Azure SQL Database (among other things), and the instructions say to place the init script in a Volume when it's Unity Catalog.

Thanks!

Benedetta

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

Benni — Thu, 07 Nov 2024 17:36:50 GMT

Hey Databricks - can you help me understand why the init script in the Volume doesn't work for a shared cluster?

Thanks!

Benedetta

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

h_h_ak — Thu, 07 Nov 2024 21:38:01 GMT

May you should have a look here for global init script:

https://learn.microsoft.com/en-us/azure/databricks/init-scripts/

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

Benni — Tue, 12 Nov 2024 14:29:46 GMT

Hi h_h_ak,

Sure, sure. I've already done that. We can't use global init scripts with Shared Clusters and UC. Seems it has to be installed in Volume or cloud storage. How come mu shared cluster can't read the pyodbc script in the volume?

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

praveenVP — Wed, 26 Feb 2025 16:08:42 GMT

Hi all,

I am also facing similar issue . is there any working alternate solution ?

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

Benni — Wed, 26 Feb 2025 16:56:44 GMT

Hi,

Yes! A workaround we discovered is to wrap the pyodbc script in some error handling code. Our particular script was throwing some exceptions/outputs which were ignored by Databricks in a notebook, as well as when installed as a global init script. Apparently....the cluster could not ignore the exceptions when the pyodbc script was installed in the Volume. Once the script in the Volume stopped "outputting".....the shared cluster was able to read it and start up.

Hope that helps you!

Benni

Re: UC, pyodbc, Shared Cluster, and :Can't open lib 'ODBC Driver 17 for SQL Server' : file not foun

praveenVP — Mon, 03 Mar 2025 04:38:45 GMT

Hello all,

Below workaround was efficient to me

1) pyodbc-install.sh is uploaded in a Volume

2) the shared cluster is able to navigate to the Volume to select the init script

3) the Databricks runtime is 15.4 LTS

4) the Allowlist has been updated to allow the init script

5) the cluster spins up with no problem

6) the pyodbc==5.2.0 library has been installed (although it made no difference) but

If you are using from ADF use below volumes option and provide ADF SP access to volume path