Problem

You are using an init script to install the Microsoft ODBC driver in a Unity Catalog enabled shared cluster 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 shared clusters you do not have root privileges and init scripts in shared clusters run as the cluster owner.

Solution

Unity Catalog shared clusters are designed to enhance security and manageability by restricting access to specific resources. In order to access init scripts in a Unity Catalog enabled shared access cluster, 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 that your cluster is configured as a Shared cluster, running Databricks Runtime 13.3 LTS or above.
2. Use the workspace file browser to create a new file (AWS | Azure | GCP) in your home directory. Call it install_msodbc.sh.
3. Open the install_msodbc.sh file.
4. Copy and paste this init script into install_msodbc.sh.

#!/bin/bash
if ! [[ "18.04 20.04 22.04 23.04 24.04" == *"$(lsb_release -rs)"* ]];
then
   echo "Ubuntu $(lsb_release -rs) is not currently supported.";

   exit;
fi

curl https://packages.microsoft.com/keys/microsoft.asc | sudo tee /etc/apt/trusted.gpg.d/microsoft.asc

curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list

sudo apt-get update
sudo ACCEPT_EULA=Y apt-get install -y  msodbcsql18
# optional: for bcp and sqlcmd
sudo ACCEPT_EULA=Y apt-get install -y mssql-tools
echo 'export PATH="$PATH:/opt/mssql-tools/bin"' >> ~/.bashrc
source ~/.bashrc
# optional: for unixODBC development headers
sudo apt-get install -y unixodbc-dev
/databricks/python3/bin/pip install --user pyodbc

5. Close the file.
6. Follow the documentation to configure a cluster-scoped init script (AWS | Azure | GCP) as a workspace file.
7. 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.
8. Add install_msodbc.sh to your allowlist (AWS | Azure | GCP).
9. Restart the cluster.
10. After the cluster has restarted, verify that the Microsoft ODBC driver is installed by viewing the ini file.

%sh

cat /etc/odbcinst.ini

11. If /etc/odbcinst.ini exists, the Microsoft ODBC driver is ready for use.

For more information, review the Install the Microsoft ODBC driver for SQL Server (Linux) documentation.