How to Install ODBC Driver 17 for SQL Server on Azure Databricks without Internet Access

A step-by-step guide on installing `ODBC Driver 17` for SQL Server on Azure Databricks Cluster with no internet access, ensuring seamless access to Azure Synapse Analytics.
---
This video is based on the question https://stackoverflow.com/q/73690341/ asked by the user 'Daniel Bonetti' ( https://stackoverflow.com/u/1720085/ ) and on the answer https://stackoverflow.com/a/73693507/ provided by the user 'Daniel Bonetti' ( https://stackoverflow.com/u/1720085/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: How to install ODBC Driver 17 for SQL Server on a Azure Databricks cluster with no internet access

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
How to Install ODBC Driver 17 for SQL Server on Azure Databricks without Internet Access

Introduction

Accessing Azure Synapse Analytics from Azure Databricks can be a challenging task, especially when facing connectivity limitations. If you're working in an environment where your Databricks cluster lacks internet access, the task of installing the necessary ODBC driver to link your Databricks notebooks with Azure Synapse can feel daunting.

In this post, we'll walk through the solutions you need to set up the ODBC Driver 17 for SQL Server within an Azure Databricks cluster that operates without internet access.

The Challenge

Many users prefer direct access to Azure Synapse Analytics via ODBC or JDBC due to permission concerns with using the spark driver (com.databricks.spark.sqldw). Specifically, the db_owner requirement for this spark driver can lead to security issues–leaving unwanted exposure to data manipulation by users.

However, in order to facilitate this connectivity, we need to address two key issues:

File Access: How to copy an ODBC driver package stored in an Azure Storage Account Gen2 to the Databricks cluster file system.

Installation: How to install the ODBC driver on the Databricks cluster.

Solution Breakdown

Step 1: Mount Azure Storage Account Gen2

Before we can access our ODBC driver package, we need to make sure that the Azure Storage Account is mounted to our Databricks filesystem. Here's how you can achieve that using dbutils.

Mounting Procedure

Execute the following Python script in a Databricks notebook cell:

[[See Video to Reveal this Text or Code Snippet]]

Replace <container>, <storage>, and account key with your specific values.

Once executed, your Azure Storage will be accessible at the path:

[[See Video to Reveal this Text or Code Snippet]]

Step 2: Install ODBC Driver

After successfully mounting your storage account, you can proceed to install the ODBC Driver 17 for SQL Server.

Installation Procedure

Open a new cell in your Databricks notebook and run the following shell command:

[[See Video to Reveal this Text or Code Snippet]]

This command does two things:

It accepts the EULA for the ODBC driver installation.

It installs the driver package from your mounted Azure Storage location.

Verifying Installation

Once the installation is complete, it's crucial to verify that it was successful. You can use the following command:

[[See Video to Reveal this Text or Code Snippet]]

This command will provide you with information about your ODBC installation, such as:

Driver version

Configuration files for data sources

Additionally, you can check the content of the driver’s configuration:

[[See Video to Reveal this Text or Code Snippet]]

This command should output the driver configuration details confirming that the ODBC Driver 17 for SQL Server is installed correctly.

Conclusion

Installing the ODBC Driver 17 for SQL Server on Azure Databricks without internet access requires some maneuvering, particularly through the process of mounting your Azure Storage and installing the driver from there. By following the steps outlined in this guide, you can effectively leverage Azure Synapse Analytics directly from Databricks while maintaining a robust security posture.

By utilizing this setup, you ensure that users can access required data without granting excessive permissions, thus maintaining better control over your Synapse resources. Happy data exploration!