Snowflake Db Store

Description	Connection to a Snowflake database
Type	snowflake
Category	Db

Configuration in States

The Snowflake Db Store can be configured directly in the States interface via the Data Stores module. The following configuration modes are available:

Parameters

Name	Description	Mandatory	Default	Comment
Account	The Snowflake account identifier	Yes	—	e.g. `xy12345.eu-west-1`
Database	The name of the target database	Yes	—
Schema	The schema to use	Yes	—
Username	The username used to authenticate	Yes	—
Password	The password used to authenticate	Yes	—	Value is hidden
Warehouse	The virtual warehouse to use	No	—
Role	The role to use for the session	No	—
Suffix	Additional parameters appended to the connection string	No	—

⚠️ Important: Make sure identifiers (like db) that contain lowercase characters are double-quoted in any manually entered value. Otherwise, Snowflake assumes they are uppercase, which may result in a "database does not exist or is not authorized" error.

Connection String

A connection string can be entered manually instead of using the parameter form. For the full list of supported connection string parameters, refer to the Snowflake .NET connector documentation.

Local Configuration

The local configuration is defined in a JSON file (stores.json) deployed directly on the Workers' server. A locally configured store takes priority over a store with the same name configured in the States interface.

Name	Description	Type	Mandatory	Comment
ConnectionString	The connection string to connect to the database. Here are the details of all the parameters of the connection string: https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/Connecting.md IMPORTANT: Make sure identifiers (like `db`) in the connection string are double-quoted if they contain lowercase characters. Otherwise, Snowflake assumes they are uppercase. In the case of `db` you’ll get a “database does not exist or is not authorized“ error. See example below.	string	True

Name

Description

Type

Mandatory

Comment

ConnectionString

The connection string to connect to the database. Here are the details of all the parameters of the connection string: https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/Connecting.md

IMPORTANT: Make sure identifiers (like db) in the connection string are double-quoted if they contain lowercase characters. Otherwise, Snowflake assumes they are uppercase. In the case of db you’ll get a “database does not exist or is not authorized“ error. See example below.

string

True

Examples

User + password

This is an example of a simple connection using user/password (authenticator: snowflake, by default)

JSON

{
  "Name": "snowflake",
  "Type": "snowflake",
  "HealthCheck": true,
  "Config": {
      "ConnectionString": "account=bonkivy-gc24111;host=bonkivy-gc24111.snowflakecomputing.com;user=myuser5000;password=$$pa$$w0rd-;db=\"TestDatabase\";schema=TESTSCHEMA;warehouse=COMPUTE_WH"
      }
}

Private key + public key

For key generation and setup on Snowflake side, see: https://docs.snowflake.com/en/user-guide/key-pair-auth

Below is an example of a simple connection using public/private keypair (authenticator: snowflake_jwt) with the keyfile being on disk. See parameters: private_key_file and private_key_pwd (if the file is not encrypted, this parameter is not needed)

JSON

{
  "Name": "snowflake",
  "Type": "snowflake",
  "HealthCheck": true,
  "Config": {
      "ConnectionString": "authenticator=snowflake_jwt;account=fm85971.eu-central-1;user=foo@bar.com;private_key_file=/var/lib/dfakto-workers/rsa_key.p8;private_key_pwd=password;db=\"default_modeling\";warehouse=BEVAULT_DEV;role=BEVAULT-CI;"
      }
}

Below is an example with the full private key in the connection string. See private_key and private_key_pwd

To get the (encrypted or not) private key string to copy-paste, do this:

Bash

cat rsa_key.p8 | sed 's/[ ]*$//' | tr -d '\n'

This should give you a one-line string of the key. Copy it with the -----BEGIN .. ----- and -----END .. -----headers into the connection string.

beVault uses the .NET drivers: Snowflake’s .NET driver documentation says something (incorrect) along the lines of “If the private key value includes any equal signs (=), make sure to replace each equal sign with two signs (==) to ensure that the connection string is parsed correctly.“ That is not true, base64 padding is interpreted strictly by the C# cryptography libraries, adding or removing them will generate errors. If you want to check the validity of the base64 string use this on the PKCS8 PEM file:

Bash

cat rsa_key.p8 | head -n -1 | tail -n +2 | base64 -d > /dev/null

If it says base64: invalid input, something modified the key.

JSON

