โ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 06:42 AM
Hi @Alexander Mora Arayaโ, We haven't heard from you since the last response from @Daniel Sahalโ โ, and I was checking to see if their suggestions helped you. Or else, If you have any solution, please share it with the community, as it can be helpful to others.
Also, please don't forget to click on the "Select As Best" button whenever the information provided helps resolve your question.
โ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.
Join our fast-growing data practitioner and expert community of 80K+ members, ready to discover, help and collaborate together while making meaningful connections.
Click here to register and join today!
Engage in exciting technical discussions, join a group with your peers and meet our Featured Members.