I am encountering an error when trying to debug a data flow in Azure Data Factory that connects to an Azure SQL Database. My linked service and dataset connections to the Azure SQL Database are working fine, and I can preview data without any issues.

Question

I am encountering an error when trying to debug a data flow in Azure Data Factory that connects to an Azure SQL Database. My linked service and dataset connections to the Azure SQL Database are working fine, and I can preview data without any issues. However, the problem arises specifically when I run the data flows. Could you help me understand why this is happening and how to resolve it?

Error Message :

Spark job failed: { "text/plain": "{"runId":"5a0e91a1-6c60-4b85-af5c-98958cb57b8b","sessionId":"d635311e-484d-4665-a36c-f95715c44a0f","status":"Failed","payload":{"statusCode":400,"shortMessage":"com.microsoft.dataflow.broker.MissingRequiredPropertyException: server is a required property for AzureSqlDatabase2.\ncom.microsoft.dataflow.broker.PropertyNotFoundException: Could not extract value from AzureSqlDatabase2","detailedMessage":"Failure 2024-05-21 02:12:38.407 failed DebugManager.processJob, run=5a0e91a1-6c60-4b85-af5c-98958cb57b8b, errorMessage=com.microsoft.dataflow.broker.MissingRequiredPropertyException: server is a required property for AzureSqlDatabase2.\ncom.microsoft.dataflow.broker.PropertyNotFoundException: Could not extract value from AzureSqlDatabase2"}}\n" } - RunId: 5a0e91a1-6c60-4b85-af5c-98958cb57b8b

Answer 1

I just posted this over on another blog, might help someone here:

I had to create a new Linked Service, this time using the "legacy" version and not the "recommended". Add my server name and db name and test. Then publish the changes with that new LS. (I might have been able to use the existing, so long as I published after) Then change my sources to use that new Linked Service (I was getting the same error as you but in CDC using the Linked Service direct). I then no longer got that error. I think there might be a bug (seen similar posts on MS) and using Legacy in the Linked Service avoids it

Answer 2

@ram boyapati

Thanks for using MS Q&A platform and posting your query.

The error message you're encountering in Azure Data Factory indicates that the "server" property is missing from the configuration for your Azure SQL Database connection. This property is crucial for ADF to identify the target server where your database resides.

Here's how to troubleshoot and fix the issue:

1. Verify Linked Service Configuration:

• Go to your Azure Data Factory and navigate to the linked service you've created for the Azure SQL Database.
• Check the connection details for the linked service. Ensure the "server" property is defined and contains the correct server name for your Azure SQL Database.

2. Using Dataset Parameters:

• If you're using a dataset that references the linked service and the server name is a parameter, make sure the parameter value is set correctly in the data flow activity.

3. Refresh Connection:

• Sometimes, a refresh can resolve temporary configuration issues. Try refreshing the connection for your linked service in ADF.

4. Documentation Reference:

For more detailed information on configuring Azure SQL Database connections in ADF, refer to the official Microsoft documentation: https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/connection-string-syntax

• Double-check for typos in the server name.
• If you're using a managed identity for accessing the database, ensure it has the necessary permissions.

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