{
  "Name": "snowflake",
  "Type": "snowflake",
  "HealthCheck": true,
  "Config": {
      "ConnectionString": "authenticator=snowflake_jwt;account=fm85971.eu-central-1;user=foo@bar.com;private_key=-----BEGIN ENCRYPTED PRIVATE KEY-----MIIFJDBWBgkqhkiG9w0BBQ0wSTAxBgkqhkiG9w0BBQwwJAQQVrderJrv9GIoZow1r5Ko3gICCAAwDAYIKoZIhvcNAgkFADAUBggqhkiG9w0DBwQIBm9k+/tNcgQEggTI8IckeTyHcb3pc7RGvkuJqdYW+rKBkDfgfFRxCfvXy0zE0ZZk1fUjfPP61FDEeZWqnFz3ZeNd/Cs0HMrXK14541azYx3SGf/U+7PdrBSMTwKLOmPY4kKk5R3DwmkiWYt2YiB91xRaggUGGmq6t5XXM+uT3nMp7nzk9vB6EBmTlqHIX2kTrLqsiDa7cuXXkrZoyB4fPcgOC5UhQEEyecAOkGbzms9XuIYKEm3dUGrhdDZ0i4ToeOP6QT1z4LoXKVivFL1tX2pErq7TcBfXm7Iii+QJ5wI/cJcCz4RU32E5trHAI5aJ7pDPaRoWfiYsduZgRWpky7Xwr7qheapio0M+FskiNmm/D+IjefdlDg5t7HPmyShZW40XWFsFgm03QhSQggdaN5woJkykv/qNawO5lzKw5QEOJ8Dk57ESDU2yyUwPlW7aqtjjvvtP5hzBdvlMj87oINRJVErgKE4nev1Amyh8JSEbd3Nn/9MfzIQhpccfD5gEEXUmDRHmJKafzu6gi02Ej1TdZ2V2lBpcU6IAUV1eTr9CQuboaayRhN4U6UYgE1x786Q0Y5tkypytHzqVfyrlA0L8H2vAjF4NigKFWQ8MOwjwozSE7YZU05G+7+QYBAELjyCN5LUzOERclOdW8EjBn4IN3hIV+WvkK4mWe+tebdT9hnGNROQ1Xyl/LMDMgnkvtlm3dDmxrm5HDGB/b+9013gHLq6Ck5XQVoDMGfFHiFuD4oMOnL+71RzG4eHAUZ+rWRdkU+v0i6l5ssZBTm2AlleuuOk3UBVh4iXL+QdBQYTFfL5TjxvSPVNI+KH4g9zA3yZF3/mxYzuInAh7XRFW6vD+92FICwc5MSOUknNnkx1gy5pn/pUQCz1Sri+nUC6s7EVMy7sOOvm9zu/yNME6dpMzaZVAa0mf08T48egAkAW+T1hOTTwS+o8sdxOyMpN9UzBUG4bygeUgOjIjiZzdHDMK5WaZk3k8/Eue4gfX7OTzRl0Sh5CerQ7Gm+RI2w/DdtqH8JnsX+04+UrqwNPJDDT5At8gHtR0bC9QvUbNdPQ7Ko+eiT7vClbmxHroOzWt7jvTyoG+4hozFIJJ/731YryXeRxTZw3e8apb/RCXLuiwQnszyX97Np2IO9/nPWuzVRh7slA4PfQq+RnPMrUoD4ZlojeU4HZT24SKOfahS8EPnvV4B/c5edtdlb495D42WV6PXgWpRKPziLna2kGZSH+vTXGdcNoMeC5RSplynERZJpjTLY5Gl4A6THPVcShPMUlmzvuYb+QzqTqs/Gijo+/dBw/eYuyxkqmlHzXC1O+7bLdobpUCAg2Dok69xK5CveF4OfNv3oAnXVMA/hL1HtLbpaOn8OIiMLqtyGnbgh+5hUJieySLL+3kyuVFr3aXViBzjG+RhI2+JOABd6YL+eKH/UhGWYWXYyFMEGZjjm5EYbOefWHvG82RFcLwLArHPQPvlns2fHjvrm8Ye66dVFC2w/aKkj4bUwbvmtZrbHXPwjUW6jXcQouVzYG76PumpdUjFyrWKIV+gGidnShu+29hysCc16zsKwnXDKHx0SW3NYS2zIvcN5GZLyD+gBOMp5AWcgj82HERjUy3BtlrvZUt1DcbawRrG6MBSoz8FXPv/1lk-----END ENCRYPTED PRIVATE KEY-----;private_key_pwd=password;db=\"default_modeling\";warehouse=BEVAULT_DEV;role=BEVAULT-CI;"
      }
}