How to export Databricks dataframe(Pandas or Scala) to Synapse analyitcs SQL table using Active Directory Password

Pradeep Thotakura 1

Hi Team,

I am new to the Azure, I need your expertise in Moving Dataframe from Databricks to Synapse Analytics table. We have implemented a classification model in Databricks and predicted the values and created the dataframe with Actual and predicted values. Now we need to move that dataframe to Synapse Analytics with Active Directory Password in JDBC connection. I have tried many ways but those are not working out. Could you please help me how to move data.

Thanks in Advance.

PRADEEPCHEEKATLA-MSFT 85,746 Reputation points Microsoft Employee

2021-07-12T11:06:29.88+00:00
Hello @Pradeep Thotakura ,

Welcome to the Microsoft Q&A platform.

To investigate further, do share the below information:

Could you please share the different methods which you have tried?

When you say it's not working are you experiencing any error message?

Please do share the code and the stacktrace of the error message?
PRADEEPCHEEKATLA-MSFT 85,746 Reputation points Microsoft Employee

2021-07-14T09:14:13.26+00:00

Hello @Pradeep Thotakura ,

Follow up to see if you have had a chance to see the previous response. We need the following information to understand/investigate this issue further.
Pradeep Thotakura 1 Reputation point

2021-07-14T11:01:48.197+00:00

@PRADEEPCHEEKATLA-MSFT Hi Pradeep, Thank for the reply. Below is the code we are trying, we are fine with Python or Scala to push the data.

%s# scala
val blobStorage = "test.blob.core.windows.net"
val blobContainer = "test"
val blobAccessKey = "access key here"

%scala
val tempDir = "wasbs://" + blobContainer + "@" + blobStorage +"/tempDirs"

%scala
val acntInfo = "fs.azure.account.key."+ blobStorage
sc.hadoopConfiguration.set(acntInfo, blobAccessKey)

%scala
val sqlDwUrlSmall = "jdbc:sqlserver://acoesynapsedev.sql.azuresynapse.net:1433;database=test;
user=test@exxaro.com;password=passwordhere;encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.sql.azuresynapse.net;
loginTimeout=30;authentication=ActiveDirectoryPassword"

I have a scala dataframe called df1

%scala
spark.conf.set(
"spark.sql.parquet.writeLegacyFormat",
"true")

df1.write.format("com.databricks.spark.sqldw").option("url", sqlDwUrlSmall).
option("dbtable", "SampleTable").option( "forward_spark_azure_storage_credentials","True").option("tempdir", tempDir).mode("overwrite").save()
Pradeep Thotakura 1 Reputation point

2021-07-14T11:02:32.967+00:00
Below is the error we are getting(User has Active Directory Access)

*****Error:*****

SqlDWSideException: Azure Synapse Analytics failed to execute the JDBC query produced by the connector.
Underlying SQLException(s):

com.microsoft.sqlserver.jdbc.SQLServerException: Failed to authenticate the user test@exxaro.com in Active Directory (Authentication=ActiveDirectoryPassword). [ErrorCode = 0] [SQLState = null]

Caused by: SQLServerException: Failed to authenticate the user test@exxaro.com in Active Directory (Authentication=ActiveDirectoryPassword).
Caused by: ExecutionException: java.lang.RuntimeException: mssql_shaded.com.nimbusds.oauth2.sdk.http.HTTPRequest.getEntityContentType()Lmssql_shaded/com/nimbusds/common/contenttype/ContentType;
Caused by: RuntimeException: mssql_shaded.com.nimbusds.oauth2.sdk.http.HTTPRequest.getEntityContentType()Lmssql_shaded/com/nimbusds/common/contenttype/ContentType;

1 answer

PRADEEPCHEEKATLA-MSFT 85,746 Reputation points Microsoft Employee

2021-07-15T05:52:03.44+00:00

Hello @Pradeep Thotakura ,

Thanks for the sharing the details.

Unfortunately, AAD credentials as basic authentication is not supported in Azure Synapse Analytics.

If you want to use user identities (and not SPN, for which there seems to be support - OAuth 2.0 with a service principal). The username/password doc should apply to sql auth support (sql users created within sql itself).

The following sections describe each connection’s authentication configuration options.

The Spark driver can connect to Azure Synapse using JDBC with a username and password or OAuth 2.0 with a service principal for authentication.

I had clearly explained the both the methods here: Azure databricks to Azure synapse Service Principal Authentication.

Hope this helps. Do let us know if you any further queries.

---------------------------------------------------------------------------

Please "Accept the answer" if the information helped you. This will help us and others in the community as well.
Please sign in to rate this answer.
PRADEEPCHEEKATLA-MSFT 85,746 Reputation points Microsoft Employee

2021-07-16T11:50:40.397+00:00

Hello @Pradeep Thotakura ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.

PRADEEPCHEEKATLA-MSFT 85,746 Reputation points Microsoft Employee

2021-07-27T10:55:57.483+00:00

Hello @Pradeep Thotakura ,

Following up to see if the above suggestion was helpful. And, if you have any further query do let us know.
Sign in to comment

Share via

How to export Databricks dataframe(Pandas or Scala) to Synapse analyitcs SQL table using Active Directory Password

1 answer