cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Data Engineering
Join discussions on data engineering best practices, architectures, and optimization strategies within the Databricks Community. Exchange insights and solutions with fellow data engineers.
cancel
Showing results forย 
Search instead forย 
Did you mean:ย 

Connecting SQL Server (on-premise) to Databricks via jdbc:sqlserver

Tico23
Contributor

Is it possible to connect to SQL Server on-premise (Not Azure) from Databricks?

I tried to ping my virtualbox VM (with Windows Server 2022) from within Databricks and the request timed out.

%sh
 
ping 122.138.0.14

This is what my connection might look like:

jdbcHostname = "122.138.0.14"
jdbcDatabase = "AdventureWorksDW2019"
username = "sa"
password = "AStrongPassword"
jdbcPort = 1433
 
jdbcUrl = "jdbc:sqlserver://{0}:{1};dabatabse={2};user={3};password={4}"\
            .format(jdbcHostname, jdbcPort, jdbcDatabase, username, password)

But when I tried to get data from my table:

df_product = spark.read.jdbc(url=jdbcUrl, table='DimProduct')
 
df_address.display()

I get the following error:

com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host 122.138.0.14, port 1433 has failed. 
Error: "connect timed out. Verify the connection properties. Make sure that an instance of SQL Server is running on the host 
and accepting TCP/IP connections at the port. Make sure that TCP connections to the port are not blocked by a firewall.".

12 REPLIES 12

daniel_sahal
Esteemed Contributor

@Alexander Mora Arayaโ€‹ 

It is possible however you need to make sure that the connectivity between Azure Cloud and On-Prem is setup. It's not a databricks issue.

@Daniel Sahalโ€‹

Thanks for your reply.

As stated in the problem, it is not in any cloud and it is a virtual box VM trying to communicate with databricks. (NO AZURE)

daniel_sahal
Esteemed Contributor

@Alexander Mora Arayaโ€‹ 

But Databricks is on Azure, so it needs to somehow connect to your On-Prem Database.

It means that the network connection needs to be opened.

@Daniel Sahalโ€‹ 

You are totally right. I tried it using my Azure SQL Server with my Databricks Community Edition and it works fine. I was just hoping (magically) that there was a way to connect to a sql server on premise. It would have been nice for testing!

daniel_sahal
Esteemed Contributor

@Alexander Mora Arayaโ€‹ 

There is a way to connect to your on-prem SQL Server through the public internet, but it would require you to configure your network connection so the 1433 port would be accessed from outside. It would of course give a big security concern and i don't recommend that.

@Daniel Sahalโ€‹ 

Thanks for the advice. Do you mean by creating an inbound rule for port 1433? If that is the case, that did not work for me.

daniel_sahal
Esteemed Contributor

@Alexander Mora Arayaโ€‹ 

It really depends on your ISP, firewalls etc. Maybe port forwarding is a key here?

IMO it's not worth digging through it since you can use AzureSQL DB.

@Kaniz Fatmaโ€‹ 

Unfortunately I was not able to connect to on premise sql server. Thanks for asking.

DBXC
Contributor

You need to setup the VNet and wire up the connection between Databricks and on-prem via VPN or ExpressRoute

Thanks Isaach, but no via vpn or expressroute. Thanks anyway.

check your firewall settings. Databricks takes fully qualified DNS names instead of server names.

One way to get around it is to use IP address directly to reference the server instead server name if you do not have control to the DNS mapping.

BharathKumarS
New Contributor II

I tried to connect to localhost sql server through databricks community edition, but it failed. I have created an IP rule on port 1433 allowed inbound connection from all public network, but still didn't connect. I tried locally using python its working fine.

maybe databricks community edition doesn't have capability to connect to private ip as it has many restrictions.

Connect with Databricks Users in Your Area

Join a Regional User Group to connect with local Databricks users. Events will be happening in your city, and you wonโ€™t want to miss the chance to attend and share knowledge.

If there isnโ€™t a group near you, start one and help create a community that brings people together.

Request a New Group