Cannot connect to SQL server from Azure Databricks

Question

Hi,

Currently we have Azure Databricks instance created in the virtual network called MarketVNet01.

We also have our SQL database server in the same virtual network.

So the current setting is:

1. Both Databricks instance and SQL server is in the same virtual network called MarketVNet01.
2. Our SQL server is NOT Azure SQL database or managed instance. It is microsoft sql server installed on Windows VM in Azure.
3. When Databricks was created, it was given with two subnets (public and private).
4. Secure Cluster Connectivity is currently disabled on Databricks.

The problem is our data analyst team has tried connecting to one of the database (name: MarketDW) from the Databricks for the first time. The result was unsuccessful.

They sent us the screenshot of the error message they got (see below).

jdbcurl: String = "jdbc:sqlserver://xx.xxx.x.x:1433;databasename=MarketDW"

connectionProperties: Properties = {user=xxxxxx, password=xxxxxxxx}

res4_4: Object = null

res4_5: Object = null

query: String = "select * from MarketDW.glbCommonValues"

df: org.apache.spark.sql.package.DataFrame = Invalid Dataframe; com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target". ClientConnectionId:f07e8238-75c2-480f-b0cf-088084677636

Also this error as well.

jdbcURL: String = "jdbc:sqlserver://xx.xxx.x.x:1433;databasename=MarketDW"

connectionProperties: Properties = {user=xxxxxx, password=xxxxxxxx}

res4_4: Object = null

res4_5: Object = null

query: String = "select * from glbCommonValues"

df: org.apache.spark.sql.package.DataFrame = Invalid Dataframe; Exception thrown in awaitResult: PERMISSION_DENIED: Local RPC without tokens.

Currently, no User-Defined Route has been setup at all because my guess was it is not necessary(?) since both Databricks instance and the SQL server is in the same virtual network (once again, our SQL server is NOT Azure SQL database or managed instance. It is Windows VM in Azure).

Do I need to setup UDR for this?

And will it resolve above issues?

I also researched about the first error (the one about SSL cert), but couldn't find any solution that I can apply.

I don't believe our SQL server has SSL cert enabled.

What type of action should I take to resolve this as well?

Any help or guidance would be greatly appreciated.

Thank you in advance.

Answer 1

Thanks for posting your question in the Microsoft Q&A forum.

Check if your SQL Server has SSL enabled. If not, consider enabling it or modifying the JDBC connection string to use a non-SSL connection. If SSL is required, ensure that the SQL Server has a valid SSL certificate.

** Please don't forget to close up the thread here by upvoting and accept it as an answer if it is helpful **