MS Spark SQL Connector AD Integration Error

Dinesh Balachandran 56

We have been using Azure Synapse notebooks to connect and load some data from Azure SQL. We were using the SQL Authentication Model and were looking to use the Azure SQL Spark Connector to use AD

While we were able to use the examples to get the code working via Service Principal Token model. We get the following error when trying to use the AD Password mode.

Py4JJavaError: An error occurred while calling o2293.load. : java.lang.NoClassDefFoundError: com/nimbusds/oauth2/sdk/auth/ClientAuthentication

Any inputs are greatly appreciated

I had posted the snapshot of the code in the comments

jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "jdbc:sqlserver://Z-ondemand.sql.azuresynapse.net") \
.option("dbtable", "dbo.MyTable").option("authentication", "ActiveDirectoryPassword") \
.option("user","a@test.com").option("password", "XXXXXXXXXXXX").option("encrypt", "true")\
.option("hostNameInCertificate", "*.database.windows.net")\
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")\
.load()

Dinesh Balachandran 56

Here is a snapshot of the code we use

jdbc_df = spark.read.format("com.microsoft.sqlserver.jdbc.spark").option("url", "jdbc:sqlserver://Z-ondemand.sql.azuresynapse.net") \
.option("dbtable", "dbo.MyTable").option("authentication", "ActiveDirectoryPassword") \
.option("user","a@test.com").option("password", "XXXXXXXXXXXX").option("encrypt", "true")\
.option("hostNameInCertificate", "*.database.windows.net")\
.option("driver", "com.microsoft.sqlserver.jdbc.SQLServerDriver")\
.load()

Dinesh Balachandran 56 Reputation points

2023-02-27T17:48:48.81+00:00

Note: the server i am connecting to is a Synapse Endpoint
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-03-01T11:37:35.3133333+00:00

@Dinesh Balachandran Thanks for the question and using MS Q&A platform.

The error message "Py4JJavaError: An error occurred while calling o2293.load. : java.lang.NoClassDefFoundError: com/nimbusds/oauth2/sdk/auth/ClientAuthentication" suggests that there is a missing Java class required to authenticate the client in Azure Synapse Analytics Spark connector.

Do you have the SDK dependency in your Maven pom.xml (if you're using Maven of course):

https://connect2id.com/products/nimbus-oauth-openid-connect-sdk/download

Hope this helps.
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-03-06T06:52:10.89+00:00

@Dinesh Balachandran We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.
Dinesh Balachandran 56 Reputation points

2023-03-06T13:17:37.75+00:00

Hello Pradeep, no we are not using Maven. The error occurs when I use a Notebook (Spark 3.2) against the default Spark Cluster. The Spark SQL Connector Website says i will not have to install any other dependencies if I am using Spark 3.1 and above. Also as I said, the connector works when I use SQL Auth or the Service Principal Token Model.
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-03-07T04:23:42.1266667+00:00

@Dinesh Balachandran This issue looks strange. For a deeper investigation and immediate assistance on this issue, if you have a support plan you may file a support ticket
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-03-13T04:25:43.7566667+00:00

@Dinesh Balachandran Did you get a chance to open a support ticket?
Dinesh Balachandran 56 Reputation points

2023-03-19T16:00:33.34+00:00

Hey Pradeep, no I do not have a ticket yet. But I think I now understand the error thank to your other comment. The https://connect2id.com/products/nimbus-oauth-openid-connect-sdk/download seems to be an essential component for the AD Password model in the Connector. This is not currently listed as available in the Default Spark Runtime environment provisioned by Synapse/MS

https://learn.microsoft.com/en-us/azure/synapse-analytics/spark/apache-spark-33-runtime

I will try to manually add this jar file to the environment and test the library out again.
Stephen Boesch (Launch Consulting) 0 Reputation points Microsoft Vendor

2023-09-20T06:22:22.3566667+00:00

Hi Dinesh - did that work for you? I am encountering the same error when trying to use kusto from spark 3.3 notebook on synapse.
PRADEEPCHEEKATLA-MSFT 78,331 Reputation points Microsoft Employee

2023-09-21T10:07:03.1733333+00:00

Stephen Boesch (Launch Consulting) - Since this thread is too old, I would recommend creating a new thread on the same forum with as much details about your issue as possible. That would make sure that your issue has better visibility in the community.