The error is weird given what you are saying…
As a last resort, I would reset the source, just to be sure.
You can implement in the JDBC provisioning rule your own check if you want to be sure.
So I was finally able to figure it out. I was using execute immediate ‘’ previously, which contained 2 separate statements. It seemed to work fine in Snowflake but it would execute the multiple statements separately and I believe IDN was attempting to read the results from the first statement.
The working approach (to force Snowflake to run the statements in a single query) is to use Snowflake scripting blocks instead for this so the single account query reads as:
execute immediate $$$ begin show grants to role "$(identity)"; let res RESULTSET := ( select "grantee_name" as ACCOUNT, "name" as ENTITLEMENT from table(RESULT_SCAN(LAST_QUERY_ID())) WHERE "granted_on" = 'ROLE' ); return table(res); end; $$$$
The correct syntax in Snowflake is execute immediate $$<script block>$$ but since $ declares variables in IDN I had to add a few extra so it would end up in the right format in Snowflake. If you know how I can escape $ in the query fields - please let me know.