how to fix 'Only one valid authentication should be used for AzureSynapseAnalytics'

Question

Hi all

I am running the training https://learn.microsoft.com/en-us/training/modules/code-free-transformation-scale/4-author-azure-data-factory-mapping-data-flow. I am using namings according the learning module, e.g. creating 'dataflow1'. When reaching task 4: writing to a data sink, I am running in an authentication issue ...

• linked service 'AzureSynapseAnalytics1' seems to work fine when testing with 'sqladminuser' and custom pwd
• dataset 'AzureSynapseAnalyticsTable1' using above linked service seems to work fine when testing connection
• creating 'sink1' with above dataset, testing connection fails with following exception ...

Spark job failed: { "text/plain": "{"runId":"d73a8129-9dc7-4e35-a21c-acc63e31e6f7","sessionId":"2c188be0-6e86-44e5-83a7-cbf93cbf768e","status":"Failed","payload":{"statusCode":400,"shortMessage":"com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for AzureSynapseAnalytics1. SQLAuthentication is invalid. One of user/password is missing.","detailedMessage":"Failure 2024-08-12 12:22:45.577 failed DebugManager.processJob, run=d73a8129-9dc7-4e35-a21c-acc63e31e6f7, errorMessage=com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for AzureSynapseAnalytics1. SQLAuthentication is invalid. One of user/password is missing."}}\n" } - RunId: d73a8129-9dc7-4e35-a21c-acc63e31e6f7

• running the corresponding pipeline1, I am getting similar exception message ...

Operation on target dataflow1 failed: {"StatusCode":"DFExecutorUserError","Message":"Job failed due to reason: com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for AzureSynapseAnalytics1. SQLAuthentication is invalid. One of user/password is missing.","Details":""}

please advice, without getting this solved, I am oviously blocked on my learning path

Urs Mueller

This question is related to the following Learning Module

Answer 1

Hi ursmueller,

Thank you for reaching out to us on the Microsoft Q&A forum.

It appears that you are trying to run a Spark job or a pipeline that involves connecting to an Azure Synapse Analytics instance. The error message suggests that the authentication configuration for the Azure Synapse Analytics connection is invalid.

Here are possible reasons for this error that might help you:

• Missing or incorrect username and/or password: The error message indicates that one of the user or password fields is missing. Make sure that you have provided the correct username and password for the Azure Synapse Analytics instance.
• Multiple authentication methods: The error message mentions that only one valid authentication should be used. This might indicate that you have configured multiple authentication methods (e.g., SQLAuthentication and another method) for the same Azure Synapse Analytics connection. Ensure that you are using only one valid authentication method.

For more details please refer: SQL Authentication in Azure Synapse Analytics

Incorrect connection configuration: Double-check the connection configuration for Azure Synapse Analytics, including the server name, database name, username, and password.

To resolve the issue, review your Azure Synapse Analytics connection configuration and ensure that the authentication details are correct and properly configured.

Please don't hesitate to reach out to us if you have any other queries.

If you found the information helpful, we would greatly appreciate it if you could acknowledge it by selecting the Accept Answer & Upvote options.