0

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

Coder62349
  • 13
  • 1
  • 6
  • did you follow all the steps from here: https://techcommunity.microsoft.com/t5/azure-database-support-blog/cross-database-query-in-azure-sql-database/ba-p/369126 – Hassan Raza Sep 20 '20 at 13:04
  • Yes, that is the same procedure I have followed to setup the cross database link. – Coder62349 Sep 20 '20 at 13:29
  • 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.Mc Sep 20 '20 at 13:34
  • 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 Sep 20 '20 at 13:40
  • 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 Sep 21 '20 at 16:01
  • Hi @LeonYue. Yes it's maybe time to create a support ticket... – Coder62349 Sep 27 '20 at 07:58
  • @Coder62349 yes, waiting for you update. – Leon Yue Sep 27 '20 at 12:20

2 Answers2

0

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

Burak Tokak
  • 1,810
  • 1
  • 20
  • 27
vincentvega832
  • 1
  • The connection between a VM in Azure and the database works fine with Private Link and a proper DNS. The problem is that external tables in one database that points to tables in another database (on the same server) is not working. I have also tried to use the yourdbname.privatelink.database.windows.net DNS when creating the external datasource, but it still doesn't work :-( – Coder62349 Sep 27 '20 at 07:54
  • Sorry to thear that. I'll let you know if I find something. How did you config the dns? Are you using an Azure Resource for DNS? My endpoint has a private ip and I need to point it to privatelink.database.windows.net. Where can I create a DNS record for privatelink.database.windows.net??! – vincentvega832 Sep 28 '20 at 09:28
0

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.

Mike Ubezzi
  • 1,007
  • 6
  • 8
  • Please refer to this forum for additional info: https://learn.microsoft.com/en-us/answers/questions/110739/external-tables-not-working-when-deny-public-netwo.html?childToView=129270#answer-129270 – Coder62349 Oct 18 '20 at 12:09