Does Azure Synapse connector for Azure Databricks work for Dedicated SQL pool (formerly SQL DW)

Question

Does Azure Synapse connector for Azure Databricks work for Dedicated SQL pool (formerly SQL DW)

Sumanasaranjani S Kanchana 11

I have been trying to write a dataframe that is transformed in Azure Databricks into the Dedicated SQL Pool (formarly SQL DW). We are not using synapse so far in production.
I am following the instructions in the page https://learn.microsoft.com/en-us/azure/databricks/data/data-sources/azure/synapse-analytics#parameters

But I can't get it to work. My code creates parquet file in the ADLS Gen2 folder (based on the data in the dataframe). But then while it tries to copy from the intermediate storage to DW, it fails with the error:
com.microsoft.sqlserver.jdbc.SQLServerException: Not able to validate external location because The remote server returned an error: (403) Forbidden.

I am using below code:

val blobStorage = "myadls.dfs.core.windows.net"
val blobContainer = "udl-container"
val blobAccessKey = dbutils.secrets.get(scope = "mykeyvault", key = "key-ADLS2")

val tempDir = "abfss://" + blobContainer + "@" + blobStorage +"/MyPath"

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

val dwDatabase = "mydwdatabase"
val dwServer = "mydwserver.database.windows.net"
val dwUser = "dwUserId"
val dwPass = "dwUserPwd"
val dwJdbcPort = "1433"
val dwJdbcExtraOptions = "encrypt=true;trustServerCertificate=true;hostNameInCertificate=*.database.windows.net;loginTimeout=30;"
val sqlDwUrl = "jdbc:sqlserver://" + dwServer + ":" + dwJdbcPort + ";database=" + dwDatabase + ";user=" + dwUser+";password=" + dwPass + ";$dwJdbcExtraOptions"
val sqlDwUrlSmall = "jdbc:sqlserver://" + dwServer + ":" + dwJdbcPort + ";database=" + dwDatabase + ";user=" + dwUser+";password=" + dwPass

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

fa_df.write
.format("com.databricks.spark.sqldw")
.option("url", sqlDwUrlSmall)
.option("dbtable", "mydwTable")
.option("forwardSparkAzureStorageCredentials","true")
.option("tempDir", tempDir)
.mode("overwrite")
.save()

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-23T00:24:01.573+00:00

Hi @Sumanasaranjani S Kanchana ,

Thanks for using Microsoft Q&A !!
I have tried your code and it worked fine for me. I got some other error though but when I installed JDBC driver (com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8) it worked fine for me.

Can you please try installing the same and check if it works for you.
You can also verify it by using Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver") in notebook cell.

Please find below the code which I have used to copy data from one synapse table to another table using databricks.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-27T00:27:04.3+00:00

Hi @Sumanasaranjani S Kanchana ,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh
Sumanasaranjani S Kanchana 11 Reputation points

2021-07-27T01:14:11.693+00:00

Hi Saurabh,
Sorry for the late reply. I missed to see your answer here.
It sounds like you tried the code to connect to your synapse dedicated pool. We don't have a synapse workspace. We are still using former DW - dedicated pool. Could you also give it a try with former DW?

Thank you for the details about the library. I have it installed already and I verified it with your line of code.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-28T23:21:01.013+00:00

Hi @Sumanasaranjani S Kanchana ,
Sorry but I do not have former DW - dedicated pool to test this but I am checking internally if I can find anything about this issue.

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-08-04T00:43:01.677+00:00

Hi @Sumanasaranjani S Kanchana ,

