cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Install the ODBC Driver 17 for SQL Server

GJ2
New Contributor II

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 ?
 
 GJ2_1-1739798450883.png

Thank you 


 

 
5 REPLIES 5

Alberto_Umana
Databricks Employee
Databricks Employee

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

 

 

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.

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?

Alberto_Umana
Databricks Employee
Databricks Employee

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

GJ2
New Contributor II

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 

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group