Connecting to Snowflake DB, warehouse not recognised error

[GosiaK]

Hi All,

I'm connecting through Alteryx Designer to external (client's) Snowflake DB for the first time.

I've installed Simba driver and configured ODBC DataSource Administrator with info provided by client, including warehouse id, test was Successful, it said connected to data source.

But when I try to set up the input tool, using this connection, I got the following error: 

Error SQLExecute: No active warehouse selected in the current session.  Select an active warehouse with the 'use warehouse' command.¶

I found on Alteryx community that warehouse can be specified in Pre SQL Stetement, so I did that, but getting following error:

Error running PreSQL: "use warehouse XXX;": SQL compilation error:¶Object does not exist, or operation cannot be performed.

Below is the configuration.

Could someone smart please advise where I'm going wrong?

 

[dataguyW]

You should be able to pick a warehouse and set it within your ODBC(64) setup configuration for that connection.   Try to set it there and take it out of your Pre-SQL statement.  That should also test that it works.   I would do as other stated and check the console that it is there and is on and then use that in your config setup.

[tom979]

So I solved this problem of no warehouse with Snowflake yesterday afternoon.

For my config I was using Snowflake oAuth as my authenticator. 

But really I think it may apply to any external oAuth setup with Alteryx.

Snowflake has a concept of secondary roles.  via an oAuth connection these are disabled.

What this means is that you only have the explicit access directly granted to the role that you are using.(it has limited scope).

So you need to confirm with your snowflake admin(i was so this was easy for me) but confirm that the role as been granted access to a warehouse that your supplying directly. NOT inherited in any manner(ie the role has been granted to another role which has the access or your user has access to a different role than the one you are using for snowflake).

There are ways to enable secondary roles in an oAuth connection through to Snowflake, but you might not like to do this for security reasons.

You can test this in Alteryx Designer by simply executing a SHOW WAREHOUSES as mentioned above.  You need to supply one of the warehouse this returns in your connection string.(if all you can see is your streamlit warehouse then you havent done it correctly.  And dont use the streamlit warehouse)

[apathetichell]

@tom979 --- going back to my earlier comment. I would avoid using Oauth here and use external browser.. If you're on Cloud Designer --- by all means use Snowflake Oauth --- but if your redirect is local (ie you are using a desktop app) ---> use externalbrowser not Oauth...

[tom979]

I had some issues with the Entra ID instructions.  After following the setup instructions in the community documentation Snowflake didnt like the token.

Whereas from the instructions followed for Snowflake oAuth I could get authentication from Alteryx Designer to Snowflake.

Noting the snowflake oAuth has a lot of constraints by default on the session in terms of access.(such as the warehouse)

How did you configure the client application in Azure for Alteryx when using MFA with Entra?  exactly as per the instructions or did you have to change it slightly?

[tom979]

(i mean alteryx designer desktop app)

[tom979]

Sorry for the repeat messages, i cant seem to delete/edit the post.  but with snowflake oAuth I can authenticate from Alteryx Desktop.  But to the original posters message.  When I first configured the connection from Desktop, the warehouse was set to NULL.  

This is an issue with snowflake oAuth being very restrictive. With the role being used being very limited in Scope.   So as a suggestion to the OP is to confirm the authenticator being used - if snowflake then I would then check with my snowflake admin around the specific roles access to warehouses(not the user).   to use Webbrowser, the alteryx client does need to be setup in Entra. 

I tried to go down this route and hit a brick wall.  Around the token being okay.  Different topic but be keen to understand

[GosiaK]

Apologies for delay in coming back, in my case, the problem was on the Snowflake side, the client haven't set the access rights correctly, so there was actually nothing we could do from the Alteryx side to resolve it.