SQL Server - Access denied when reading a file from Azure Blob Storage container using SAS key
1
vote
1
answer
1892
views
I have a requirement to read CSV files from an Azure blob storage. So far, this is throwing access denied errors every time I run my query:
CREATE DATABASE SCOPED CREDENTIAL
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET = 'sv=2021-06-08&ss=b&srt=sco&sp=rl&se=2023-03-31T09:38:05Z&st=2022-09-01T02:38:05Z...';
CREATE EXTERNAL DATA SOURCE
WITH (
TYPE = BLOB_STORAGE
, LOCATION = 'https://.blob.core.windows.net/ '
, CREDENTIAL= -->
);
SELECT *
FROM OPENROWSET (
BULK '/.csv'
, DATA_SOURCE = ''
, FORMAT ='CSV'
, FORMATFILE='/.fmt'
, FORMATFILE_DATA_SOURCE = ''
, FIRSTROW = 2
) AS test
Below are some more details on how everything was setup:
- The storage account kind is of BlockBlobStorage.
- In the **Firewalls and virtual networks setting**, it is only **Enabled
from selected virtual networks and IP addresses**. I already added my
public IP address, as well as the IP address of Azure SQL Server
which I got from here:
https://learn.microsoft.com/en-us/azure/azure-sql/database/connectivity-architecture?view=azuresql#gateway-ip-addresses
- The whole process works if I set it to Enabled from all networks. The
SQL server and the storage account lives within the same resource
group.
- I also configured a VNet that is both added for both of the resource.
- Saw this thread which is exactly similar to my issue, however the
accepted answer is not working from my end: https://stackoverflow.com/questions/58340185/cannot-bulk-load-because-the-file-file-csv-could-not-be-opened-operating-syst
I checked all the documentations regarding SAS access keys, database scoped credentials, external data sources and VNet networking and I don't see any limitations for SAS key access to be denied. Did I miss a configuration setup? I find it a little weird that in most cases, they are recommending to setup the storage account to be **Enabled from all networks**, which might be a security issue.
Asked by Dustine Tolete
(119 rep)
Oct 3, 2022, 03:38 AM
Last activity: May 5, 2025, 03:06 PM
Last activity: May 5, 2025, 03:06 PM