Skip to content

Grokem · https://www.grokem.com/data/data-platforms/data-warehouses/how-to-connect-to-snowflake-with-a-key-pair-in-python-setup-and-checks

Data WarehousesIntegration guideIntermediate level

How to connect to Snowflake with a key pair in Python: setup and checks

This guide walks through the three stages of key pair authentication for Snowflake in Python: create the keys, enroll the public key, and use the private key in your code. It also covers access, secret handling and the checks that confirm the login works.

Pankaj Nigam

Technical Writer

Published
  • 30 min to complete
  • 0 sec

Tested with
  • Snowflake
  • Snowflake Connector for Python
  • Python
Your progress
  1. How to connect to Snowflake with a key pair in Python: setup and checks4 min(finished)
    0%

Before you start

6
  • A Snowflake account and a user you are allowed to log in as
  • Someone with the rights to change that user in Snowflake (you or an administrator)
  • Python installed on the machine that will run the code
  • The Snowflake Connector for Python installed in your environment (follow the installation page of the official connector documentation)
  • A local terminal where you can generate keys
  • A secure place outside your code repository to store the private key file

Key pair authentication lets a Python program log in to Snowflake with a private key instead of a password. Snowflake holds the matching public key on the user, and your code proves it owns the private key.

The flow has three stages:

  1. Create a public key and a private key in a local terminal.
  2. Enroll the public key in Snowflake with a query.
  3. Use the private key in your Python code.
Local terminalcreate public key andprivate key1Snowflake queryenroll public key2Python codeuse private key withSnowflake Connectorfor Python3private key stays local
The private key never leaves your side; only the public key goes to Snowflake.

Version note. No version numbers are pinned in this guide. Before you start, check the version of the Snowflake Connector for Python and of Python that your environment uses, and read the matching pages of the official Snowflake documentation.

What this guide does not give you. The exact key generation commands, the exact SQL statement for enrolling the key, and the exact argument names of the Python connection call are not reproduced here. Take them from the official Snowflake documentation page on key pair authentication and from the Snowflake Connector for Python documentation. This avoids copying a command that does not match your version.

A warning about parameter names. Some tools that connect to Snowflake through a driver ask you to set the AUTHENTICATOR and PRIV_KEY_FILE parameters in an additional options field. Do not assume those names apply to the Python connector. Confirm the argument names in the connector documentation.

Step 1 of 7: Confirm access and permissions

Ask who can change your Snowflake user, and confirm the user you will connect as. Enrolling a public key changes that user, so you need the right to do it or an administrator who will.

Also decide whether the connection is for a person or for a service. A service that runs unattended should use its own user, so you can revoke it without affecting anyone else.

Step 2 of 7: Create the public and private key in a local terminal

Generate the key pair in your local terminal, using the commands from the official Snowflake key pair authentication page. The private key stays with you. Only the public key goes to Snowflake.

Decide at this point whether the private key file is protected by a passphrase. If it is, your Python code needs that passphrase later, so plan where it will come from.

Step 3 of 7: Store the private key outside your repository

Move the private key file to a location with restricted file permissions, outside any folder tracked by version control. Anyone who can read this file can log in as the user.

Step 4 of 7: Enroll the public key in Snowflake

Run the query from the official documentation in Snowflake to attach the public key to the user. This is how Snowflake learns which private key to trust for that user.

Use the public key only. Never paste the private key into a query.

Step 5 of 7: Use the private key in your Python code

Pass the private key to the Snowflake Connector for Python when you open the connection. Look up the argument names in the connector documentation, since the names differ between tools.

Read the key location and any passphrase from environment variables or a secret store, with placeholders such as YOUR_PRIVATE_KEY_PATH and YOUR_PASSPHRASE in any shared example. This keeps secrets out of source files.

Step 6 of 7: Run a trivial query to check the connection

Open the connection and run the simplest query you can, then close the connection. A returned row proves that authentication, the user and the account details are all correct.

Do this before you add real queries, so a failure points at the login and not at your SQL.

