Connecting to Snowflake DB, warehouse not recognised error

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?

Database Connection

Accepted answers

davidskaife

Hi @GosiaK

I don't think so, beyond double checking everything has been entered correctly in the ODBC Settings, the connection itself etc

The ODBC connection tests that you can connect, which is working, but the process fails in Alteryx which uses credentials to which permissions would need to be granted would potentially suggest that is the cause. However someone else far more knowledgeable than me may chime in with help!

@apathetichell any thoughts?

All comments

davidskaife

Hi @GosiaK

Providing you've set the ODBC driver up correctly, I'd say this is more a Snowflake issue rather than an Alteryx one. A big one to check is does the User Credentials you're using to connect have permissions in Snowflake to use that Warehouse?

GosiaK

Thanks @davidskaife for quick answer. Is there anyway I could check this from Alteryx side, before I get on to client to check Snowflake from their side? The user credentials have been given to us by the client, so it was just copy and paste on our side.

davidskaife

Hi @GosiaK

I don't think so, beyond double checking everything has been entered correctly in the ODBC Settings, the connection itself etc

@apathetichell any thoughts?

ArunMukkaraLYB

Hello there, we are also facing the same issue. Did you manage to resolve the issue? Please let me know! Thanks in advance.

zcdavis

Have you attempted to perform the same query utilizing snowflake?

The potential issue I see is the path isn't available in the written warehouse. The format of the Pre Create SQL Statement looks correct as long as it's ending in ";". Ensure that you have the privileges to use that warehouse and perform that query prior to attempting the same pull in Alteryx.

apathetichell

Hey --- just saw this thread --- and belated thanks to @davidskaife for sharing... 1 ---> yes this reads like a Snowflake issue ---> not an Alteryx issue. I would double check this in a few ways:

1) Does the user you are authenticating as have access to the warehouse in Snowflake?

2) Do they have a default warehouse?

3) What do the Snowflake logs say?

4) If you are using ODBC 64 for a connection ---> throw in the warehouse and hit test.

5) remove the warehouse and hit test --> real talk --- you do not need to specify a warehouse if you have a default warehouse.

apathetichell

If use warehouse xxxx; in presql gives you an issue --- you can try -> SHOW WAREHOUSES; -> in your SQL and see what shoes up.

tom979

Im having the same error.

Configuration: Snowflake oAuth

Alteryx seems to have Warehouse set to NULL (run a select current_warehouse() as my query)

a command show warehouses doesn't show all the warehouses accessible by the user.

Are you guys all using Snowflake oAuth for authentication? I could never get the Entra ID(AD Private) integration to work properly through Azure.

apathetichell

I have never used Snowflake OAUTH with Alteryx. I prefer to never touch Snowflake Oauth. Can you use externalbrowser vs oauth? externalbrowser (SAML) is the standard for browser based auth with Snowflake and Alteryx --- not Oauth. Oauth is really webapp to webapp vs desktop system to Snowflake.

You'll also need to like provide the error messages.

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.

Quick Links

This months top contributors

AkimasaKajitani 388

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335