SQL Server - Access denied when reading a file from Azure Blob Storage container using SAS key

Question

SQL Server - Access denied when reading a file from Azure Blob Storage container using SAS key

Dustine Todd 1

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

Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-10-04T06:29:21.333+00:00

is there a private endpoint configured for the Storage Account? if yes, is the traffic going via On-Premise DNS or Cloud DNS?
Dustine Todd 1 Reputation point

2022-10-04T06:31:12.917+00:00

No, there are no private endpoints configured in the storage account.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-04T08:17:12.43+00:00

Hi @Dustine Todd ,

Welcome to Microsoft Q&A platform and thanks for using Azure Services.

As I understand your ask, you are getting "Permission denied" error when reading a file from Azure Blob Storage container using SAS key.

Sorry for the inconvenience you are facing.

Since you have tried multiple workarounds already as mentioned in the question, I would request you to once check the CSV file as described in the link azure-blob-to-azure-sql-database-cannot-bulk-load-because-the-file-xxxx-csv-c

Let us know if this helps in your case. If not, please reply to further investigate the issue.
Dustine Todd 1 Reputation point

2022-10-04T22:06:36.037+00:00

Hi @SSingh-MSFT the approach outlined in the post would work on my end because the firewall is set to allow from all networks. However, as we are concerned with the security, we should only be allowing access to the configured IP address and VNet. That is when we are encountering the issue, as discussed in the post, which is mentioned in points 2 and 3.

Is there some kind of limitation to what SAS can allow?
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-06T07:23:47.987+00:00

Hi @Dustine Todd ,

Sorry about the inconvenience you are facing.

Could you please check the Type of SAS and the permissions in it?

Reference link: storage-sas-overview
azure-sas-and-permission

Awaiting your reply.
Dustine Todd 1 Reputation point

2022-10-07T06:05:21.667+00:00

Hi @SSingh-MSFT My SAS has all the correct permissions, Read and List. I also tried adding others (Add, Updated, etc) and still it's not working IF the storage account was set to Enabled from selected virtual networks and IP addresses. If it's enabled for all net networks, it will work as expected but that's the part that I am unsure: isn't it more secured if it's not enabled for all networks on the storage account? The container being private is no problem, just having this restriction even while using SAS is just too confusing, since there are no mention of this limitation for SAS tokens.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-10T07:05:58.21+00:00

Hi @Dustine Todd ,

Sorry about the inconvenience you are facing.

Since you have already tried multiple workarounds, I request you to please check the source file and file format which might cause this issue. Refer to this link for more details.

Awaiting your response.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-13T08:35:55.873+00:00

Hi @Dustine Todd ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
Dustine Todd 1 Reputation point

2022-10-13T22:19:30.347+00:00

Hi @SSingh-MSFT no. As I mentioned in my previous comments, everything works except when we configured the storage account be enabled for selected networks. Currently we set it up to be enabled to all networks but with private blob containers. The process only fails for that scenario, which we think should be more secure.

I think the question right now is if this is an undocumented limitation with SAS? I saw no documentations regarding this.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-14T04:52:10.447+00:00

Hi @Dustine Todd ,

This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Irfan basha Shaik 0 Reputation points

2023-03-10T06:07:02.63+00:00

Hi @SSingh-MSFT

Do you have any further update on this thread. I too have similar problem

In my case.

I am trying to run a sql query calling a .json file from Azure blob storage from sql server where both has same private endpoint configured . i am seeing below error message.

Error:

Msg 4861, Level 16, State 1, Line 1

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

But when i change the networking setting of storage account to public it works without any error it runs the query.

Need help on this.

Thank you

2 answers

Your answer

Pratik Somaiya 4,211 Reputation points Volunteer Moderator

2022-10-04T06:29:21.333+00:00

is there a private endpoint configured for the Storage Account? if yes, is the traffic going via On-Premise DNS or Cloud DNS?
Dustine Todd 1 Reputation point

2022-10-04T06:31:12.917+00:00

No, there are no private endpoints configured in the storage account.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-04T08:17:12.43+00:00

Hi @Dustine Todd ,

Welcome to Microsoft Q&A platform and thanks for using Azure Services.

As I understand your ask, you are getting "Permission denied" error when reading a file from Azure Blob Storage container using SAS key.

Sorry for the inconvenience you are facing.

Since you have tried multiple workarounds already as mentioned in the question, I would request you to once check the CSV file as described in the link azure-blob-to-azure-sql-database-cannot-bulk-load-because-the-file-xxxx-csv-c

Let us know if this helps in your case. If not, please reply to further investigate the issue.
Dustine Todd 1 Reputation point

2022-10-04T22:06:36.037+00:00

Hi @SSingh-MSFT the approach outlined in the post would work on my end because the firewall is set to allow from all networks. However, as we are concerned with the security, we should only be allowing access to the configured IP address and VNet. That is when we are encountering the issue, as discussed in the post, which is mentioned in points 2 and 3.

Is there some kind of limitation to what SAS can allow?
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-06T07:23:47.987+00:00

Hi @Dustine Todd ,

Sorry about the inconvenience you are facing.

Could you please check the Type of SAS and the permissions in it?

Reference link: storage-sas-overview
azure-sas-and-permission

Awaiting your reply.
Dustine Todd 1 Reputation point

2022-10-07T06:05:21.667+00:00

