Unable to add the role assignment for the snowflake storage account in azure AD

Question

I have created a snowflake with azure cloud provider and trying to load data from the data lake to external stages in snowflake

For that i followed below steps:

1. Created storage account in azure and uploaded files in the container(data lake )

2. Created storage integration in the snowflake with azure_tenant_ID and storage_allowed_locations

3. From the integration description, authorized AZURE_CONSENT_URL to access the file from the azure storage account

4. I also noticed the AZURE_MULTI_TENANT_APP_NAME to be added in the role assignment in azure

But while searching for the members in the role assignment, i am not able to see the snowflake member(AZURE_MULTI_TENANT_APP_NAME) in the list?

Because of that i am not able to load/read data from the stage

Expected

If AZURE_MULTI_TENANT_APP_NAME has been added in the role assignment, then i believe below authorization issue will be resolved

Failure using stage area. Cause: [This request is not authorized to perform this operation using this permission. (Status Code: 403; Error Code: AuthorizationPermissionMismatch)]

Answer 1

It sounds like you haven't properly authorised Snowflake from within Azure. The doc page that you need to follow to set this up is here. It sounds like you are following the method described in 'Option 1'.

Make sure you carefully read the whole page. It is easy to miss a small item in these steps that will stop the whole thing from working. I think Option 1, Step 2 is the bit you will most likely have made an error on.

You mention that you cannot see 'SNOWFLAKE' in the AZURE_MULTI_TENANT_APP_NAME list. It seems that this is achieved through the following actions under 'Step 2':

1. Run desc storage integration <integration_name>;

2. In a web browser, navigate to the URL in the AZURE_CONSENT_URL column. The page displays a Microsoft permissions request page.

3. Click the Accept button. This action allows the Azure service principal created for your Snowflake account to obtain an access token on any resource inside your tenant. Obtaining an access token succeeds only if you grant the service principal the appropriate permissions on the container (see the next step).

   The Microsoft permissions request page redirects to the Snowflake corporate site (snowflake.com).