Connection Failed - The value of the property '' is invalid

Question

I am trying to set up a "Source Data Store" in Azure Data Factory, using an existing private endpoint to an Azure SQL Managed Instance. I keep getting the error:

"Connection failed - The value of the property '' is invalid: 'Value does not fall within the expected range.'. Value does not fall within the expected range."

The private endpoint must be valid, because I can see the list of databases on that SQL Instance in the "Edit Linked Service" UI. What parameter could be missing? How do I troubleshoot this?

Answer 1

Hi @sguidos,

It seems that you have some doubts regarding the assumptions mentioned.

I am not encountering a network, firewall or virtual network issue. Is this a correct assumption?

It is safe to assume there are no network, firewall, or virtual network issues. When you select "From Azure Subscription" and a "Server name," ADF uses the Azure Resource Manager (ARM) APIs to query and list databases for the selected Managed Instance. Your SQL MI is accessible to ADF through the private endpoint or a network connection. Necessary permissions for ADF to read database metadata are in place.

What mechanism is the Web UI using to connect with my SQL Managed Instance and get the list of database names? My SQL Managed Instance does not have a public endpoint, so I assume that the Web UI is accessing the instance using my private endpoint, and thus my private endpoint must be set up correctly. Are these correct assumptions?

Your assumption is correct, that the private endpoint is correctly configured is likely accurate if you can see database names in the dropdown. The Web UI leverages ARM(Azure Resource Manager) APIs to retrieve metadata about the SQL Managed Instance, including the list of databases. Since your SQL Managed Instance does not have a public endpoint, ADF must be accessing it through the private endpoint. The network setup allows ADF to access the private endpoint.

Still I get the "Connection failed - The value of the property '' is invalid error message.

The error you are encountering due to various reasons. You can diagnose the issue potentially using following steps.

Verify Credentials: Verify that the SQL Authentication, Managed Identity, or Microsoft Entra ID(Azure AD) matches what is configured on your SQL MI. Make sure that, the provided username/password or Managed Identity has the necessary permissions on the SQL MI.

Improper JSON Construction by the UI: The Web UI generates JSON for the connection. It's unlikely (but possible) that there's a bug in the UI. To verify this:

Debugging: Use browser developer tools to inspect the network requests sent when creating the connection. Look for the generated JSON payload in the "Request Payload" section.

If you suspect an issue, manually test the connection by creating a linked service in ADF using the ARM Template or PowerShell with correctly formatted JSON.

Private Endpoint DNS Resolution: If DNS for the private endpoint is misconfigured, ADF might resolve the SQL MI hostname to the wrong IP. Confirm this by testing connectivity using tools like Azure Bastion or a Virtual Machine in the same network.

If none of these steps resolve the issue, please file a support ticket for deeper investigation and do share the SR# with us? In case if you don't have a support plan please let us know here.

I hope, This response will address your query and helped you to overcome on your challenges. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.