Unexpected database in connection details #966

sfc-gh-smason · 2024-03-21T23:32:19Z

sfc-gh-smason
Mar 21, 2024

SnowCLI version

2.1.1

Python version

3.12.2

Platform

No response

What happened

I want to test the SnowCLI connection without requiring DB/Schema being configured so that I can make sure my installation and authn is working.

I was expecting the following or some friendly message. It would also be helpful if you printed the version of Snowflake I was connecting to (e.g. select current_version())

[~]$ snow connection test --connection="testing"
+---------------------------------------------------+
| key             | value                           |
|-----------------+---------------------------------|
| Connection name | testing                         |
| Status          | OK                              |
| Host            | hbb49926.snowflakecomputing.com |
| Account         | hbb49926                        |
| User            | smason                          |
| Role            | not set                         |
| Database        | not set                         |
| Warehouse       | not set                         |
+---------------------------------------------------+

Instead I got the following:

╭─ Error ──────────────────────────────────────────────────────────────────────╮
│ 002003 (02000): 01b32558-0002-37a1-001f-9007027fe086: SQL compilation error: │
│ Database 'SERVICESNOW' does not exist or not authorized.                     │
╰──────────────────────────────────────────────────────────────────────────────╯

config.toml

default_connection_name = "testing"

[cli.logs]
save_logs = true
path = "/Users/smason/.snowflake/logs"
level = "info"

[connections.testing]
account = "hbb49926"
user = "smason"
password = "<redacted>"

Console output

No response

How to reproduce

No response

Answered by sfc-gh-pjob

Apr 3, 2024

Ok, I have an idea. I was able to reproduce the issue when I set wrong combination of "default namespace" and "default role" in settings of my Snowflake user. The connection test respects these default values fetched from Snowflake and it can be the cause of your issue. If you have access to check them, then please do it.

View full answer

sfc-gh-pjob · 2024-03-22T14:49:20Z

sfc-gh-pjob
Mar 22, 2024

Hello, I checked it on my installation and this works correctly. Please take a look at my configuration and the output:

[connections.bug1]
account = "<edited>"
user = "PJOB"
password = "<edited>"
host = "<edited>"

(snowflake-cli-labs) ➜  build git:(v2.1.1) snow connection test --connection bug1
+----------------------------------------------------------------------------+
| key             | value                                                    |
|-----------------+----------------------------------------------------------|
| Connection name | bug1                                                     |
| Status          | OK                                                       |
| Host            | <edited>                                                 |
| Account         | <edited>                                                 |
| User            | <edited>                                                 |
| Role            | PUBLIC                                                   |
| Database        | not set                                                  |
| Warehouse       | not set                                                  |
+----------------------------------------------------------------------------+

Are you sure you don't have defined one of the following env variables:

SNOWFLAKE_DATABASE
SNOWFLAKE_CONNECTIONS_TESTING_DATABASE

You can check it by trying to print these variables in your shell:

echo $SNOWFLAKE_DATABASE
echo $SNOWFLAKE_CONNECTIONS_TESTING_DATABASE

I'm asking about it because it looks like you have somehow defined SERVICESNOW database (we don't setup it). I was able to to receive the same error as you when I set up SNOWFLAKE_DATABASE env variable:

export SNOWFLAKE_DATABASE=SERVICESNOW

snow connection test --connection bug1
╭─ Error ──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ 002003 (02000): 01b328f5-0000-d2ca-0000-0f2d04bcd28e: SQL compilation error:                                                                                                                                                                                                                                                                                 │
│ Database 'SERVICESNOW' does not exist or not authorized.                                                                                                                                                                                                                                                                                                     │
╰──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯

0 replies

sfc-gh-smason · 2024-03-22T15:15:01Z

sfc-gh-smason
Mar 22, 2024
Author

I do not have any snowflake env variables defined:

[~]$ export |grep SNOW
[~]$

0 replies

sfc-gh-pjob · 2024-03-22T15:39:25Z

sfc-gh-pjob
Mar 22, 2024

