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

Erik Olofsson 36

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

Mike Ubezzi 2,776 Reputation points

2020-10-01T15:04:12.45+00:00

Hi, @Erik Olofsson . Can you detail your VNET configuration for your Azure SQL Database instance? I am looking for services that are part of the VNET, not part of the VNET, and any subnet specific services. The second item is the full connection string being used to attempt the connection to the external tables? The FQDN string is NAT'd to the private IP address that was assigned by the Private Link configuration. Are the databases in separate VNETs?
Erik Olofsson 36 Reputation points

2020-10-02T05:45:59.69+00:00

Hi @Mike-Ubezzi-MSFT. The SQL database is connected via Private Link to a subnet (10.1.1.0/24) in a vNet (10.1.0.0/16). The internal IP to the SQL server is 10.1.1.5. There is only one database server with two databases in it, so everything is connected to the same vNet. Since the tables are external, I use the same connection string to query them as when I query ordinary tables. I can test this by connecting via SSMS (over vpn) to the database and if I query an ordinary table it works but when I query an external table I get the above error message. It's the internal communication between database A and database B that is failing. The external data source and tables are created with these commands:

CREATE EXTERNAL DATA SOURCE [mydatasource] WITH (TYPE = RDBMS, LOCATION = N'mydbserver.database.windows.net', CREDENTIAL = [xxx], DATABASE_NAME = N'mydatabase')

CREATE EXTERNAL TABLE [dbo].[XXX] (...) WITH (DATA_SOURCE = [mydatasource])

/Erik
Mike Ubezzi 2,776 Reputation points

2020-10-05T23:07:11.273+00:00

Thank you for the additional details, @Erik Olofsson . Do you have the Microsoft.SQL NSG enabled in that VNET? Please see: Create, change, or delete a network security group

Also, you you should always reference the FQDN in the connection string as you did before enabling private endpoint. With private endpoint enabled, there is a network translation (NAT) that takes place. Referencing your instance by any other unique identifier such as private or public IP address or <instance_name>.privatelink.database.windows.net will fail with a certificate error. So, if this is the case...there is likely the Microsoft.sql NSG missing from the VNET/subnet configuration.
Erik Olofsson 36 Reputation points

2020-10-06T06:16:52.317+00:00

Hi @Mike-Ubezzi-MSFT. No, I don't have any Network Security Group configured. It seems to me that you're talking about Service Endpoint? If yes, I have Microsoft.Sql configured on the private link vNet subnet. It's status is set to Succeeded.
Vincent 1 Reputation point

2020-10-09T08:56:43.227+00:00

We're having same issue.

@Erik Olofsson Was you able to resolve this issue?
Mike Ubezzi 2,776 Reputation points

2020-10-12T18:30:19.213+00:00
I apologize for the delay in getting back to you and thank you for the additional detail. There are quite a few parts to this along with some service interruptions to networking services back on the 7th, I am working to weed through the possible nuances:

Connection policy needs to be set to Proxy Connection as per the Private link availability for Azure SQL Database considerations.

Azure SQL Database

All public regions
All Government regions
All China regions

Supported for proxy connection policy

GA
Learn how to create a private endpoint for Azure SQL
Mike Ubezzi 2,776 Reputation points

2020-10-12T18:41:27.733+00:00

There are limited characters available in a comment and following up here with additional information. The connection policy needs to be proxy mode for private link to function, and possibly you have redirect setup? I can step through this document to attempt to reproduce your issue, including the second database with the external table functionality but, I am hoping to flush the issue out through this process.
Vincent 1 Reputation point

2020-10-13T02:11:48.42+00:00

Hello Mike,
I got same issue @Erik Olofsson .

I've try your suggestion on using Proxy Mode but still unlucky.
I only can set Proxy Mode at Firewall Level, not at per Private Link level. Could you share any detail document or steps for this configuration?
Mike Ubezzi 2,776 Reputation points

2020-10-16T04:20:42.583+00:00

@ErikOlofsson - Did you get this resolved? This appears to be an issue with DNS/NAT when calling the external table by the FQDN as the traffic should route to the private IP you indicated but as per the error message you are seeing, it is routing to the public Azure network. You verified connectivity via SSMS: Check connectivity using SQL Server Management Studio (SSMS)

So, there is either a limitation or an service issue with this scenario. Let me reach out internally to see if I can confirm or substantiate this. Regards, Mike.
Mike Ubezzi 2,776 Reputation points

2020-10-16T04:28:26.31+00:00
@ErikOlofsson - Did you get this resolved? This appears to be an issue with DNS/NAT when calling the external table by the FQDN as the traffic should route to the private IP you indicated but as per the error message you are seeing, it is routing to the public Azure network. You verified connectivity via SSMS: Check connectivity using SQL Server Management Studio (SSMS)

So, there is either a limitation or an service issue with this scenario. Let me reach out internally to see if I can confirm or substantiate this. Regards, Mike.

Additional Information:

How to set up Private Link for Azure SQL Database (link)
Erik Olofsson 36 Reputation points

2020-10-16T06:36:59.1+00:00

Hi Mike, thanks for your help. No, the issue is not resolved so please do some internal research on your side. As you may have noticed, other people have the same problem. There is also a SO thread (created by me) on the same topic here: external-tables-not-working-when-deny-public-network-access-is-set-to-yes

If this is a limitation it should be clearly documented. It's very frustrating when you invest a lot of time in a new architecture and it fails on something that is impossible for you to predict. The team behind the Private Link service should be well aware that using external tables in sql database is very common.

Accepted answer

Mike Ubezzi 2,776 Reputation points

2020-10-16T20:12:28.083+00:00

Here is the situation, @Erik Olofsson - 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.
Please sign in to rate this answer.

1 person found this answer helpful.
Erik Olofsson 36 Reputation points

2020-10-18T12:06:51.913+00:00

Ok, that explains why it's not working. Since the data (in the external tables) is not static, I don't think it's worth synchronizing it to a storage account. A simple workaround would be to sync the data from the source database to the destination and use real tables in the destination instead of external tables.

Anyway, thank you for confirming the cause of the issue. Do you know if there are any plans for making Polybase support Private Link?

Mike Ubezzi 2,776 Reputation points

2020-10-19T04:44:40.337+00:00

I have used this insight to provide visibility into the demand for this functionality...thank you for the additional workaround option detail as well. I don't have any timelines or release date information but let's use the following Uservoice forum post (Enable same region support for VNET Integration) to drive the conversation further with regard to Polybase + VNET functionality.
Sign in to comment

Share via

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

0 additional answers