Problem

You are using an init script to install the Microsoft ODBC driver in a Unity Catalog enabled compute with standard access mode (formerly shared access mode) when you get a Can't open lib 'ODBC Driver 17 for SQL Server' error message. You may also get a sudo password error message.

Error: ('01000', "[01000] [unixODBC][Driver Manager]Can't open lib 'ODBC Driver 17 for SQL Server' : file not found (0) (SQLDriverConnect)")
Error: No ODBC driver(s) found on your machine.

Or 

sudo: a terminal is required to read the password; either use the -S option to read from standard input or configure an askpass helper
sudo: a password is required

Cause

In Unity Catalog enabled compute with standard access mode you do not have root privileges and init scripts run as the compute owner.

Solution

Unity Catalog enabled compute with standard access mode is designed to enhance security and manageability by restricting access to specific resources. In order to access init scripts in a Unity Catalog enabled compute with standard access mode, you must use an allowlist (AWS | Azure | GCP). Once you have added the init script to the allowlist, you can run the init script and install the Microsoft ODBC driver.

1. Verify your compute with standard access mode is running Databricks Runtime 13.3 LTS or above.

2. From the following table, select the Microsoft SQL Server (MSSQL) ODBC version required for your Databricks Runtime version. Click the corresponding URL and download the .deb file to a volume you can access. 

3. Use the workspace file browser to create a new file (AWS | Azure | GCP) in your home directory. Call it install_msodbc.sh.

4. Open the install_msodbc.sh file.

5. Copy and paste this init script into install_msodbc.sh. Replace the /Volumes path with your package location and <mssqlodbc-package> with your package name.

#!/bin/bash

echo "Installing odbcinst."

apt update
apt-get install odbcinst

echo "Installing msodbcsql."

### provide your /Volumes path with package location. Replace <mssqlodbc-package> with your package name. For example, for MSSQL ODBC 17 the package name is msodbcsql17_17.10.6.1-1_amd64.deb

apt-get install -y  /Volumes/<catalog>/<schema>/<directory>/<mssqlodbc-package>.deb

6. Close the file.

7. Follow the documentation to configure a cluster-scoped init script (AWS | Azure | GCP) as a workspace file

8. Specify the path to the init script. Since you created install_msodbc.sh in your home directory, the path should look like /Users/<your-username>/install_msodbc.sh.

9. Add install_msodbc.sh to your allowlist (AWS | Azure | GCP).

10. Restart the compute.

11. After the compute has restarted, verify that the Microsoft ODBC driver is installed by executing the following command in a notebook.

%sh
odbcinst -d -j