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

[Tico23]

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

[daniel_sahal]

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

[Tico23]

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

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

[Tico23]

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

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

[Tico23]

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

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

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.

[Tico23]

@Kaniz Fatma​ 

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

[DBXC]

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

[Tico23]

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

[DBXC]

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.