Cannot connect to Azure SQL source from mapping data flow

Question

Hello Microsoft Support,

I'm experiencing an issue with Azure Data Factory when executing a Mapping Data Flow that involves a Calendar_Sql source. The Spark job fails with the following error message:

Spark job failed: {

"text/plain": "{"runId":"77f4368e-3ac7-47a8-8be2-7fe29ffa3467","sessionId":"47028b41-6479-465a-b8eb-950355d77dd0","status":"Failed","payload":{"statusCode":400,"shortMessage":"com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for Calendar_Sql. SQLAuthentication is invalid. One of user/password is missing.","detailedMessage":"Failure 2024-11-27 22:39:07.675 failed DebugManager.processJob, run=77f4368e-3ac7-47a8-8be2-7fe29ffa3467, errorMessage=com.microsoft.dataflow.broker.InvalidOperationException: Only one valid authentication should be used for Calendar_Sql. SQLAuthentication is invalid. One of user/password is missing."}}\n"

}

RunId: 77f4368e-3ac7-47a8-8be2-7fe29ffa3467

Details:

• Data Source: Calendar_Sql
• Authentication Method: SQL Authentication
• Issue: The error indicates that SQL Authentication is invalid because either the username or password is missing.

Additional Context:

• When I navigate to the batchidlist source and use the "Test Connection" feature, the connection succeeds without any issues.

• However, when I attempt to test the connection within the Mapping Data Flow's source settings, it fails and throws the above error.

Questions:

1. What could be causing the authentication error in the Mapping Data Flow despite the successful connection test on the batchidlist source?
2. How can I resolve the InvalidOperationException related to SQL Authentication in the Mapping Data Flow?
3. **Are there specific configurations or best practices I should follow when setting up SQL Authentication for sources within Mapping Data Flows to prevent such issues?
   **
   I would appreciate guidance on troubleshooting this authentication issue within the Mapping Data Flow. Any insights or recommended steps to resolve this error would be highly valuable. Thank you!

Answer 1

Hi @Max Teitelbaum

The error message indicates that SQL Authentication is invalid because either the username or password is missing.

Regarding your first question, it is possible that the successful connection test on the batchidlist source is not related to the authentication error in the Mapping Data Flow.

The Mapping Data Flow may be using a different authentication method or may have different authentication settings.

To resolve the InvalidOperationException related to SQL Authentication in the Mapping Data Flow, you can try the following steps:

1. Check the authentication settings in the Mapping Data Flow's source settings to ensure that the correct authentication method is selected and that the username and password are provided correctly.
2. Verify that the SQL Server instance is configured to allow SQL Authentication. You can do this by checking the SQL Server instance properties in SQL Server Management Studio.
3. Ensure that the SQL Server instance is accessible from the Azure Data Factory environment. You can test this by connecting to the SQL Server instance from a machine in the same network as the Azure Data Factory environment.

Regarding your third question, there are some best practices you can follow when setting up SQL Authentication for sources within Mapping Data Flows to prevent such issues.

These include:

1. Using a secure password for the SQL Authentication account.
2. Ensuring that the SQL Authentication account has the necessary permissions to access the required data.
3. Using a dedicated SQL Authentication account for each Mapping Data Flow to limit the scope of any potential security breaches.

I hope this helps.