Connecting Databricks to on prem sources

Question

Is there a way to connect my Azure Databricks workspace to my local SQL Server database? I am trying to read data from my local SQL Server installed on my machine, but I am looking for a way to connect the two directly. I am aware we can use a SHIR with Data factory and load the data to a storage account and read the data from storage but looking for a method of direct connection. I also know there is a method to connect on prem networks to Databricks workspace but I'm having issues connecting my network to Azure (might be due to ISP restrictions, not entirely sure).

Answer 1

Hello Abdullah Humayun,

Welcome to the Microsoft Q&A forum.

Databricks use the updated MSSQL-JDBC driver. You can use the below code to connect to your on-prem

(spark.read
  .format("jdbc")
  .option("url", "jdbc:sqlserver://servername\sqlinst001;database=dbname;loginTimeout=30;integratedSecurity=true;authenticationScheme=NTLM;")
  .option("dbtable", "tblname")
  .option("user", "username")
  .option("password", "password")
  .load()
  .show()
)

also, you can use Apache spark for SQL connector

Referene documents:
https://learn.microsoft.com/en-us/sql/connect/spark/connector?view=sql-server-ver16#azure-active-directory-authentication

https://learn.microsoft.com/en-us/azure/databricks/security/network/classic/on-prem-network

https://learn.microsoft.com/en-us/sql/connect/jdbc/using-ntlm-authentication-to-connect-to-sql-server?view=sql-server-ver16

I hope this helps.