Supported Databases

​

Native support

• Documentation on required configuration properties to connect to the remote database.
• Reference SQL queries to create the database tables (these are available in mr-directquery/src/test/resources/databases).
• Reference data in a format compatible with the specific database (for Databricks this is available in mr-directquery/src/test/resources/databases/databricks/data. For Microsoft SQL Server and Snowflake you can use the primary dataset in mr-application/src/main/resources/data).
• Databases tested for computational accuracy.

​

Natively supported databases

• Databricks
• Microsoft SQL
• Snowflake

​

Non-native support

​

Snowflake key-pair authentication

directquery:
  database:
    type: snowflake
    snowflake:
      connection-string: ${SNOWFLAKE_CONNECTION_STRING}
      username: ${SNOWFLAKE_USERNAME}
      # ...
      additional-options:
        private-key-base64: ${SNOWFLAKE_PRIVATE_KEY_BASE64}

​

Prerequisites

• OpenSSL 1.1.1 or later, available on the command line.
• The Snowflake user that Atoti Market Risk connects with, the value of SNOWFLAKE_USERNAME.
• A Snowflake role that can run ALTER USER on that user, such as SECURITYADMIN.
• Write access to the environment of the Atoti Market Risk process, to set SNOWFLAKE_PRIVATE_KEY_BASE64.

​

Set up the key pair

1. Generate an unencrypted PKCS#8 RSA private key and its matching public key. The following commands create the rsa_key.p8 private key and the rsa_key.pub public key in the current directory.
   openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt
   openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub
   
   To protect the key at rest instead, generate an encrypted PKCS#8 key with openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -v2 aes-256-cbc -out rsa_key.p8, and supply the passphrase through the directquery.database.snowflake.additional-options.private-key-pwd property.
2. Register the public key on the Snowflake user. The key value is the contents of rsa_key.pub without the PEM header, the PEM footer, and the line breaks, which the following command prints.
   grep -v '^-----' rsa_key.pub | tr -d '\n'
   
   Run the following statement under a role that can alter the user, such as SECURITYADMIN.
   ALTER USER <snowflake_user> SET RSA_PUBLIC_KEY='<stripped contents of rsa_key.pub>';
   
   Verify that Snowflake stored the key:
   DESC USER <snowflake_user>;
   
   The RSA_PUBLIC_KEY_FP row holds a SHA256: fingerprint. An empty value means the key was not registered.
3. Base64-encode the private key and expose it to Atoti Market Risk. The following command encodes the entire rsa_key.p8 file. The resulting value feeds the directquery.database.snowflake.additional-options.private-key-base64 property.
   export SNOWFLAKE_PRIVATE_KEY_BASE64=$(base64 < rsa_key.p8 | tr -d '\n')
   

​

Verify the connection

​

Rotate the key pair

1. Generate a new key pair, following the steps above.
2. Register the new public key as the user’s second key:
   ALTER USER <snowflake_user> SET RSA_PUBLIC_KEY_2='<stripped contents of the new rsa_key.pub>';
   
3. Update SNOWFLAKE_PRIVATE_KEY_BASE64 with the new base64-encoded private key and restart Atoti Market Risk.
4. Confirm the connection succeeds, then retire the old key:
   ALTER USER <snowflake_user> UNSET RSA_PUBLIC_KEY;