SQL execution error: Error assuming AWS_ROLE. Please verify the role and externalId are configured correctly in your AWS policy.

 Q.  SQL execution error: Error assuming AWS_ROLE. Please verify the role and externalId are configured correctly in your AWS policy.

Answers:  Once you created the storage integration, you would use the below commands to get the USER ARN and External ID and both must exact match in AWS Role Trust Policies: 



Now go to AWS Console --> Users --> Open the Trust Relationship and Edit, If your snowflake user is different from who created the storage integration (Accountadmin mostly) then to allow to another user use StringLike instead of StringEquals and *_* symbols to allow them. This is silly mistake we mostly forget to correct 😊




Once you saved the User, It will look like the below screen:
 





I hope this will resolve your issue 😊
If you still facing the issue, please write to me in comment box, i will try to help you :)







Comments

Post a Comment

Popular posts from this blog

error_on_column_count_mismatch=false : Number of columns in file does not match that of the corresponding table use file format option error_on_column_count_mismatch=false to ignore this error File

Image
Error: Number of columns in file does not match that of the corresponding table  use file format option error_on_column_count_mismatch=false to ignore this error File  this error normally occurs when number of columns in staged file and table are not match, I would give you an example to check this and a solution, that can work for you  😊 if you check the file format you choose and your staged file, you format and data is looking like the below images, as I do have the first column with blank value and somewhere I do have data there if you uncheck the error_on_column_count_mismatch to false, you can better about this now try to execute the select query on stage file. I am using a Table stage and you can see on above screen, columns data are not coming as per table column so you do need to create one more column in table to insert it correctly. It is very necessary to design the model properly, otherwise a mistake can cost you, however it is good that get and put do...
Read more

Snowflake Automation using SnowSql CLI || Create Azure Devops CI CD using Snowsql CLI

Image
 Hello Guys, I am writing this blog to understand how we can automate snowflake databases using CI CD pipelines in Devops, I am using Azure Devops tool to automate snowflake pipeline.  To start it lets have a repository and create a folder inside like called Migrations and inside it create a .sql file or you can have multiple .sql files if you want to execute the multiple files. file1.sql -- create a table using a Sequence  use   role  sysadmin; use   database  snowstages_db; use   schema   public ; create   or   replace   table  test ( Id  integer   default  SEQ_1.nextval, name   varchar ( 50 ), Dept_number  int ); file2.sql -- create a clone table using first one use   role  sysadmin; use   schema   public ; Create  transient  table   if   not   exists  Test_Clone1  clone Test; Go inside the Library in Devops Portal and add Config variab...
Read more

Practice Set 1: Snowpro Certification Practice Set with more than 250 Unique Questions

Image
Snowflake SNOWPRO Core Certification Practice Set Questions: Note : Please verify correct/incorrect answers using green / red highlighter. Top of Form Question 1:  Correct Can you share data with multiple consumers with row based access control? ·          NO ·          YES (Correct) Explanation Secure View with Account mapping using CURRENT_ACCOUNT(); Bottom of Form Top of Form Question 2:  Correct A user can connect Snowflake in multiple ways. Please select ·          Command line clients (e.g. SnowSQL) (Correct) ·          A web-based user based (Correct) ·          Native connector (e.g. Python) that can be used to develop applications (Correct) ·          ODBC and JDBC drivers ...
Read more