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!

[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.

[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

[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!

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

Regards,

Henrik