JDBC connection to Fabric Lakehouse SQL endpoint using notebook

[rettep48]

Hi,

We are trying to connect to the SQL endpoint of a Fabric lakehouse using a JDBC connection in a notebook in Fabric. The reason for this is that we want to access the SQL views created in the endpoint. There is a service principal created for this and it has read access to the Fabric workspace. 

We have tried to connect using code from example screenshot below found at: https://learn.microsoft.com/en-gb/training/modules/ingest-data-with-spark-fabric-notebooks/2-connect...

The only change is that we have used the property Authentication=ActiveDirectoryPassword instead of ActiveDirectoryIntegrated.

When trying to connect to the endpoint however, we get the following error:

We have also tried doing the same connection with the same service principal using PYODBC and this works fine. Would like to use JDBC though, or at least know if it is possible.

Would greatly appreciate if anyone has any input regarding this, if there is anything clearly wrong in our connection, how to proceed with troubleshooting etc.

Thanks in advance!

[vtboc]

Team, I have a similar requirement of connecting to Lakehouse's SQL endpoint using Spark Notebook. Reason is I want to query views like INFORMATION_SCHEMA using fabric spark notebooks. Do we have a solution?

[sriniwas_msbi]

Hi,

I am also facing same issue. is there any solution?

[kskistad]

Does it make a difference if you change your authentication to use "ActiveDirectoryServicePrincipal"?

[v-nikhilan-msft]

Hi @rettep48 
Thanks for using Fabric Community.
Can you please confirm if you are following these steps?

When establishing connectivity via JDBC, check for the following dependencies:

1. Add artifacts, choose Add Artifact and add the following four dependencies in the window like this, then select Download/Update to load all dependencies.

    

2. Select Test connection, and Finish.

   

For more details, refer to Connectivity to data warehousing in Microsoft Fabric - Connect using JDBC.

Thanks.

[rettep48]

Hi @v-nikhilan-msft

Thank you for a fast reply!

How do I add artifacts/check dependencies inside of Fabric? Not sure how to apply the steps in your link when using a Fabric notebook.

[v-nikhilan-msft]

Hi @rettep48 
Are you using any Firewall for the Azure SQL database? And can you please confirm if you are using Microsoft Entra ID authentication?

[rettep48]

Hi @v-nikhilan-msft,

It is actually not an Azure SQL Database that we are trying to connect to, it is the Fabric Lakehouse SQL analytics endpoint (server URL ending with .datawarehouse.fabric.microsoft.com). I don't think it is a firewall issue since we are able to successfully connect from Fabric notebook to the exact same SQL endpoint using PYODBC.

I can confirm that we are using Microsoft Entra ID service principal.

[v-nikhilan-msft]

Hi @rettep48 
Thanks for providing details. I will repro this from my side and let you know.
Appreciate your patience.

[v-nikhilan-msft · Accepted solution]

Hi @rettep48 
Apologies for the issue you have been facing. I have contacted the internal team and they have confirmed that there is an ongoing bug regarding this issue. I will keep you updated regarding the bug. For now please use the pyodbc connection as it works good.

Hope this helps. Please let me know if you have any further questions.

[FelixL]

Is there any update here? I am facing this issue, and using PyODBC connection is not a realistic alternative given I need to load large amounts of data and want to utilize the underlying spark engine for this, not overload the top level python executor. 

I am trying to connect from Synapase Analytics notebooks, to a Fabric SQL endpoint. I find it very concerning that two Microsoft products wont talk to each other, and that the bug reported half a year ago has not yet been solved. 

I am running on the latest version of Spark in Synapse, and facing the exact same issue as the original poster.

Please update us on the bug / timeline of fix. Thanks.

[joearus]

Any ETA for this bug?

[RodrigoPombo]

@v-nikhilan-msft @rettep48 

To mitigate the issue, we can add the mssql-jdbc-11.2 and msal4j-1.13.3 jar file path in the spark properties

spark.yarn.dist.jars <path to the jars in /Files folder>

spark.driver.extraClassPath ./mssql-jdbc-11.2.1.jre8.jar:./msal4j-1.13.3.jar

spark.executor.extraClassPath -- ./mssql-jdbc-11.2.1.jre8.jar:./msal4j-1.13.3.jar

[FelixL]

The mentioned versions did not mitigate the issue. They introduced another issue where the queries timed out with error message "SQL Pool is warming up...." - which is isnt. 

I was however able to fix this in Synapse Analytics by adding the following jars to my spark pool:

* mssql-jdbc-12.8.1.jre11

* msal4j-1.17.2

(latests available versions at the time of writing this)

[rettep48]

Hi @v-nikhilan-msft

Thank you for the update. I will be looking forward to more information regarding the bug in the future.

[v-nikhilan-msft]

Hi @rettep48 
Glad that I could help you. Please continue using Fabric Community for any help regarding your queries.

[HenrikS2]

Hi,

Still no update on this bug from the internal team? Is it still considered a bug?

[HenrikS2]

Hi!

I will also be looking forward to more information regarding the bug in the future.

Regards,

Henrik