Snowflake Db Store

Description	Connection to a Snowflake database
Type	snowflake
Category	Db

Configuration

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)

CODE

{
  "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;"
      }
}