Databricks JDBC Connections works but query fails

user_6419513 0
Viewed 486

I am trying to build a connection to a SQL Server database through Azure Databricks. While there are a number of questions attempting to alleviate issues here, I have yet to find a solution for mine.

I am able to connect, at least it seems because the DataFrame (spark) object reads the exact schema from my database table, yet in attempting to look at the data (display(df) or df.show()) it throws a connection error.

Here is how I'm connecting:

jdbcUrl = "jdbc:sqlserver://{}:{};database={}".format(jdbcHostname, jdbcPort, jdbcDatabase)

connectionProperties = {
  'user': jdbcUsername,
  'password': jdbcPassword,
  "driver" : "com.microsoft.sqlserver.jdbc.SQLServerDriver"
}

pushdown_query = "(select * from persons where personid = 3040) Person"
df = spark.read.jdbc(url=jdbcUrl, table=pushdown_query, properties=connectionProperties)
display(df)

I can see the object df and it correctly identifies all 47 fields in the table (see below photo), but display(df) throws the following error:

SQLServerException: The TCP/IP connection to the host hornets-sql.westus.cloudapp.azure.com, 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.".

enter image description here

pyspark azure-databricks databricks jdbc python
1 Answers

By default, SQL Server has a firewall enabled and don't allow access from arbitrary IPs. In your case you may have the driver node allowed in the firewall - that's why you get the table schema, but the reading itself happens on the worker nodes that may not be enabled on the firewall, and reading fails.

You may try to solve it by:

  1. Adding public IPs all worker nodes to the firewall of SQL server
  2. or by configuring the private link/endpoint for Azure SQL into the VNet of your
  3. or using service endpoints for Azure services

The last two items are well covered in the Databrick's blog post.

Alex_Ott_186270
Related