azure - Avoiding Access is Denied for Azure SQL External Data Source to Private BLOB on Virtual Network

Question

I have set up some SQL for a bulk insert from an external data source shown below. The external data source is a private blob so I'm using a (liberal) SAS secret.

CREATE DATABASE SCOPED CREDENTIAL mycred WITH IDENTITY = 'SHARED ACCESS SIGNATURE', SECRET = 'sv=2019-10-10&ss=bfqt&srt=sco&sp=rwdlacup&se=2025-04-30T01:41:12Z&st=2020-04-29T17:41:12Z&spr=https,http&sig=[REDACTED]';

CREATE EXTERNAL DATA SOURCE sqlinput WITH (
 TYPE = BLOB_STORAGE,
 LOCATION = 'https://test.blob.core.windows.net/mycontainer',
 CREDENTIAL = mycred,
);

BULK INSERT dbo.target
FROM 'data.csv'
WITH (DATA_SOURCE = 'sqlinput', FORMAT = 'CSV');

This works well! However my nightmare begins when I want to change the BLOB firewall from All Networks to Selected networks. Following this SQL will then produce the following error:

Cannot bulk load because the file "data.csv" could not be opened. Operating system error code 5(Access is denied.).

My approach then was to do the following:

1. In the BLOB storage account Firewalls and virtual networks I create a new virtual network
2. Then a private endpoint connection is created for the storage account (Microsoft.Storage/storageAccounts) targeting the resource's blobs. (10.0.0.4)
3. In SQL server's Firewalls and virtual networks I add this to the virtual network.
4. At some point in this process a Private DNS Zone also was created, containing a A host entry to the BLOB vnet IP (10.0.0.4)

This does not resolve the access denied issue for the SQL bulk insert. I did spin up a new VM targeting the same subnet for the new virtual network, and it was able to access storage via the SAS connection string in Storage Explorer. I verified on the VM that it was resolving test.blob.core.windows.net to the internalized IP. I also tried providing an endpoint to the SQL server (10.0.0.5) but this had no effect.

Also attempted: Change the LOCATION to test.privatelink.blob.core.windows.net which yields a different error:

Cannot bulk load because the file "data.csv" could not be opened. Operating system error code 12175(failed to retrieve text for this error. Reason: 317).

On the VM I attempted to use Storage Explorer with the connection string updated to include privatelink however it ended up in a recursive loop when trying to browse the BLOB container. I suspect this is not an improvement and a dead end as the virtual network should be resolving everything fine.

Note: All resources are in the same subscription/resource group/region

标签： azure