Step 7 of 7: Check that no password is involved

Confirm that your code contains no password and that nothing prompts for one. This shows the login really used the key pair and not a leftover setting.


Expected result

The Python program opens a connection to Snowflake without asking for a password. A trivial query returns a result, and the connection closes without an authentication error. The exact result format depends on the query you run and the connector you use.

Troubleshooting

ProblemLikely causeFix
Authentication fails when the connection opensThe public key was not enrolled, was enrolled on a different user, or does not match the private key your code loads.Check which user the public key is attached to and that it is the user in your connection settings. If in doubt, generate a new pair and enroll the new public key again.
The private key file cannot be readThe path is wrong, the file permissions block your account, or the file is in a different format from the one the connector expects.Check the path and the permissions. Compare the key format with the requirements in the Snowflake Connector for Python documentation.
Loading the key fails with a passphrase or decryption errorThe key is protected by a passphrase that your code does not supply, or the passphrase is wrong.Supply the correct passphrase from a secret store or environment variable, or generate a key without a passphrase if your policy allows it.
A parameter such as AUTHENTICATOR or PRIV_KEY_FILE is rejected in PythonThose names appear in the additional options of some driver based tools. The Python connector can use different argument names.Look up the argument names in the Snowflake Connector for Python documentation and use those.
The login works on your machine but not on a serverThe key file or the secrets were not deployed to the server, or the server cannot read them.Deploy the private key through your secret management, set the path and passphrase there, and check file permissions on the server.

Frequently asked questions

Can I connect to Snowflake from Python without a password?

Yes. With key pair authentication you create a key pair, enroll the public key in Snowflake and use the private key in your Python code.

What are the stages of key pair setup?

Create the public and private key in a local terminal, enroll the public key in Snowflake with a query, then use the private key in Python.

Do AUTHENTICATOR and PRIV_KEY_FILE work in Python?

Those names are used in the additional options of some driver based tools. For the Python connector, check the argument names in its official documentation.

Which key goes into Snowflake?

Only the public key. Keep the private key on your side and out of version control.

Where do I find the exact commands?

In the official Snowflake documentation on key pair authentication and the Snowflake Connector for Python documentation.

From Grokem: https://www.grokem.com/data/data-platforms/data-warehouses/how-to-connect-to-snowflake-with-a-key-pair-in-python-setup-and-checks

Finished this article?

How did this strike you?

Be the first to react – it is anonymous.

Community

Questions about this?

Ask in Questions and help and get help from other readers.

Ask a questionBrowse Questions and help

Comments

No comments yet. Share what worked for you, or ask a question.

Keep learning

Picked for this article

Related reading, discussions and learning paths on the same subject.

All in Data Warehouses

Learning paths

Learning pathBeginner

Infrastructure basics for data work

The ground a data project stands on: the command line, how a name finds a server, packaging an app in a container, where files and databases live, keeping secrets out of code, backups you have tested and a cloud bill you control.

7 lessonsabout 15 minStart

Choose what we may use. You can change this at any time with “Privacy choices” at the foot of every page. Details are in the Cookie policy and the Privacy policy.

Strictly necessary

Remembers your theme, the lessons you marked as done and this choice. It stays on your device, tracks nothing and cannot be switched off.

Always on
What exactly is collected, and why

Google Analytics

Collects
The pages you view, your device and browser type, your language, how you reached the site and your approximate location (country and city, worked out from your IP address).
Why
To understand which pages and lessons are used, so we can improve them.
Stored
Two cookies, _ga and _ga_<id>, for up to 2 years. Google acts for us; the data is processed by Google, including outside your country.

Grokem’s own counts

Collects
The pages you open and how long you stay, how far you scroll, where you came from, your device type, browser, screen size, language and time zone. Your IP address is turned into a code that changes every day; it is never stored as it is.
Why
To see how long lessons are read and where readers leave. Not shared with anyone.
Stored
A random visitor ID, a visit ID and the time of your last page view in your browser’s storage (gr_vid, gr_sid, gr_last). No name or email is attached.