02-28-2023 12:07 PM
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.".
02-28-2023 10:28 PM
@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.
03-01-2023 01:39 AM
@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)
03-01-2023 04:32 AM
@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.
03-02-2023 07:33 AM
@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!
03-02-2023 10:33 PM
@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.
03-03-2023 08:48 AM
@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.
03-05-2023 10:53 PM
@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.
03-06-2023 08:12 AM
@Kaniz Fatma
Unfortunately I was not able to connect to on premise sql server. Thanks for asking.
04-26-2023 07:49 AM
You need to setup the VNet and wire up the connection between Databricks and on-prem via VPN or ExpressRoute
04-26-2023 07:52 AM
Thanks Isaach, but no via vpn or expressroute. Thanks anyway.
04-26-2023 08:01 AM
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.
09-08-2024 09:04 PM
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.
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