External Table in Azure SQL Database

Question

I am following the below MSFT doc for the preview functionality :

https://learn.microsoft.com/en-us/azure/azure-sql/database/data-virtualization-overview?view=azuresql&tabs=sas

Stg account details :  

Account name : fabricdemostgaccount

container name : network

Queries executed :

CREATE MASTER KEY 

ENCRYPTION BY PASSWORD = '<<>>';

CREATE DATABASE SCOPED CREDENTIAL MyCredential

WITH IDENTITY = 'SHARED ACCESS SIGNATURE',

SECRET = 'sv=2024-11-04&ss=b&srt=c&sp=rwdlactfx&se=2';

CREATE EXTERNAL DATA SOURCE MyPrivateExternalDataSource

WITH (

    LOCATION = 'abs://[******@fabricdemostgaccount.blob.core.windows.net]' ,

       CREDENTIAL = [MyCredential]

);

CREATE EXTERNAL FILE FORMAT FileCSV

WITH (FORMAT_TYPE = DELIMITEDTEXT,

      FORMAT_OPTIONS(

          FIELD_TERMINATOR = ',',

          STRING_DELIMITER = '"',

          FIRST_ROW = 2,

          USE_TYPE_DEFAULT = True)

);

CREATE EXTERNAL TABLE extnetwork

(

 C1 int,

 C2 int,

 C3 int

)

WITH (

 LOCATION = N'/sample/sample.csv',

 DATA_SOURCE = MyPrivateExternalDataSource,

 FILE_FORMAT = FileCSV

);

When I execute a Select * from extnetwork ; I am getting the below error :

What might I be doing wrong ?

Answer 1

Hi @Nandan Hegde

Thanks for providing the latest updates on the reported issue, here are the different ways to handle the network blockages -

·         Use IP-Based Firewall Rules - This ensures only trusted IPs can access the storage, avoiding full public exposure. [Network Ac...lytics ...]

·         Enable “Allow Azure Services” Option - If your Azure SQL Database and Storage Account are in the same tenant, enabling this option allows secure access without needing to open public IPs.

How to enable:

• In the Storage Account → Networking → Firewalls and virtual networks → Check “Allow Azure services on the trusted services list to access this storage account.”

This is a secure way to allow internal Azure services like SQL to access storage.

·         Use Private Endpoints (Private Link) - For enterprise-grade security, configure Private Link to create a private endpoint between Azure SQL and the Storage Account.

·         Private Endpoints (Private Link) - configure Private Link to create a private endpoint between Azure SQL and the Storage Account.

Requires setup of DNS and virtual network rules. [Virtual ne...L Database]

·         Virtual Network Service Endpoints - If Private Link is too complex for your use case, Service Endpoints allow secure access from a VNet to the storage account.

Configuration:

·         Enable service endpoints for Azure Storage in your VNet.

·         Add VNet rules to the storage account.

This restricts access to only resources within the VNet. https://learn.microsoft.com/en-us/azure/azure-sql/database/vnet-service-endpoint-rule-overview?view=azuresql

·         SAS Token Permissions

Ensure your SAS token includes:

·         sp=rl (read and list)

·         srt=sco (service, container, object)

·         spr=https (secure protocol)

·         Valid st and se timestamps

This ensures Azure SQL can list and read the file without being blocked by network rules. [Troublesho...soft Learn]

 

 Thanks,
Vrishabh