Connecting Databricks to on prem sources

Abdullah Humayun 40 Reputation points
2024-05-29T11:03:25.18+00:00

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).

Azure Databricks
Azure Databricks
An Apache Spark-based analytics platform optimized for Azure.
2,045 questions
Windows Network
Windows Network
Windows: A family of Microsoft operating systems that run across personal computers, tablets, laptops, phones, internet of things devices, self-contained mixed reality headsets, large collaboration screens, and other devices.Network: A group of devices that communicate either wirelessly or via a physical connection.
696 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. BhargavaGunnam-MSFT 28,606 Reputation points Microsoft Employee
    2024-05-29T20:06:28.2433333+00:00

    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://video2.skills-academy.com/en-us/sql/connect/spark/connector?view=sql-server-ver16#azure-active-directory-authentication

    https://video2.skills-academy.com/en-us/azure/databricks/security/network/classic/on-prem-network

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

    I hope this helps.