Skip to main content

Connect Snowflake with a key pair

Goal​

You'll create a Snowflake service user that signs in with an RSA key pair (Snowflake's JWT authentication), give it read-only access to one database, and connect it to Measured Cloud. Measured Cloud then tests the connection, lists tables, profiles and validates through that user, directly or through a Privacy Agent.

Use a key pair for every new Snowflake data source. Snowflake requires multi-factor authentication for password users on many accounts, and an unattended connection can't answer an MFA prompt. A password connection to such an account fails with:

390197 (08004): Multi-factor authentication is required for this account.

Measured Cloud shows that error as "This Snowflake account requires multi-factor sign-in, which Measured Cloud can't complete. Use key-pair authentication with a service user." The fix is this page.

Prereqs​

  • A Snowflake role that can create users and grant privileges: SECURITYADMIN (or ACCOUNTADMIN).
  • openssl on the machine where you generate the key.
  • The account identifier, in either form: <orgname>-<account_name> (for example myorg-analytics) or the account locator with its region (xy12345.us-east-1). Snowsight shows it under your account menu → Account details. You can also paste the account URL, https://myorg-analytics.snowflakecomputing.com: Measured Cloud keeps just the identifier.
  • In Measured Cloud, the editor role or higher on the workspace.

Steps​

1. Generate the key pair​

Generate a 2048-bit RSA private key in PKCS#8 form and its public key:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

To protect the private key with a passphrase, leave out -nocrypt; openssl asks for one. Measured Cloud takes the passphrase along with the key.

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 aes-256-cbc -inform PEM -out rsa_key.p8
openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Keep rsa_key.p8 secret: anyone with it can sign in as the service user. Don't commit it to a repository.

Print the public key's body on one line, for the next step:

grep -v "PUBLIC KEY" rsa_key.pub | tr -d '\n'

2. Create the service user and its role​

In a Snowsight worksheet, as SECURITYADMIN, replace the placeholders (ANALYTICS, COMPUTE_WH, the key) and run:

USE ROLE SECURITYADMIN;

CREATE ROLE IF NOT EXISTS MEASURED_RO;
GRANT ROLE MEASURED_RO TO ROLE SYSADMIN;

CREATE USER IF NOT EXISTS MEASURED_SVC
  TYPE = SERVICE
  DEFAULT_ROLE = MEASURED_RO
  DEFAULT_WAREHOUSE = COMPUTE_WH
  RSA_PUBLIC_KEY = 'MIIBIjANBgkqhkiG9w0BAQEFAAOCAQ8AMIIBCgKCAQEA…';

GRANT ROLE MEASURED_RO TO USER MEASURED_SVC;

TYPE = SERVICE makes it a service user: it has no password and no MFA, and signs in with the key only.

3. Grant read access​

Still as SECURITYADMIN:

GRANT USAGE ON WAREHOUSE COMPUTE_WH TO ROLE MEASURED_RO;
GRANT USAGE ON DATABASE ANALYTICS TO ROLE MEASURED_RO;
GRANT USAGE ON ALL SCHEMAS IN DATABASE ANALYTICS TO ROLE MEASURED_RO;
GRANT SELECT ON ALL TABLES IN DATABASE ANALYTICS TO ROLE MEASURED_RO;
GRANT SELECT ON ALL VIEWS IN DATABASE ANALYTICS TO ROLE MEASURED_RO;

-- Tables and views created later are readable too.
GRANT USAGE ON FUTURE SCHEMAS IN DATABASE ANALYTICS TO ROLE MEASURED_RO;
GRANT SELECT ON FUTURE TABLES IN DATABASE ANALYTICS TO ROLE MEASURED_RO;
GRANT SELECT ON FUTURE VIEWS IN DATABASE ANALYTICS TO ROLE MEASURED_RO;

Measured Cloud only reads: listing tables reads INFORMATION_SCHEMA, profiling and validation run SELECT queries. It needs no write privilege.

4. Check the key is registered​

DESC USER MEASURED_SVC;

The RSA_PUBLIC_KEY_FP row shows SHA256:…. Compare it with your key's fingerprint:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64

5. Connect it in Measured Cloud​

  1. Data Assets → Add source → pick Snowflake. (Through a Privacy Agent, pick Connect via → Privacy Agent first.)

  2. Sign in with: Key pair (the default).

  3. Fill in the fields:

    FieldValue
    AccountThe account identifier, for example myorg-analytics or xy12345.us-east-1.
    Service userMEASURED_SVC
    Private key (PEM)The whole content of rsa_key.p8, from -----BEGIN PRIVATE KEY----- (or -----BEGIN ENCRYPTED PRIVATE KEY-----) to the END line.
    Key passphraseOnly for an encrypted key.
    DatabaseANALYTICS
    SchemaThe schema to list and validate, for example PUBLIC.
    WarehouseCOMPUTE_WH
    RoleMEASURED_RO

    Set the schema, warehouse and role: validation runs need all three.

  4. Add data source. Measured Cloud checks the key opens (a wrong passphrase is an error here, not at the first run), encrypts the key and passphrase under your organization's key, and never shows them again.

  5. Add tables (step 2 of the same window) lists the tables and views in the database and schema. Tick them and click Add N tables.

Through a Privacy Agent, the browser encrypts the connection details for that agent, and only the agent can read them. The private key field can also hold a secret reference that the agent resolves when it connects, for example vault:secret/dq/snowflake#private_key. See Use a secret manager with the Privacy Agent.

Verify​

  • Test connection in the source's window shows a green check and the latency.
  • The source's tables list in step 2, and a profile run on one of them finishes.
  • In Snowflake, as ACCOUNTADMIN, SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY WHERE USER_NAME = 'MEASURED_SVC' ORDER BY EVENT_TIMESTAMP DESC LIMIT 5; shows the sign-ins with FIRST_AUTHENTICATION_FACTOR = RSA_KEYPAIR. The view can lag by up to two hours.

Switch an existing password source to a key pair​

Open the source → Edit connection → Sign in with: Key pair, set the service user and paste the private key, and save. The source keeps its data assets, rules and run history. The old password is cleared.

Rotate the key​

Snowflake holds two public keys per user, so you can rotate without downtime:

  1. Generate a new key pair (step 1).
  2. ALTER USER MEASURED_SVC SET RSA_PUBLIC_KEY_2 = '<new public key body>';
  3. In Measured Cloud, Edit connection and paste the new private key (and its passphrase, if any). Leaving the key field blank keeps the stored key.
  4. Once the source tests green, ALTER USER MEASURED_SVC UNSET RSA_PUBLIC_KEY;

Troubleshooting​

MessageCause
This Snowflake account requires multi-factor sign-in, which Measured Cloud can't complete. Use key-pair authentication with a service user.The source still uses a password. Switch it to a key pair (above).
The Snowflake private key is encrypted: enter its passphrase.The key starts -----BEGIN ENCRYPTED PRIVATE KEY----- and the passphrase is empty.
The Snowflake private key could not be read.The paste is incomplete, the passphrase is wrong, or it's not an RSA private key. Paste the whole rsa_key.p8.
390144 / JWT token is invalidThe public key registered on the user doesn't match the private key. Compare the fingerprints (step 4).
390100 / Incorrect username or passwordThe service user name or the account identifier is wrong.