I am trying to make connection through Azure Databricks to Azure SQL Server and if database is not available, then create a new one and same for table but getting error for ODBC driver

How to install 'ODBC Driver 17 for SQL Server" in an Azure Databricks workspace?

%python
%pip install pyodbc
dbutils.library.restartPython()
import pyodbc

#pending need more work to finish this
#Azure SQL connection details

server = '<Sever-name>'
database = 'master'  # Start with the 'master' database for creating a new one
username = 'admin_1'
password = 'test&123'
driver = 'ODBC Driver 17'

# Set up connection string
connection_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}'

# Create a connection
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

# Step 1: Create a new database
new_db_name = 'NewDatabase'  # Specify your new database name
cursor.execute(f"CREATE DATABASE {new_db_name}")
connmit()

print(f"Database {new_db_name} created successfully!")

# Step 2: Connect to the new database
connection_string_new_db = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={new_db_name};UID={username};PWD={password}'
conn_new_db = pyodbc.connect(connection_string_new_db)

print(f"Connected to {new_db_name} successfully!")

# Close the connection
cursor.close()
conn.close()
conn_new_db.close()

I am trying to make connection through Azure Databricks to Azure SQL Server and if database is not available, then create a new one and same for table but getting error for ODBC driver

How to install 'ODBC Driver 17 for SQL Server" in an Azure Databricks workspace?

%python
%pip install pyodbc
dbutils.library.restartPython()
import pyodbc

#pending need more work to finish this
#Azure SQL connection details

server = '<Sever-name>'
database = 'master'  # Start with the 'master' database for creating a new one
username = 'admin_1'
password = 'test&123'
driver = 'ODBC Driver 17'

# Set up connection string
connection_string = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={database};UID={username};PWD={password}'

# Create a connection
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

# Step 1: Create a new database
new_db_name = 'NewDatabase'  # Specify your new database name
cursor.execute(f"CREATE DATABASE {new_db_name}")
conn.commit()

print(f"Database {new_db_name} created successfully!")

# Step 2: Connect to the new database
connection_string_new_db = f'DRIVER={driver};SERVER={server};PORT=1433;DATABASE={new_db_name};UID={username};PWD={password}'
conn_new_db = pyodbc.connect(connection_string_new_db)

print(f"Connected to {new_db_name} successfully!")

# Close the connection
cursor.close()
conn.close()
conn_new_db.close()

• azure
• azure-sql-database
• azure-databricks

• Can you add code what you have tried? – Pratik Lad Commented Jan 19 at 13:43
• Pratik Lad added code base for your reference – ravipatel Commented Jan 19 at 19:15

2 Answers 2

To install MS SQL ODBC Driver on Azure Databricks workspace run below command in a single cell.

%sh
curl https://packages.microsoft.com/keys/microsoft.asc | apt-key add -
curl https://packages.microsoft.com/config/ubuntu/22.04/prod.list > /etc/apt/sources.list.d/mssql-release.list
sudo apt-get update
sudo ACCEPT_EULA=Y apt-get -q -y install msodbcsql17

To install ODBC Driver 17 for SQL Server on databricks cluster you can follow below steps:

1. Create an shell script file with below content and save it. For example save the file with name as install_msodbcsql17.sh

# Download the Microsoft signing key
echo "Downloading Microsoft signing key..."
curl https://packages.microsoft.com/keys/microsoft.asc | sudo apt-key add -
# Add the Microsoft repository for ODBC Driver 18
echo "Adding Microsoft repository..."
curl https://packages.microsoft.com/config/ubuntu/$(lsb_release -rs)/prod.list | sudo tee /etc/apt/sources.list.d/mssql-release.list
# Update the package index
echo "Updating package index again..."
sudo apt-get update
# Install the ODBC Driver 17
echo "Installing ODBC Driver 17..."
sudo ACCEPT_EULA=Y apt-get install -y msodbcsql17
# Install oDBC Driver Manager
# echo "Installing ODBC Driver manager ..."
sudo apt-get -y install unixodbc-dev
# Check if the driver is installed
echo "Checking installed driver..."
dpkg -L msodbcsql17
echo "ODBC Driver installation completed successfully."```

2. You can refer this script by uploading it on dbfs, s3 bucket or unity volumes while configuring the cluster in the Advanced options -> Init scripts.

Note: If you are using the above shell script content and pasting it in notepad++ on windows machine and creating the .sh file make sure that it has Unix(LF) EOL Conversion otherwise script might not work properly on databricks cluster.