search

MicrosoftDocs / azure-docs

Open source documentation of Microsoft Azure
https://docs.microsoft.com/azure
Creative Commons Attribution 4.0 International
10.29k stars 21.47k forks source link

SQL server unable to access firewall protected Storage account in a different tenant #98120

Closed Subhashini2610 closed 2 years ago

Subhashini2610 commented 2 years ago

SQL server unable to access firewall protected Storage account in a different tenant

I have a SQL server in tenant A and a firewall protected Storage Account in tenant B. I am creating external table T-SQL query to access a blob in the storage account. Unfortunately, I cannot add a role assignment of Storage Blob Data Reader to the managed identity of the SQL server as it is present in another tenant. I have a multi-tenant service principal that can be used for this purpose. But I get the following error when I authenticate using the service principal (note that the service principal has Storage Blob Data Reader access to the Storage Account):

Msg 105019, Level 16, State 1, Line 1
External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message:
HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: AbfsRestOperationException: Operation failed: "This request is not authorized to perform this operation.", 403, HEAD, https://someadlsl001.dfs.core.windows.net/somecontainer/?upn=false&action=getAccessControl&timeout=90'

This is the SQL query I am executing to access the blob data:

CREATE DATABASE SCOPED CREDENTIAL cred WITH IDENTITY = '{clientID of service principal}@https://login.microsoftonline.com/{tenantID}/oauth2/token', SECRET = 'xxxxxxxxxxxxxxxx'

CREATE EXTERNAL DATA SOURCE AzureDataLakeStore
WITH ( LOCATION = 'abfss://somecontainer@someadlsl001.dfs.core.windows.net/weather.csv' , CREDENTIAL = cred, TYPE = HADOOP ) ;

CREATE EXTERNAL TABLE [dbo].[WeatherData2] (
    [usaf] [nvarchar](100) NULL

)
WITH
(
    LOCATION='/',
    DATA_SOURCE = AzureDataLakeStore,
    FILE_FORMAT = csvFile,
    REJECT_TYPE = VALUE,
    REJECT_VALUE = 0
);

select * from [dbo].[WeatherData2]

The above works seamlessly when I remove the firewall on the storage account. However, I do not want to do this for security reasons.

PS: I have also tried using Az Synapse (dedicated SQL pool) for the same. I am facing the same issue there as well.

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

YashikaTyagii commented 2 years ago

@Subhashini2610 It would be great if you could add a link to the documentation you are following for these steps? This would help us redirect the issue to the appropriate team. Thanks!!

Subhashini2610 commented 2 years ago

@YashikaTyagi-MSFT I am referring to https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/develop-storage-files-storage-access-control?tabs=public-access#firewall-protected-storage document. In the firewall protected storage part, I see that only way of authentication is the MSI one. However, this would not work for cross tenant scenario (as is in my case).

RamanathanChinnappan-MSFT commented 2 years ago

@Subhashini2610

Thanks for your feedback! We have assigned the issue to author and will provide further updates.

SnehaGunda commented 2 years ago

@RamanathanChinnappan-MSFT this issue needs further debugging, can you redirect it to the Synapse support team? If it results in a doc update, we can make the changes.

KranthiPakala-MSFT commented 2 years ago

@Subhashini2610 Thanks for leveraging Azure docs feedback channel to raise this question. This channel is reserved for ‘Azure docs’ feedback- for sharing doc feedback and suggesting content improvements. We didn't determine any changes for this documentation upon reviewing this feedback.

As the issue is specific to your use case, we request you to please reach out in forums like Microsoft Q&A or Stackoverflow. We have our engineers and other community members who will be happy to assist. Forums have a better suited audience for such queries. Once your issue is posted in forum, please do share the forum thread link here for tracking.

If you have a document which you are following and if you think it needs an update, please let us know about that here.

And if this is a blocker and need immediate assistance, please file a support ticket for the same.

Thank you

RamanathanChinnappan-MSFT commented 2 years ago

@Subhashini2610

We are going to close this thread as resolved but if there are any further questions regarding the documentation, please tag me in your reply and we will be happy to continue the conversation.