SqlDWConnectorException: Exception encountered in Azure Synapse Analytics connector code.

Question

Hello Team,

I am trying to connect to Synaose analytucs via Databricks.I have used the below code :

spark.conf.set(
"fs.azure.account.key.xyz.dfs.core.windows.net",
"BcM/ifAQEUTjkhWb59yjxKiw21HK9D1P63ec5xHQCS9XcYIAPA2rR5RNm6vy/huJJqR4BakPGcyEpL0P2OCAMg==")

Read from Azure Synapse table via spark.read.load

df = spark.read \
.format("com.databricks.spark.sqldw") \
.option("url", "jdbc:sqlserver://xyz;AccountKey=BcM/ifAQEUTjkhWb59yjxKiw21HK9D1P63ec5xHQCS9XcYIAPA2rR5RNm6vy/huJJqR4BakPGcyEpL0P2OCAMg==;EndpointSuffix=core.windows.net") \
.option("tempDir", "abfss://xyz@tbuestbiscwe1.dfs.core.windows.net/Bronze/Salesforce/Location/**") \
.option("forwardSparkAzureStorageCredentials", "true") \
.option("dbTable", "Account") \
.load()

And i am getting error :

com.databricks.spark.sqldw.SqlDWConnectorException: Exception encountered in Azure Synapse Analytics connector code.

And all the Connection string and tempdir all are correct. please advise me if i am missing anything.

Regards
Rk

Answer 1

Hello @Rohit Kulkarni ,

Thanks for the question and using MS Q&A platfrom.

If you look at the code the url variable is not provided correctly: "jdbc:sqlserver://<the-rest-of-the-connection-string>"

Note: It should be JDBC connection string of the database and not the Account Key of the Azure Storage account.

JDBC (SQL authentication): jdbc:sqlserver://<workspaceName>.sql.azuresynapse.net:1433;database=<sqlpoolName>;user=<UserName>;password={your_password_here};encrypt=true;trustServerCertificate=false;hostNameInCertificate=*.sql.azuresynapse.net;loginTimeout=30;  

To resolve this issue - I would request to check the complete stacktrace to find the esact reason for the failure of the query.

Note: If you scroll down the side bar - you will be able to find the exact reason for the failure similar to below screenshot:

In case, if you are unable to find the resolution - you can share the complete stacktrace of the error message which you are experiencing?

Reading the table data from Azure Synapse Analytics using Azure Databricks:

For more details, refer to Azure Synapse Analytics.

Hope this will help. Please let us know if any further queries.

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

• Please don't forget to click on or upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
• Want a reminder to come back and check responses? Here is how to subscribe to a notification
• If you are interested in joining the VM program and help shape the future of Q&A: Here is jhow you can be part of Q&A Volunteer Moderators

Answer 2

Hey there I'm having a similar problem, I have checked the connection string, the secret for the credentials, and nothing also the error code, doesn't provide any other info

Any help is welcomed