I have tested this with former DW - dedicated pool and it worked fine with the installed JDBC driver (com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8.

Thanks
Saurabh
Sumanasaranjani S Kanchana 11 Reputation points

2021-08-04T01:22:02.613+00:00

Saurabh,

Thank you so much for testing this with former DW!
Did you use enableServicePrincipalAuth or forwardSparkAzureStorageCredentials? Also, Did you mount ADLS or set the spark context with some Service Principal name and password before you executed the lines of code I pasted above? Or did you just use ADLS storage Access Key and sql login for DW, like I had in my code?

I run into the afore said error when the connector is trying to read ADLS to load the DW table. The portion where it creates the DW table and copies the data into Temp path, is working fine. That is with forwardSparkAzureStorageCredentials option. With enableServicePrincipalAuth, it errors out without me doing anything and I couldn't find enough documentation to use that option (enableServicePrincipalAuth) correctly.

One more question: Could it be that my connector fails while trying to read data from ADLS into DW, due to some network setup security stuff? One thing I did check is that, all 3 ADLS, Databricks and DW are in the same single VNET. Could there still be some other security setup preventing the connection?
I was also going through https://chinnychukwudozie.com/2020/11/13/write-data-from-azure-databricks-to-azure-synapse-analyticsformerly-sql-dw-using-adls-gen-2/ but couldn't grasp all of it.

Thank you
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-08-09T20:30:51.46+00:00

Hi @Sumanasaranjani S Kanchana ,

I have the used your code which uses username and password. You need to use enableServicePrincipalAuth when you are using OAuth authentication to connect a service principal for authentication which is currently in preview. If you are using OAuth authentication then you need to provide your service principal (registered application) to underlying Storage account of Azure Synapse Analytics service (Storage Blob Data Contributor role). Have you done that ? Please refer to this documentation on using enableServicePrincipalAuth and let me know if you still see an issue.

Thanks
Saurabh

1 answer

Your answer

Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-23T00:24:01.573+00:00

Hi @Sumanasaranjani S Kanchana ,

Thanks for using Microsoft Q&A !!
I have tried your code and it worked fine for me. I got some other error though but when I installed JDBC driver (com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8) it worked fine for me.

Can you please try installing the same and check if it works for you.
You can also verify it by using Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver") in notebook cell.

Please find below the code which I have used to copy data from one synapse table to another table using databricks.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-27T00:27:04.3+00:00

Hi @Sumanasaranjani S Kanchana ,

We haven't heard back from you. Just wanted to check if you are you still facing the issue? In case If you already found a solution, would you please share it here with the community? Otherwise, let us know and we will continue to engage with you on the issue.

Thanks
Saurabh
Sumanasaranjani S Kanchana 11 Reputation points

2021-07-27T01:14:11.693+00:00

Hi Saurabh,
Sorry for the late reply. I missed to see your answer here.
It sounds like you tried the code to connect to your synapse dedicated pool. We don't have a synapse workspace. We are still using former DW - dedicated pool. Could you also give it a try with former DW?

Thank you for the details about the library. I have it installed already and I verified it with your line of code.
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-07-28T23:21:01.013+00:00

Hi @Sumanasaranjani S Kanchana ,
Sorry but I do not have former DW - dedicated pool to test this but I am checking internally if I can find anything about this issue.

Thanks
Saurabh
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-08-04T00:43:01.677+00:00

Hi @Sumanasaranjani S Kanchana ,

I have tested this with former DW - dedicated pool and it worked fine with the installed JDBC driver (com.microsoft.sqlserver:mssql-jdbc:7.4.1.jre8.

Thanks
Saurabh
Sumanasaranjani S Kanchana 11 Reputation points

2021-08-04T01:22:02.613+00:00

Saurabh,

Thank you so much for testing this with former DW!
Did you use enableServicePrincipalAuth or forwardSparkAzureStorageCredentials? Also, Did you mount ADLS or set the spark context with some Service Principal name and password before you executed the lines of code I pasted above? Or did you just use ADLS storage Access Key and sql login for DW, like I had in my code?

I run into the afore said error when the connector is trying to read ADLS to load the DW table. The portion where it creates the DW table and copies the data into Temp path, is working fine. That is with forwardSparkAzureStorageCredentials option. With enableServicePrincipalAuth, it errors out without me doing anything and I couldn't find enough documentation to use that option (enableServicePrincipalAuth) correctly.

One more question: Could it be that my connector fails while trying to read data from ADLS into DW, due to some network setup security stuff? One thing I did check is that, all 3 ADLS, Databricks and DW are in the same single VNET. Could there still be some other security setup preventing the connection?
I was also going through https://chinnychukwudozie.com/2020/11/13/write-data-from-azure-databricks-to-azure-synapse-analyticsformerly-sql-dw-using-adls-gen-2/ but couldn't grasp all of it.

Thank you
Saurabh Sharma 23,846 Reputation points Microsoft Employee Moderator

2021-08-09T20:30:51.46+00:00

Hi @Sumanasaranjani S Kanchana ,

I have the used your code which uses username and password. You need to use enableServicePrincipalAuth when you are using OAuth authentication to connect a service principal for authentication which is currently in preview. If you are using OAuth authentication then you need to provide your service principal (registered application) to underlying Storage account of Azure Synapse Analytics service (Storage Blob Data Contributor role). Have you done that ? Please refer to this documentation on using enableServicePrincipalAuth and let me know if you still see an issue.

Thanks
Saurabh

Answer 1

Sumanasaranjani S Kanchana 11

I was able to connect only using "useAzureMSI". I think it is because of the VNETs we have. Thank you for all the help. This issue is solved now.

Share via

Does Azure Synapse connector for Azure Databricks work for Dedicated SQL pool (formerly SQL DW)

1 answer

Your answer