Connect to on prem SQL server through Azure Databricks notebook using Python PYODBC

Question

Currently I connect to my on premises SQL servers using Windows authentication. I would like to do the same using Databricks so that I can load the data extracted using the query into a dataframe and do some machine learning experiments. Is it possible to connect to sql server (on-prem) using pyodbc or any other driver in notebooks?

Currently I am trying this code to connect to server database using databricks notebooks and I run into a connection timeout error

cxn=pyodbc.connect("Driver ={SQL Server Native Client 11.0};""Server=server name;"
"Port=1433;"Database=database_name;"Trusted_Connection=yes;")

If its a login issue is the solution to connect my databricks cluster to my network? If not it would be great if somebody can point me in right direction.

Answer 1

Is it possible to connect to sql server (on-prem) using pyodbc or any other driver in notebooks?

Yes, Databricks Runtime contains JDBC drivers to connect to an on-premises database from Azure Databricks notebook.

SQL databases using JDBC this document show the how to use the DataFrame API to connect to SQL databases using JDBC and how to control the parallelism of reads through the JDBC interface.

Connect your Azure Databricks workspace to your on-premises network this document give you an idea about how to establish connectivity from your Azure Databricks workspace to your on-premises network.

Reference: Connecting to on-prem SQL Server through Azure Databricks similar thread

If it’s a login issue is the solution to connect my databricks cluster to my network?

Your connection string looks different, you have to add username and password also eg.,

import pyodbc 
# Some other example server values are
# server = 'localhost\sqlexpress' # for a named instance
# server = 'myserver,port' # to specify an alternate port
server = 'tcp:myserver.database.windows.net' 
database = 'mydb' 
username = 'myusername' 
password = 'mypassword' 
cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER='+server+';DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()

The default SQL Server port is 1433 and there is no need to specify that in the connection string.