topic Re: Unable to read data from onprem sql server to databricks in Data Engineering

Unable to read data from onprem sql server to databricks

plakshmi — Mon, 14 Oct 2024 07:07:32 GMT

I am trying to read data into data frame of data Bricks from on Prem SQL server but facing
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host HYDNB875, port 1433 has failed. Error: "HYDNB875. 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.".

I have all the set up later this also showing blank screen, port is listening
telnet <your_sql_server_ip> 1433

Re: Unable to read data from onprem sql server to databricks

Rishabh-Pandey — Mon, 14 Oct 2024 10:04:55 GMT

@plakshmi

1-Double-check your JDBC connection string. It should look something like this

jdbc_url = "jdbc:sqlserver://<your_sql_server_ip>:1433;databaseName=<your_database>;user=<your_username>;password=<your_password>"

2-Ensure that there are no firewall rules blocking inbound/outbound traffic on port 1433. Both the SQL Server machine and any intermediate firewalls must allow traffic on this port.

3-Make sure TCP/IP is enabled.

4-Ensure that your SQL Server instance is running and accessible. You can check this by connecting to it using SQL Server Management Studio (SSMS) from the same network where Databricks is hosted.

Re: Unable to read data from onprem sql server to databricks

Panda — Mon, 14 Oct 2024 10:47:04 GMT

@plakshmi

Along with what @Rishabh-Pandey mentioned, follow these additional step

If HYDNB875 can't be resolved, try using the server's IP address.
Check for network routing issues between Databricks and the SQL Server using traceroute or ping.
Review the SQL Server logs for any blocked or failed connection attempts.

Re: Unable to read data from onprem sql server to databricks

plakshmi — Mon, 14 Oct 2024 11:30:36 GMT

Hi @Rishabh-Pandey

First 3 steps I have done correct and crosschecked many times, my data Bricks workspace not created under VPN and also I checked in powershell

telnet <ip-address> 1433 it is showing blank screen...

The SQL server able to listening to port but when I am trying to connect from Databricks using pyspark code facing the above issue

Note: 1.Downloaded .jar files and attached to cluster

2.Allowed inbound rule for data Bricks region IP addresses range

Thanks,

P.Lakshmi

Re: Unable to read data from onprem sql server to databricks

plakshmi — Mon, 14 Oct 2024 11:49:56 GMT

Hi @Panda

Even

1.I have used IP address instead of server name

2.could you please provide any reference document for this

network routing issues between Databricks and the SQL Server using traceroute or ping (or) any support engineer assist on this