Snowflake

Integrate Teleskope with Snowflake

Requirements

In order to integrate Teleskope with Snowflake, you must make sure the following prerequisites are met:

• Within Snowflake: You must have a Snowflake user with the ACCOUNTADMIN role

• Within Teleskope: A Teleskope admin account to complete the setup process.

Snowflake enforces multi-factor authentication (MFA) on password-based sign-ins. The Teleskope service user must use key-pair authentication, which is exempt from interactive MFA. Password authentication works only on accounts that have not enabled the MFA policy; on MFA-enabled accounts a password sign-in fails with error 390197 (08004): Multi-factor authentication is required for this account.

Integration

1

Create a Teleskope Snowflake Role and User

-- Create role & user under ACCOUNTADMIN
USE ROLE ACCOUNTADMIN;

CREATE ROLE IF NOT EXISTS TELESKOPE;
CREATE USER IF NOT EXISTS TELESKOPE_USER
  DEFAULT_WAREHOUSE = '<WAREHOUSE>'      -- replace with your warehouse
  DEFAULT_ROLE = TELESKOPE
  COMMENT = 'Teleskope integration user';

GRANT ROLE TELESKOPE TO USER TELESKOPE_USER;

You assign the authentication credential to this user in the next step.

2

Configure Key Pair Authentication

Key-pair authentication is the required method for the Teleskope service user. Follow Key Pair Authentication to generate a key pair, assign the public key to TELESKOPE_USER, and verify the fingerprint. Snowflake's own reference is here.

You provide the private key (and passphrase, if the key is encrypted) to Teleskope during enrollment.

3

Run the Below Script to:

1. Grant Read Access to the Teleskope Role for each database

2. Grant Access History Access (optional)

-- 1. Grant SELECT on all current + future objects

DECLARE
  -- Cursor selecting all non-system databases
  c1 CURSOR FOR
    SELECT database_name
      FROM snowflake.information_schema.databases
     WHERE database_name NOT IN (
       'exampleDB1',
       'exampleDB2'
     )
     AND type != 'PERSONAL DATABASE';
  warehouse   STRING := 'WAREHOUSE';    -- same warehouse as above
  role_name   STRING := 'TELESKOPE';
  sql_cmd     STRING;
BEGIN
  -- Warehouse usage
  sql_cmd := 
    'GRANT USAGE ON WAREHOUSE "' || warehouse || '" TO ROLE "' || role_name || '"';
  EXECUTE IMMEDIATE sql_cmd;

  -- Loop through each database and grant privileges
  FOR rec IN c1 DO
    -- Database usage
    sql_cmd := 
      'GRANT USAGE ON DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Schemas
    sql_cmd := 
      'GRANT USAGE ON ALL SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT USAGE ON FUTURE SCHEMAS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Tables
    sql_cmd := 
      'GRANT SELECT ON ALL TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT SELECT ON FUTURE TABLES IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;

    -- Views
    sql_cmd := 
      'GRANT SELECT ON ALL VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
    sql_cmd := 
      'GRANT SELECT ON FUTURE VIEWS IN DATABASE "' || rec.database_name || '" TO ROLE "' || role_name || '"';
    EXECUTE IMMEDIATE sql_cmd;
  END FOR;

  -- 2. Optional: account-wide monitoring
  sql_cmd := 
    'GRANT MONITOR USAGE ON ACCOUNT TO ROLE "' || role_name || '"';
  EXECUTE IMMEDIATE sql_cmd;
  sql_cmd :=
    'GRANT IMPORTED PRIVILEGES ON DATABASE snowflake TO ROLE "' || role_name || '"';
  EXECUTE IMMEDIATE sql_cmd;
END;


SHOW GRANTS TO USER TELESKOPE_USER;
SHOW GRANTS TO ROLE TELESKOPE;

4

Grant write access to the Teleskope Role (optional)

grant delete on future tables in database identifier($database) to role identifier($role);
grant delete on all tables in database identifier($database) to role identifier($role);                           
grant delete on future views in database identifier($database) to role identifier($role);         
grant delete on all views in database identifier($database) to role identifier($role); 

5

Password Authentication (legacy)

Use password authentication only if the account has not enabled the Snowflake MFA policy. Set a password on the user:

ALTER USER TELESKOPE_USER SET PASSWORD = '<PASSWORD>';   -- replace with a secure password

On MFA-enabled accounts this path fails at sign-in with error 390197. Use key-pair authentication instead.

Enrollment

To enroll your Snowflake instance:

1. Navigate to Settings -> Connector Settings -> Snowflake -> + Enroll New Account

2. Enter your account information and click Next

3. Choose Key pair as the Authentication Type.

4. Enter your username, private key, and passphrase (if the key is encrypted), then click Enroll