Install the ODBC Driver 17 for SQL Server

[GJ2]

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 

[Alberto_Umana]

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

[Rjdudley]

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.

[pperin]

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?

[dinarde]

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.

[Alberto_Umana]

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

[GJ2]

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 

[karifclark]

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.

[kathrynshai]

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!

[Coffee77]

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. 

[Rjdudley]

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.

[Coffee77]

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

[Coffee77]

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 

KR