Hi @SSingh-MSFT My SAS has all the correct permissions, Read and List. I also tried adding others (Add, Updated, etc) and still it's not working IF the storage account was set to Enabled from selected virtual networks and IP addresses. If it's enabled for all net networks, it will work as expected but that's the part that I am unsure: isn't it more secured if it's not enabled for all networks on the storage account? The container being private is no problem, just having this restriction even while using SAS is just too confusing, since there are no mention of this limitation for SAS tokens.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-10T07:05:58.21+00:00

Hi @Dustine Todd ,

Sorry about the inconvenience you are facing.

Since you have already tried multiple workarounds, I request you to please check the source file and file format which might cause this issue. Refer to this link for more details.

Awaiting your response.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-13T08:35:55.873+00:00

Hi @Dustine Todd ,

Just checking in to see if the above answer helped. If this answers your query, do click Accept Answer and Up-Vote for the same. And, if you have any further query do let us know.
Dustine Todd 1 Reputation point

2022-10-13T22:19:30.347+00:00

Hi @SSingh-MSFT no. As I mentioned in my previous comments, everything works except when we configured the storage account be enabled for selected networks. Currently we set it up to be enabled to all networks but with private blob containers. The process only fails for that scenario, which we think should be more secure.

I think the question right now is if this is an undocumented limitation with SAS? I saw no documentations regarding this.
SSingh-MSFT 16,296 Reputation points Moderator

2022-10-14T04:52:10.447+00:00

Hi @Dustine Todd ,

This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Irfan basha Shaik 0 Reputation points

2023-03-10T06:07:02.63+00:00

Hi @SSingh-MSFT

Do you have any further update on this thread. I too have similar problem

In my case.

I am trying to run a sql query calling a .json file from Azure blob storage from sql server where both has same private endpoint configured . i am seeing below error message.

Error:

Msg 4861, Level 16, State 1, Line 1

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

But when i change the networking setting of storage account to public it works without any error it runs the query.

Need help on this.

Thank you

Answer 1

SSingh-MSFT 16,296 Moderator

Hi @Irfan basha Shaik,

Welcome to Microsoft Q&A forum.

If you are facing similar issue, please follow the steps on SQL server below if helps:

open SQL Server
right click on database
select properties.
select permissions.
select your database role (local or cloud)
At bottom you will see explicit permissions table
find " backup database " permission and click Grant permission.

Other helpful links: https://sqlbak.com/blog/cannot-bulk-load-because-the-file-could-not-be-opened-operating-system-error-code-5-access-is-denied

https://techcommunity.microsoft.com/t5/sql-server-support-blog/sql-bulk-copy-error-8220-operating-system-error-code-5-access-is/ba-p/316231

Let us know if this helped or still have issue. Thanks

Irfan basha Shaik 0 Reputation points

2023-03-17T09:21:34.29+00:00

Hi @SSingh-MSFT

Thank you for your reply.

Followed and tried your suggestions by granting explicit permissions " backup database " on sql server but still same error.

========

Msg 4861, Level 16, State 1, Line 1

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

========

Just want to know, How it relates to permission issue, because the same query works perfectly when we set "networking setting of storage account to public" without granting "backup database" on sql server.

We explicitly seeing the issue when we DISABLED THE PUBLIC NETWORK in storage account to allow traffic only from Private Endpoint.

Thank you
SSingh-MSFT 16,296 Reputation points Moderator

2023-03-17T09:35:21.0366667+00:00

Hi @Irfan basha Shaik,

This is strange and not expected. I would recommend you to please file a support ticket for deeper investigation and in case if you don't have a support plan, do let us know here so that we can check on other options to unblock you. Thanks
Irfan basha Shaik 0 Reputation points

2023-03-17T16:46:56.92+00:00

Hi @SSingh-MSFT ,

I can open a support case with MS, in the meanwhile i thought to check here in this forum and also i am checking with internal team about this issue.

If you look into more options to unblock this issue i would be thankful.

I try my best to get this solved, if not i will finally open a case with MS.

Thank you for giving your time.
SSingh-MSFT 16,296 Reputation points Moderator

2023-03-20T05:17:52.5933333+00:00

Hi @Irfan basha Shaik,

Could you send an email to AzCommunity@Microsoft.com with your Subscription ID and thread link to this post, and I will enable a one-time free support request for your subscription.

Please reference this forum thread in the subject: ATTN: SHAKTI - SQL Server - Access denied when reading a file from Azure Blob Storage container using SAS key

Note: For such issues, please raise a separate Q&A question to get addressed rather than posting as comment on older threads.

Regards,

@SSingh-MSFT
Irfan basha Shaik 0 Reputation points

2023-03-20T08:01:44.05+00:00

Hi @SSingh-MSFT

sure

Thank you i will do the same.

Answer 2

Lara, Yasmani 0

Hi there, I'm also facing the same issue. Has someone found a solution?

SSingh-MSFT 16,296 Reputation points Moderator

2023-04-03T05:40:34.27+00:00

Hi Lara, Yasmani •,

Welcome to Microsoft Q&A forum.

For solution, could you please try the steps mentioned in the answers above and see if that helps in your case.

If not, we recommend you to raise support case if you have support plan. Thank you.

Share via

SQL Server - Access denied when reading a file from Azure Blob Storage container using SAS key

2 answers

Your answer