Snowflake Configuration (Azure)

Snowflake is a compute-based warehouse software built on top of Amazon Web Services or Microsoft Azure Infrastructure. It’s similar to our Qrvey product but functions more like a SaaS (Software as a Service) platform, without offering the unique benefits of our product. This document specifies how to configure and use the Snowflake connector.

Requirements

• Snowflake Account: A Snowflake account is required to access the Snowflake Data Cloud.
• Snowflake User with Database Permissions: A dedicated Snowflake user with sufficient permissions to interact with the necessary databases.
• Snowflake Storage Integration: Allows the Snowflake account to access external cloud storage resources, such as Azure Blob Storage.
• Private Key: Used to securely authenticate the Snowflake user.
• Public Key: Paired with the private key to complete the key-pair authentication process in Snowflake.

Step 1: Snowflake Configuration

Follow the steps below to configure communication between Snowflake and Azure.

Create a Storage Integration

Create a storage integration using the CREATE STORAGE INTEGRATION command. A storage integration is a Snowflake object that stores a generated service principal for your Azure cloud storage, along with an optional set of allowed or blocked storage locations (e.g., containers). Cloud provider administrators in your organization grant permissions on the storage locations to the generated service principal. This option eliminates the need to supply credentials when creating stages or loading data.

CREATE STORAGE INTEGRATION <integration_name>
  TYPE = EXTERNAL_STAGE
  STORAGE_PROVIDER = 'AZURE'
  ENABLED = TRUE
  AZURE_TENANT_ID = '<tenant_id>'
  STORAGE_ALLOWED_LOCATIONS = ('azure://<StorageaccountName>.blob.core.windows.net/<container>/<path>/')

You can find the StorageaccountName in the resource group of your deployment.

• Integration Name: Name of the integration.
• Azure Tenant ID: To find your tenant ID, log into the Azure portal and search for Tenant Properties. The tenant ID is displayed in the Tenant ID field.
• Storage Allowed Locations: Specify allowed or blocked access to containers. Replace <container>/<path> with dataload-drdatacommons/sql_export.

Step 2: Grant Access to the Storage Account

In the Snowflake console, execute the following command:

DESC STORAGE INTEGRATION integration_name;

Where:

• integration_name is the name of the integration created in Step 1.

Note the values in the following columns:

• AZURE_CONSENT_URL: URL to the Microsoft permissions request page.
• AZURE_MULTI_TENANT_APP_NAME: Name of the Snowflake client application created for your account.

1. Navigate to the URL in the AZURE_CONSENT_URL column. The page displays a Microsoft permissions request.
2. Click Accept to grant the Azure service principal created for your Snowflake account access to specified resources.
   The page redirects to the Snowflake corporate site (snowflake.com).
3. Log into the Microsoft Azure portal.
4. Navigate to Azure Services » Storage Accounts. Select the storage account to grant access.
5. Click Access Control (IAM) » Add role assignment.
6. Select the desired role to grant to the Snowflake service principal:
   • Storage Blob Data Reader: Grants read-only access.
   • Storage Blob Data Contributor: Grants read and write access, including the ability to execute the REMOVE command.
7. Search for the Snowflake service principal. Use the string before the underscore in the AZURE_MULTI_TENANT_APP_NAME property.

Step 3: Grant Access to the Storage Location

GRANT USAGE ON INTEGRATION azure_int TO ROLE myrole;

• myrole: Name of the role used by the Snowflake user.
• azure_int: Name of the integration created in Step 1.

Step 4: Secrets Creation

Generate a private key and public key to store in the secrets. Use the openssl command or a similar tool.

Generate Private Key

openssl genpkey -algorithm RSA -out private-key.pem

Generate Public Key

openssl rsa -pubout -in private-key.pem -out public-key.pem

Assign Public Key

Assign the public key to the Snowflake user:

ALTER USER USER_NAME SET RSA_PUBLIC_KEY = public_key

Note: Remove all line breaks from the public key before assigning it in the Snowflake console.

Create Secrets on Kubernetes

Create Kubernetes secrets for Snowflake. Include the following properties:

• account: Snowflake account, formatted as accountid.region.
• username: Username for Snowflake operations.
• password: Password for the user.
• rsaPrivateKey: Private key for generating API tokens.
• storageIntegration: Name of the storage integration created earlier.

Use the following command to create the secret:

kubectl create secret generic <snowflakesecret> --from-literal=account=<SNOWFLAKEACCOUNT> --from-literal=username=<user> --from-literal=password=<password> --from-file=rsaPrivateKey=./private-key.pem --from-literal=storageIntegration=<integration> -n qrveyapps

Configure Connection in Qrvey

Open Qrvey Composer, configure the connection, and test whether it is successful.