Managed private endpoint and Azure SQL Database

[thuanss]

I have created a managed private endpoint in my workspace, with my Azure SQL Database as a resource identifier. I have also approved the newly created endpoint in the Azure SQL Database. When I connect to it (using the Copy data assistant task), it always shows an error message, telling me that my Azure SQL database disabled the public network access and suggesting that I enable it. 

An exception occurred: DataSource.Error: Microsoft SQL: Reason: An instance-specific error occurred while establishing a connection to SQL Server. Connection was denied since Deny Public Network Access is set to Yes (https://docs.microsoft.com/azure/azure-sql/database/connectivity-settings#deny-public-network-access). To connect to this server, use the Private Endpoint from inside your virtual network (https://docs.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-p...).

Is there a way to connect to a private Azure SQL Database without creating and running on-premises data gateway software on an Azure VM? I thought that the managed private endpoint would provide me with seamless connectivity 

[v-hashadapu]

Hi @thuanss , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @thuanss , Hope your issue is solved. If it is, please consider marking the answer 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details.
Thank you.

[v-hashadapu]

Hi @thuanss , Hope your issue is solved. If it is, please consider marking it 'Accept as solution', so others with similar issues may find it easily. If it isn't, please share the details. Thank you.

[v-hashadapu]

Hi @thuanss , thank you for reaching out to the Microsoft Fabric Community Forum.

Please consider below steps:

1. Ensure that the managed private endpoint is correctly configured and approved in both the Azure SQL Database and your workspace. Double-check that the endpoint is properly linked to your virtual network.
2. Ensure that the Network Security Group (NSG) rules allow traffic from your virtual network to the Azure SQL Database. Verify that there are no conflicting rules that might be blocking the connection.
3. Make sure that your private DNS zone is correctly configured to resolve the private endpoint's DNS name. Verify that the DNS settings are propagated correctly within your virtual network.
4. Since the error message suggests enabling public network access, ensure that "Deny Public Network Access" is set to "No" in your Azure SQL Database connectivity settings. This setting should be disabled to allow connections through the private endpoint.
5. Use tools like Azure Bastion or a virtual machine within your virtual network to test connectivity to the Azure SQL Database. Ensure that you can connect to the database from within the virtual network without any issues.
6. Please refer to below links:

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-portal

https://learn.microsoft.com/en-us/azure/azure-sql/database/private-endpoint-overview?view=azuresql

https://learn.microsoft.com/en-us/azure/private-link/tutorial-private-endpoint-sql-cli

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.

[thuanss]

I have been using the private endpoint pattern for over a year. This is not the first time I set it up. I think Microsoft Fabric still doesn't support the Copy data. I have done the on-premises data gateway setup, and it works well, but I don't want to manage an infrastructure for it if there is anything better.

[v-hashadapu · Accepted solution]

Hi @thuanss , thank you for reaching out to the Microsoft Fabric Community Forum.

You are correct that Microsoft Fabric currently does not natively support direct connectivity to private endpoints for Azure SQL Database through the Copy Data feature without using an on-premises data gateway. The managed private endpoint in your workspace establishes a secure connection, but as of now, Fabric's native integration doesn't fully utilize these endpoints for certain tasks, including Copy Data.

And you are right, managing additional infrastructure can be a hassle. If possible, please consider below:

1. Consider using Azure Synapse Analytics or standalone Azure Data Factory, which provide better integration with private endpoints without requiring an on-premises gateway. Once data is copied or transformed, you can integrate it back into Fabric for further processing.
2. Consider Using an Azure Integration Runtime (IR) with a Managed Virtual Network as when you create an Azure Integration Runtime within a Managed Virtual Network, the IR is provisioned with the Managed VNet and uses private endpoints to securely connect to supported data stores. This eliminates the need for an on-premises gateway.
3. Using service principal authentication with Azure Data Factory can provide a more seamless way to connect to your Azure SQL Database without needing an on-premises data gateway.

If this helps, please consider marking it 'Accept as Solution' so others with similar queries may find it more easily. If not, please share the details.
Thank you.