Snowflake Setup for Workbench

When the Snowflake system runs queries and receives data, it consumes CU (compute units). The system also consumes CU for periodic event polling.

CU have a monetary value assigned to them. The number of CU that are consumed depends on your organization's size and user activity. The cost can get high for a robust environment. To control the cost of consuming CU, you can set the warehouse to auto-suspend after 1 minute of inactivity. We can also reduce our polling frequency, which can reduce consumed CU.

Prerequisites

1. You must have an account with sufficient permissions to perform the tasks detailed in this article. This guide was tested using the default ACCOUNTADMIN role.

2. You must have the ability to run SQL queries.

3. You must have access to the openssl command line tool.

Step 1: Configure Access for Expel

1. Log in to the Snowflake console and navigate to Admin > Warehouses. Create a new warehouse and name it "Expel_warehouse". Select Create Warehouse.

2. Navigate to Admin > Users&Roles > Roles and create a new role named "EXPELROLE". Select Create Role.

3. In SnowSQL CLI, grant the SECURITY_VIEWER role the EXPELROLE with the following command:

   GRANT DATABASE ROLE SNOWFLAKE.SECURITY_VIEWER TO ROLE EXPELROLE

   This command allows Expel to retrieve events from the following views:

   • SNOWFLAKE.ACCOUNT_USAGE.LOGIN_HISTORY

   • SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_ROLES

   • SNOWFLAKE.ACCOUNT_USAGE.GRANTS_TO_USERS

4. Navigate to Admin > Users&Roles > Users and create a new user named "EXPEL_INTEGRATION".

   • Set the Default Role to EXPELROLE

   • Set the Default Warehouse to EXPEL_WAREHOUSE.

5. Select Save User.

6. In the openSSL command line, generate an encrypted RSA private key. You’re prompted to type a passphrase. Make note of the the passphrase, because you need it in a later step.

   $ openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

7. Generate the corresponding public key using the following command:

$ openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

1. In Snowflake, use the following SQL statement to attach the public key from the previous step to the Expel user. See the Snowflake documentation for more information.

ALTER USER EXPEL_INTEGRATION SET RSA_PUBLIC_KEY='MIIBI...';

1. Navigate to Admin > Warehouses and transfer the ownership of EXPEL_WAREHOUSE to the EXPELROLE.

2. Determine the account identifier. It usually has the following format: orgname-account_name. It's typically in the URL you use to log in. Learn more about Account Identifiers..

Step 2: Add Snowflake as a Security Device in Workbench

Now that you have the correct access configured and noted the credentials, you can integrate your tech with Workbench.

1. Log in to Workbench.

2. In the side menu, navigate to Organization Settings > Security Devices. If you have multiple organizations, you must select the appropriate organization name from the list.

3. Select Add Security Device.

4. Search for and select the Snowflake integration.

1. Complete the fields as follows:

   • Name - enter a name that might help you more easily identify this integration, such as “CompanyName Snowflake”; this name will display in Workbench under the Name column, and is a text string that you can filter on.

   • Location - enter the location of your integration, for example “cloud.” This is also a text string that you can filter on, so we recommend being consistent with location naming across your Expel integrations.

   • Username - enter "EXPEL_INTEGRATION".

   • Account - enter the org name from the account identifier (URL).

   • Private key - paste all content from the generated private key, making sure to include the -----BEGIN ENCRYPTED PRIVATE KEY----- and -----END ENCRYPTED PRIVATE KEY-----.

   • Passphrase - enter the passphrase you used while generating the key.

   • Warehouse name - enter "EXPEL_WAREHOUSE".

2. Select Save.

Your device should be created successfully within a few seconds. A few reminders:

• After your connection is healthy, it will take some time for your device to begin polling and receiving data.

• To check on the status, select the downward arrow for your device in the first column and choose View details.

• Polling will happen first; data will be received after that. You must refresh the page to see updates.

• If your device does not begin polling within 15 minutes, and does not begin receiving data within 30 minutes, contact our support team for help.

• To check if alerts are coming through, navigate to Dashboards > Alert Analysis. Scroll to the device you want to check, and select the Expel Alerts tab to reveal more alert information. It can take 36 to 72 hours for alerts to appear after setup, as we tune your device.