Skip to main content

​
Requirements

To use the Formal Connector with Snowflake, you must have access to your Snowflake account and the necessary permissions to manage resources within it.

​
Configuration

  • Snowflake account URL: Your Snowflake account URL, typically in the format X.Y.snowflakecomputing.com. Refer to the displayed image for clarity.
  • Snowflake username and password: These credentials will be used by Formal users to connect to your Snowflake account.

​
Native Users

Native users define the Snowflake credentials the connector uses to authenticate with the upstream Snowflake account on behalf of Formal users. Snowflake supports two authentication methods, and both are configured through the Password native user type in Formal. Formal automatically detects which method to use based on the format of the secret you provide.

​
Password Authentication

To use password-based authentication, create a native user with the Password type and set the secret to the Snowflake user’s password. 
resource "formal_native_user" "snowflake_password" {
  resource_id        = formal_resource.snowflake.id
  native_user_id     = "SNOWFLAKE_USERNAME"
  native_user_secret = var.snowflake_password
  use_as_default     = true
}

​
Key-Pair Authentication

To use key-pair authentication (SNOWFLAKE_JWT), create a native user with the Password type and set the secret to the PEM-encoded private key. Formal detects the PEM format and automatically uses JWT authentication when connecting to Snowflake. 
resource "formal_native_user" "snowflake_keypair" {
  resource_id        = formal_resource.snowflake.id
  native_user_id     = "SNOWFLAKE_USERNAME"
  native_user_secret = var.snowflake_private_key_pem
  use_as_default     = true
}
The client authenticator method must match the native user method. If the native user is configured with a PEM key (key-pair auth), Formal clients must also use SNOWFLAKE_JWT. If the native user uses a password, clients should use password authentication.

​
TLS Configuration

TLS must be enabled for secure communication. You have two options:
  1. Formal-Managed TLS: Formal wil manage the TLS Certificate for you, providing you with a domain of the format .YOUR_ORG_NAME.connectors.joinformal.com.
  2. Self-Managed TLS: If you prefer to manage the TLS certificate yourself, follow these steps:
    • Obtain a TLS wildcard certificate.
    • Add CNAME records for account.hostname, s3.hostname, backend.hostname, and app.hostname to your DNS.
    • Provide the TLS certificate through environment variables:
    CUSTOMER_TLS_CERT_PRIVATE_KEY: ""
CUSTOMER_TLS_CERT_FULLCHAIN: ""
When Formal manages the TLS certificate, the Network Load Balancer (NLB) must be internet-facing.

​
Connecting to Snowflake

With the above configuration complete, you can now connect to Snowflake using the specified hostname and Formal credentials.

​
Connection Parameters

When configuring a Snowflake client to connect through the Formal Connector, note the following:
  • Host: Must use the account. subdomain prefix on your Formal connector hostname. For example, if your connector hostname is my-org.connectors.joinformal.com, the host should be account.my-org.connectors.joinformal.com. Use the literal value “account” instead of passing the account identifier”.
  • Account: Set this to be your Snowflake account identifier. This is the first segment of your Snowflake account URL (<account id>.snowflakecomputing.com).
  • Port: The port your connector listener is configured on (e.g., 443 or 1443).
  • User: Your Formal identity (e.g., idp:formal:human:you@yourcompany.com or idp:formal:machine:my-service).
  • Authenticator: Must match the authenticator method configured on the native user in Formal. If the native user uses key-pair authentication (SNOWFLAKE_JWT), your Formal client must also use SNOWFLAKE_JWT. If the native user uses password authentication, your client should use password authentication.

​
Connecting via the Snowflake CLI (snow)

Add a connection to ~/.snowflake/config.toml:

​
Password Authentication

[connections.my-connection]
account = "<snowflake-account-id>"
host = "account.<connector-hostname>"
port = <connector-port>
user = "idp:formal:human:you@yourcompany.com"
password = "<your-formal-token>"

​
Key-Pair Authentication (SNOWFLAKE_JWT)

[connections.my-connection]
account = "<snowflake-account-id>"
host = "account.<connector-hostname>"
port = <connector-port>
user = "idp:formal:machine:my-service"
authenticator = "SNOWFLAKE_JWT"
private_key_file = "/path/to/private_key.pem"
When using SNOWFLAKE_JWT, the private key file should be the PEM file available via the Download Private Key button on the machine user details page in the Formal console. Test the connection: 
snow connection test -c my-connection
Use --debug for verbose logging if troubleshooting: 
snow connection test -c my-connection --debug

