SNOW-1454377: Snowflake Native Apps Stored Procedure fails to execute or return table when called from Task

1. What version of Python are you using?

   Python 3.11

2. What operating system and processor architecture are you using?

   The environment of the Stored Procedure implemented within the Snowflake Native Apps

3. What are the component versions in the environment (pip freeze)?

   The Stored Procedure is executed within a Snowflake Native App environment. The specific versions of the installed packages are not directly accessible within the Native App context.

4. What did you do?

   I am attempting to execute a Stored Procedure implemented within a Snowflake Native App via a Snowpark Python Task. The Stored Procedure is designed to return a table, but it fails to execute when called from the Task.

   Steps to Reproduce:

   1. Create a Snowflake Native App containing a Stored Procedure that returns a table.
   2. Implement Task that calls the Stored Procedure.
   3. Execute the Task.

5. What did you expect to see?

   I expected the Task to successfully call the Stored Procedure and retrieve the resulting table.

   What happened instead?

   The Task fails with an error indicating that the Stored Procedure could not be executed. Additionally, attempting to retrieve the results using result_scan fails, suggesting that the SYSTEM user (used by Tasks) does not have permission to access the results within the Native App context.

It seems that the execution of the procedure is failing when trying to run the following code:

https://github.com/snowflakedb/snowpark-python/blob/d640a36e781295188a543b2f9fdbf991cb0dc362/src/snowflake/snowpark/_internal/server_connection.py#L452

6. Can you set logging to DEBUG and collect the logs?

Additional Information:

• This issue appears to be a bug specific to the interaction between Tasks and Native Apps when Stored Procedures return tables.
• The Stored Procedure functions correctly and returns the expected table when called directly (outside of a Task).

Request:

Please investigate and resolve this issue to enable the successful execution of Stored Procedures within Snowflake Native Apps when called from Snowpark Tasks, especially when those procedures return tables. Additionally, please address the permissions issue related to result retrieval to ensure seamless integration between these components.

Start with src/snowflake/snowpark/_internal/server_connection.py around line 452, then reproduce the reported flow: a Snowflake Native App stored procedure returning a table called from a Snowpark Python Task. Compare direct procedure execution with Task execution and the result_scan attempt. Done means the Task can execute the procedure, retrieve its table result, and avoid the reported SYSTEM-user permission failure.