topic Re: Connecting SQL Server (on-premise) to Databricks via jdbc:sqlserver in Data Engineering

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

Tico23 — Tue, 28 Feb 2023 20:07:22 GMT

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

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

daniel_sahal — Wed, 01 Mar 2023 06:28:37 GMT

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

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

Tico23 — Wed, 01 Mar 2023 09:39:01 GMT

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

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

daniel_sahal — Wed, 01 Mar 2023 12:32:46 GMT

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

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

Tico23 — Thu, 02 Mar 2023 15:33:46 GMT

@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!

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

daniel_sahal — Fri, 03 Mar 2023 06:33:52 GMT

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

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

Tico23 — Fri, 03 Mar 2023 16:48:42 GMT

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

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

daniel_sahal — Mon, 06 Mar 2023 06:53:41 GMT

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

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

Tico23 — Mon, 06 Mar 2023 16:12:56 GMT

@Kaniz Fatma

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

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

DBXC — Wed, 26 Apr 2023 14:49:10 GMT

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

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

Tico23 — Wed, 26 Apr 2023 14:52:37 GMT

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

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

DBXC — Wed, 26 Apr 2023 15:01:09 GMT

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.

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

BharathKumarS — Mon, 09 Sep 2024 04:04:24 GMT

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.