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

0
votes

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://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-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...

2
sqlazureazure-sql-databasevnetazure-private-link
did you follow all the steps from here: techcommunity.microsoft.com/t5/azure-database-support-blog/… – Hassan Raza
Yes, that is the same procedure I have followed to setup the cross database link. – Coder62349
We also discovered that external tables don't work with private link. You should get out of the habit of using multiple databases and instead use one database with multiple schemas. – Nick.McDermaid
Ok, have you got a confirmation from Microsoft that Private Link does not support external tables? I havn't seen anything about it in the docs. I don't have the option to put everything in the same database with different schemas. Every customer has it's own database and every database links to a master database. – Coder62349
Hi @Coder62349, no documents talked about this, I think the best way is ask Azure support team for help. But this may take some money and time. – Leon Yue

2 Answers

0
votes

You need to use the name yourdbname.privatelink.database.windows.net

Afterwards you'll maybe receive another error that this name is incorrect. In this case you're experiencing a DNS problem and you need to add an entry in the host file of your VM with the IP of the endpoint. If your VM is outside of that VNET, it's another story.

Then you need to add the public IP of your endpoint in your hostfile. I'm still trying to solve this with a serious dns, haven't figured it out yet.

For More information see this;

https://techcommunity.microsoft.com/t5/azure-database-support-blog/lesson-learned-126-deny-public-network-access-allow-azure/ba-p/1244037

0
votes

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.