topic Re: Install the ODBC Driver 17 for SQL Server in Data Engineering

Install the ODBC Driver 17 for SQL Server

GJ2 — Mon, 17 Feb 2025 13:22:02 GMT

Hi,

I am not a Data Engineer, I want to connect to ssas. It looks like it can be connected through

pyodbc. however looks like I need to install "ODBC Driver 17 for SQL Server" using the following command. How do i install the driver on the cluster and are there any requirement to connect with SSAS ?

Thank you

Re: Install the ODBC Driver 17 for SQL Server

Alberto_Umana — Mon, 17 Feb 2025 14:00:57 GMT

Hello @GJ2,

You will need to create a file as init script .sh and then put it into the server init script. https://docs.databricks.com/en/init-scripts/cluster-scoped.html

You can use this script:

\#!/bin/bash

# Add Microsoft package repository
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
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

# Install ODBC Driver 17 for SQL Server
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

# Install additional required packages
sudo apt-get -y install unixodbc
sudo apt-get -y install libgssapi-krb5-2
sudo apt-get -y install odbcinst
sudo apt-get -y install odbcinst1debian2
sudo apt-get -y install libodbc1

Save the initialization script in a location accessible by Databricks, e.g., DBFS (Databricks File System / Workspace File).

Attach the initialization script to your Databricks cluster:

Go to the Databricks workspace.

Navigate to the cluster you want to install the driver on.
Click on the "Clusters" and then select the cluster.
Click on "Edit" to modify the cluster configuration.
Scroll down to the "Advanced Options" and select the "Init Scripts" tab.
Add a new script and provide the path to the script saved in DBFS / Workspace File .sh

Re: Install the ODBC Driver 17 for SQL Server

Rjdudley — Mon, 17 Feb 2025 14:32:56 GMT

Hey @Alberto_Umana do you know if Lakehouse Federation supports SSAS, or just the transactional catalog of SQL Server? I looked in the docs, SSAS isn't mentioned specifically, and now I'm curious.

Re: Install the ODBC Driver 17 for SQL Server

Alberto_Umana — Mon, 17 Feb 2025 14:47:05 GMT

Hi @Rjdudley - SSAS isn't supported only SQL server.

Re: Install the ODBC Driver 17 for SQL Server

GJ2 — Tue, 18 Feb 2025 02:32:50 GMT

Hi @Alberto_Umana Basically what I want to do is get all the tables used in Power BI datasets. I am trying to connect to xmla endpoint through SSMS and get the tables names used in Power BI dataset. If I do it directly in SSAS then I can only query one dataset. I want to loop through all the PBI datasets (databases when connected through SSAS) and get the tables used in each dataet. Is there any better way to do this ? Can I do this in databricks notebook? Thanks heaps

Re: Install the ODBC Driver 17 for SQL Server

pperin — Thu, 20 Feb 2025 14:18:44 GMT

I have the same requirement of installing the Microsoft ODBC driver. I added your code in the init script and I got the following error: "databricks_error_message": "Cluster scoped init script /Volumes/packages/install_sqlserver_driver.sh failed: Script exit status is non-zero". How can we solve it?

Re: Install the ODBC Driver 17 for SQL Server

karifclark — Sat, 10 May 2025 10:58:41 GMT

To connect to SSAS using pyodbc, you're right that installing the "ODBC Driver 17 for SQL Server" is required. You can install it on your cluster using the appropriate system package manager (e.g., apt for Ubuntu or yum for CentOS), and make sure the driver is accessible from all nodes if it's a distributed environment. Also, ensure that your connection string includes the correct SSAS server name and authentication details. Just like you wouldn’t stream without first installing a proper app—such as visiting a reliable site like Tele-Latino to download a free streaming app—you can’t connect to SSAS without the correct driver in place. Proper setup is key for smooth access.

Re: Install the ODBC Driver 17 for SQL Server

dinarde — Thu, 17 Jul 2025 19:50:31 GMT

I had this problem with Init Script and changing from CRLF to LF (Unix) solved it:

Incorrect Line Endings (CRLF vs. LF): If the script was created on a Windows system and uploaded to a Unity Catalog volume, it might have Windows-style line endings (CRLF). Linux environments (where Databricks clusters run) expect LF line endings.

Re: Install the ODBC Driver 17 for SQL Server

kathrynshai — Sat, 08 Nov 2025 10:50:16 GMT

Hello Databricks Community,

You’re correct—connecting to SSAS via pyodbc usually requires the “ODBC Driver 17 for SQL Server.” To install it on your cluster, the steps depend on your OS: for Linux, you typically add the Microsoft repository and install via apt-get or yum; for Windows, you can download the installer from Microsoft’s official site. Make sure the cluster has network access to the SSAS server and that any necessary credentials and firewall rules are properly configured. Also, note that for SSAS, you might need the correct MSOLAP/OLAP support depending on whether you’re connecting to a multidimensional or tabular model. For additional guidance and downloads, always refer to Microsoft’s official documentation. And if you need a break from data setups, you can visit Dooflix, a free streaming app, to relax and catch up on shows.

Best Regards!

Re: Install the ODBC Driver 17 for SQL Server

Coffee77 — Sun, 09 Nov 2025 14:19:30 GMT

Why not using SQL Server Access Connector directly instead of ODBC driver? With first option you don't need to install anything. Using that in some of my projects and working pretty fine to connect Azure SQL databases.

Re: Install the ODBC Driver 17 for SQL Server

Coffee77 — Mon, 10 Nov 2025 09:30:09 GMT

If you only need to interact with your cloud SQL database, I recommend you use simple code like displayed below for running select queries. To write would be very similar. Take a look here: https://learn.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver17

Re: Install the ODBC Driver 17 for SQL Server

Rjdudley — Mon, 10 Nov 2025 13:32:56 GMT

The original question asked about SSAS, which is not supported. Only the SQL Server OLTP engine is supported. Also, this question was asked prior to public availability of the SQL Connector.

Re: Install the ODBC Driver 17 for SQL Server

Coffee77 — Mon, 10 Nov 2025 14:12:35 GMT

Got it, I didn't realize SSAS was asked, sorry for the confusion.

Re: Install the ODBC Driver 17 for SQL Server

angelaguzma — Thu, 04 Dec 2025 07:36:27 GMT

@kathrynshai I encountered an issue with the Init Script, which was resolved by switching from CRLF to LF (Unix):

Incorrect Line Endings (CRLF vs. LF): If the script was generated on a Windows platform and subsequently uploaded to a Unity Catalog volume, it may contain Windows-style line endings (CRLF). However, Linux environments (where Databricks clusters operate) anticipate LF line endings.

Re: Install the ODBC Driver 17 for SQL Server

Hubert-Dudek — Thu, 04 Dec 2025 09:50:32 GMT

As SQL Server is included in the Lakehouse federation driver, it is built in databricks. Install only in case you need a different version - the built-in one is not working