sqlazureazure-sql-databasevnetazure-private-link

External tables not working when "Deny public network access" is set to Yes


I have enabled Private link by setting the "Deny public network access" knob to Yes in the Firewall settings on my Azure SQL Database server. Everything is working as expected except external data sources (external tables). The external tabels are simply links to tables in another Azure SQL database that belongs to the same server. Before I enabled the Private link, everything worked fine. If I try to query the external tables I get this error message:

"Error retrieving data from [mydbserver].database.windows.net.[mydbname]. The underlying error message received was: '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://learn.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://learn.microsoft.com/azure/sql-database/sql-database-private-endpoint-overview#how-to-set-up-private-link-for-azure-sql-database)."

I can't find anything in the docs about any limitation regarding external data sources and external tables in combination with Private Link setup.

The external tables where created using the standard way: "CREATE EXTERNAL DATA SOURCE" and "CREATE EXTERNAL TABLE". I have also tried to recreate the data source and the tables after enabling Private Link, but the error remains...


Solution

  • Want to reiterate the answer to the same question posted on Microsoft Q&A: External tables not working when “Deny public network access” is set to Yes

    The limitation is with Polybase as it currently does not support Private Link at this time. As per the PG:

    Polybase does not support using private link at this time. Please direct the customer to use Managed Identity to secure the connection to Azure Storage.

    Albeit, this may not be a workable solution for you but, if the data you need to access is extracted to a storage account and then imported via the method referenced by the PG, this could be a workable solution. The same process is reversed with flip/flop endpoints, and could be done within the security of a VNET + Managed Identity.