connect timed out error - Connecting to SQL Server from Databricks

[patrickw]

I am getting a connect timed out error when attempting to access a sql server. I can successfully ping the server from Databricks. I have used the jdbc connection and the sqlserver included driver and both result in the same error. I have also attempted to use the IP address instead of the host name in the connection strings but still get an error. I worked with my system admin and they were able to see the traffic on the firewall when the server was pinged but did not see any traffic when I ran the jdbc and sqlserver code cells. Is there any driver I would need to install on my cluster to be able to connect to this sql server? 

Error message: 
com.microsoft.sqlserver.jdbc.SQLServerException: The TCP/IP connection to the host ********, 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.".

[Walter_C]

Can you run the following command in a notebook using the same cluster you are using to connect:

%sh

nc -vz <hostname> <port>

This test will confirm us if we are able to communicate with the SQL server by using the port you are defining to connect. If the connection is not succeeded we might need to check if any firewall or security group is blocking the communication.

If it succeeds can you confirm you are using the latest jdbc driver version?

[patrickw]

Thank you Walter_C. I ran a similar block of code utilizing the shell command you provide. 

Code

import subprocess

# Define the hostname and port
hostname = "************"
port = "1433"

# Create the command as a list
command = ["nc", "-vz", hostname, port]

# Execute the command and capture the output and errors
process = subprocess.run(command, text=True, capture_output=True)

# Check if the process was successful
if process.returncode == 0:
    print("Connection successful.")
else:
    print("Failed to connect.")
    print("Output:", process.stdout)
    print("Errors:", process.stderr)

And I got this output:

Failed to connect. Output: Errors: nc: connect to ************** (ip address) port 1433 (tcp) failed: Connection timed out

I have consulted with my system admin and he confirmed that the port and firewall were open. In this example, I attempted to execute code like this while the admin was monitoring the firewall activity and he did not see anything attempt to access and get blocked. Now when I ran a ping command he could see the IP from the cluster in the activity log.