Can you paste the output of snow connection list (please remove entries and parameters which you don't want to share)?

And also please execute snow connection test --connection="testing" --debug and share the output (please confirm that you don't share any sensitive information).

0 replies

sfc-gh-smason · 2024-03-22T16:38:16Z

sfc-gh-smason
Mar 22, 2024
Author

[~]$ snow connection list
+---------------------------------------------------------------------------------+
| connection_name | parameters                                                    |
|-----------------+---------------------------------------------------------------|
| testing         | {'account': 'hbb49926', 'user': 'smason', 'password': '****'} |
+---------------------------------------------------------------------------------+

[~]$ snow connection test --connection="testing"  --debug
2024-03-22 09:37:35 DEBUG [snowflake.cli.api.sql_execution] Executing describe database SERVICESNOW
2024-03-22 09:37:35 DEBUG [snowflake.cli.api.commands.snow_typer] Executing command exception callback
2024-03-22 09:37:35 DEBUG [snowflake.cli.api.commands.snow_typer] Executing command post execution callback
╭─ Error ───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ 002003 (02000): 01b32965-0002-3f9c-001f-90070281500e: SQL compilation error:                                                  │
│ Database 'SERVICESNOW' does not exist or not authorized.                                                                      │
╰───────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯
[~]$

0 replies

sfc-gh-turbaszek · 2024-03-27T20:40:11Z

sfc-gh-turbaszek
Mar 27, 2024
Maintainer

I do not have any snowflake env variables defined:
[~]$ export | grep SNOW
[~]$

I'm afraid this is not the right way. Use env | grep SNOW

0 replies

sfc-gh-pjob · 2024-03-28T10:55:43Z

sfc-gh-pjob
Mar 28, 2024

@sfc-gh-smason Could you try to:

check envs using the command presented by @sfc-gh-turbaszek
upgrade to 2.1.2 and run snow connection test --connection="testing" --debug again

Upgrade instructions:
pip:

pip install --upgrade snowflake-cli-labs

brew:

brew tap "Snowflake-Labs/snowflake-cli"
brew update
brew install snowflake-cli

0 replies

sfc-gh-smason · 2024-03-28T20:21:09Z

sfc-gh-smason
Mar 28, 2024
Author

[~/.snowflake]$ cat config.toml
default_connection_name = "testing"

[cli.logs]
save_logs = true
path = "/Users/smason/.snowflake/logs"
level = "info"

[connections.testing]
account = "hbb49926"
user = "smason"
password = "<redacted>"

[~/.snowflake]$ env |grep SNOW
[~/.snowflake]$

[~/.snowflake]$ snow --version
Snowflake CLI version: 2.1.2

[~/.snowflake]$ snow connection list
+---------------------------------------------------------------------------------+
| connection_name | parameters                                                    |
|-----------------+---------------------------------------------------------------|
| testing         | {'account': 'hbb49926', 'user': 'smason', 'password': '****'} |
+---------------------------------------------------------------------------------+

[~/.snowflake]$ snow connection test --connection="testing"  --debug
2024-03-28 13:19:46 DEBUG [snowflake.cli.api.sql_execution] Executing use role SETH_TEST_ROLE
2024-03-28 13:19:46 DEBUG [snowflake.cli.api.sql_execution] Executing use database SERVICESNOW
2024-03-28 13:19:46 DEBUG [snowflake.cli.api.commands.snow_typer] Executing command exception callback
2024-03-28 13:19:46 DEBUG [snowflake.cli.api.commands.snow_typer] Executing command post execution callback
╭─ Error ────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╮
│ 002043 (02000): 01b34c03-0002-44a9-001f-90070285000a: SQL compilation error:                                               │
│ Object does not exist, or operation cannot be performed.                                                                   │
╰────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────╯
[~/.snowflake]$

Where is it picking up a ROLE and DB?

0 replies

sfc-gh-turbaszek · 2024-04-02T12:35:45Z

sfc-gh-turbaszek
Apr 2, 2024
Maintainer

Have you ever used connections.toml?

0 replies

sfc-gh-smason · 2024-04-02T16:05:08Z

sfc-gh-smason
Apr 2, 2024
Author

I have not.

0 replies

sfc-gh-pjob · 2024-04-03T15:56:54Z

sfc-gh-pjob
Apr 3, 2024

Ok, I have an idea. I was able to reproduce the issue when I set wrong combination of "default namespace" and "default role" in settings of my Snowflake user. The connection test respects these default values fetched from Snowflake and it can be the cause of your issue. If you have access to check them, then please do it.

0 replies

sfc-gh-pjob · 2024-04-03T15:58:38Z

sfc-gh-pjob
Apr 3, 2024

To clarify: I'm talking about settings of Snowflake user in account admin view, not configuration in CLI.

0 replies

sfc-gh-smason · 2024-04-04T21:06:09Z

sfc-gh-smason
Apr 4, 2024
Author

@sfc-gh-pjob, I checked the account settings/profile for my account in Snowsight, and the default role is SETH_TEST_ROLE and there's no default warehouse selected. This looks like where it's picking it up from. I'm going to close this out.

0 replies

Uh oh!

Unexpected database in connection details #966

Uh oh!

sfc-gh-smason Mar 21, 2024

SnowCLI version

Python version

Platform

What happened

Console output

How to reproduce

Replies: 12 comments

Uh oh!

sfc-gh-pjob Mar 22, 2024

Uh oh!

Uh oh!

sfc-gh-smason Mar 22, 2024 Author

Uh oh!

sfc-gh-pjob Mar 22, 2024

Uh oh!

sfc-gh-smason Mar 22, 2024 Author

Uh oh!

sfc-gh-turbaszek Mar 27, 2024 Maintainer

Uh oh!

sfc-gh-pjob Mar 28, 2024

Uh oh!

sfc-gh-smason Mar 28, 2024 Author

Uh oh!

sfc-gh-turbaszek Apr 2, 2024 Maintainer

Uh oh!

sfc-gh-smason Apr 2, 2024 Author

Uh oh!

sfc-gh-pjob Apr 3, 2024

Uh oh!

sfc-gh-pjob Apr 3, 2024

Uh oh!

sfc-gh-smason Apr 4, 2024 Author

sfc-gh-smason
Mar 21, 2024

sfc-gh-pjob
Mar 22, 2024

sfc-gh-smason
Mar 22, 2024
Author

sfc-gh-pjob
Mar 22, 2024

sfc-gh-smason
Mar 22, 2024
Author

sfc-gh-turbaszek
Mar 27, 2024
Maintainer

sfc-gh-pjob
Mar 28, 2024

sfc-gh-smason
Mar 28, 2024
Author

sfc-gh-turbaszek
Apr 2, 2024
Maintainer

sfc-gh-smason
Apr 2, 2024
Author

sfc-gh-pjob
Apr 3, 2024

sfc-gh-pjob
Apr 3, 2024

sfc-gh-smason
Apr 4, 2024
Author