Skip to main content

Good afternoon

We are working on the migration to fully deploy to Snowflake. We created a sas token as described in the instruction (https://support.timextender.com/configure-resources-126/use-snowflake-data-warehouse-storage-727) and added the token in the ODX settings in the portal.  However when we deploy and execute to Snowflake the deployment goes fine but when executing we get the following error:
Failure using stage area. Cause: :This request is not authorized to perform this operation. (Status Code: 403; Error Code: AuthorizationFailure)]

In Snowflake the user has enough priviliges but it is when running the stage that want to read from the data lake storage that this error occures.
The storage account has enabled that you can read from it with keys so that should not be the issue.
Any ideas on why Snowflake can not access the data lake?

Hi @Roy V 

Can you please ensure that:

  • the “allow access from” setting in your data lake is set to All Networks 
  • The SAS token you have generated is still valid
  • The app registration has been added to the data lake with the Storage Blob Data Contributor role

Thanks for your reaction.
For security reasons we can and do not want to set the data lake open to all networks.

What should we add specific for Snowflake to be able to address the datalake?


for a test I set the storage account open to all networks

this results in the following error (where the snowflake user has accountadmin access so to my opion it can not be related to access on snowflake side):
 

-------------------------

    -Execute DSA_History HIST.TOBIASAX_HCMTITLE ODX Transfer 'Failed'
        System.AggregateException: One or more errors occurred. ---> Snowflake.Data.Client.SnowflakeDbException: Error: SQL access control error:
Insufficient privileges to operate on stage 'TimeXtender_ExternalStage_4f07df3c5b524bb6a421fe32b78128dd_39717e1130d846c0bb2090652a1f956f' SqlState: 42501, VendorCode: 3001, QueryId: 01b51c00-0203-f860-0001-61aa00f5ba12
           at Snowflake.Data.Core.SFStatement.ExecuteHelper.T,U](Int32 timeout, String sql, Dictionary`2 bindings, Boolean describeOnly)
           at Snowflake.Data.Core.SFStatement.Execute(Int32 timeout, String sql, Dictionary`2 bindings, Boolean describeOnly)
           at Snowflake.Data.Client.SnowflakeDbCommand.ExecuteInternal(Boolean describeOnly)


Hi @Roy V I have created a support ticket for this


As snowflake is on a different subscription you can not give access to the ADLS in the Azure Portal directly..
In snowflake you can retrieve the subnets that need to be given access to and with an Azure Power shell script this access can then be given to the ADLS. 

When that has been done the ODX and Snowflake can communicate with each other.


Reply