​
Connecting to Snowflake via Retool

  1. Configure the Connector for Snowflake: Ensure your Formal Connector is correctly set up to proxy Snowflake connections.
  2. Update Retool Resource: Retool requires an account identifier to connect to Snowflake. However, to use Formal with Snowflake, you need to bypass this limitation by ensuring Retool supports custom connection options similar to PostgreSQL resources. This allows for more flexible connections, including the ability to specify the full HTTP URL instead of just the account identifier.
  3. Custom Snowflake Hostname: For a successful connection, it’s crucial to set a custom Snowflake hostname that points to the Formal Connector. This is necessary because Retool’s default configuration might not support the direct use of custom domains for Snowflake connections.
  4. Connection String: Use the following JDBC connection string format for connecting to Snowflake through Formal:
jdbc:snowflake://account.test-snowflake-proxy.prod.com:443/?user=idp:formal:machine:prd-retool&password=[pwd]&db=analytics&options=formal_enduser={{ current_user.email }}
Replace [pwd] with your actual password. This format ensures that the connection is routed through the Formal Connector, leveraging Formal’s security and policy enforcement features.

​
End-User Identity Propagation

When a machine user (e.g., a BI tool) connects to Snowflake through Formal, you can propagate the actual end-user’s identity so that policies evaluate against the human user, not the shared machine credential. Formal supports two mechanisms for this, depending on the BI tool.

​
Omni

Omni propagates end-user identity at session login time via the QUERY_TAG session parameter. When an Omni user runs a query, Omni includes a JSON-encoded QUERY_TAG in the Snowflake login request containing the omniOrganizationUserId field. The Formal Connector extracts this ID and resolves it to a Formal user via external ID mappings. To set this up:
1

Create a BI Integration

In the Formal console, go to BI Integrations and create a new Omni integration.
2

Map External IDs

For each Omni user, add an external ID to their corresponding Formal user. The external ID value should match the user’s omniOrganizationUserId in Omni, and the app type should be set to the Omni integration you created.
3

Verify

Run a query through Omni and check Logs to confirm the end-user is correctly attributed.
Omni resolves end-user identity once per session at login time. All queries within that session are attributed to the same end-user.

​
Other BI Tools (Metabase, Sigma, Hex, Custom)

Other BI tools propagate end-user identity per query via SQL comments. These tools append a comment to each SQL statement containing the end-user’s external ID, which the Connector parses on every request. For example, a query from Metabase might look like: 
SELECT * FROM orders /* {"formal_external_user_id": "12345"} */
The Connector extracts the external ID from the comment and resolves it to a Formal user. This means different queries within the same session can be attributed to different end-users. For setup instructions, see the BI Integrations guide.

​
Looker (FORMAL_ENDUSER)

Looker uses a different mechanism: the FORMAL_ENDUSER session parameter passed via JDBC connection options. This resolves the end-user by email address rather than external ID. See the Looker setup instructions for details.

​
Policy Evaluation

Formal supports the following policy evaluation stages for Snowflake:
  • Session: Evaluate and enforce policies at connection time
  • Pre-Request: Evaluate and enforce policies before query execution
  • Post-Request: Evaluate and enforce policies after data retrieval

​
Troubleshooting

​
Common Issues and Solutions

  • “403: Host forbidden”: The connector uses subdomain-based routing. Ensure the host in your connection config uses the account. prefix (e.g., account.my-org.connectors.joinformal.com), not the bare connector hostname.
  • Error code 390102 (Incorrect username or password): The connector successfully proxied to Snowflake, but the native user credentials were rejected. Verify that the native user exists in Snowflake and that the password or private key is correct. If using ENV: for the native user secret, check the connector logs for ENV variable ... not found errors.
  • Authenticator mismatch: If the native user in Formal is configured with key-pair auth, your client must also use SNOWFLAKE_JWT as the authenticator. A mismatch will cause authentication failures.
  • Ensure TLS certificates are correctly set up, and DNS records are pointing to the NLB.
  • Verify JDBC driver compatibility, especially if using self-signed certificates with Formal.