Blog Post

Overview: –

• In modern data architectures, integrating data warehouses like Snowflake with application integration platforms such as Boomi is a crucial requirement, especially when prioritizing security. Snowflake, known for its scalability, security, and performance, requires a secure authentication mechanism when integrated with Boomi to ensure protected data transfers. While traditional username/password authentication is common, it can pose security risks. Key Pair Authentication is a highly recommended, secure approach that eliminates password-based authentication, providing a more robust connection between Snowflake and Boomi.
• This blog will guide you through the process of implementing Key Pair Authentication for Snowflake and integrating it securely with Boomi.

What is Snowflake Key Pair Authentication and Its Benefits?: –

• Key Pair Authentication is a method that uses asymmetric cryptography to authenticate users securely. This involves generating a public-private key pair.
• Snowflake key-pair authentication involves using a private key and a corresponding public key to establish a secure connection. where the public key is registered with Snowflake, and the private key is used to sign authentication requests.
• Instead of sending a password over the network, the client uses the private key to sign a request, which Snowflake verifies using the associated public key.
• Snowflake also supports rotating public keys in an effort to keep data secure and meet compliance standards.
• This authentication method requires, as a minimum, a 2048-bit RSA key pair. You can generate the Privacy Enhanced Mail (PEM) private-public key pair using OpenSSL.

Implementation: –

• Before implementing Key Pair Authentication in Boomi, ensure you have the following:
  • A Snowflake account with appropriate permissions.
  • OpenSSL installed for key generation.
  • Snowflake JDBC driver (Jar)

Configuring key-pair authentication in SnowFlake: –

• Generate the Key Pair: you have the option to generate encrypted or unencrypted private keys. Generally, it is safer to generate encrypted keys.
• Open a OpenSSL terminal and run the following commands:

1. You can generate either an encrypted version of the private key or an unencrypted version of the private key.

• To generate an unencrypted version, use the following command:

openssl genrsa 2048 | openssl pkcs8 -topk8 -inform PEM -out rsa_key.p8 -nocrypt

• To generate an encrypted version, use the following command:

openssl genrsa 2048 | openssl pkcs8 -topk8 -v2 des3 -inform PEM -out rsa_key.p8

For encrypted version, you have to provide Encryption password.

Here, I’m using encrypted version.

Note: The Above command will generate a private key in PEM format which will be stored generally in local root directory.

2.From the command line, generate the public key by referencing the private key. The following command assumes the private key is encrypted and contained in the file named rsa_key.p8.

• To generate Public key, use the following command:

openssl rsa -in rsa_key.p8 -pubout -out rsa_key.pub

Then, Enter pass phrase for rsa_key.p8

• Open the current directory in the system explorer, where you can find two files named rsa_key.p8 and rsa_key.pub. To open the current directory from command line you can use following command:

explorer .

• Assign the public key to a Snowflake user:
  1.Log in to your Snowflake account, navigate to Projects, then click on Worksheets. To create a new SQL worksheet, click on the ‘+’ icon.

2. Run the below command in the command line and Copy the content of the public key, but make sure to exclude the —–BEGIN PUBLIC KEY—– and —–END PUBLIC KEY—– lines

type rsa_key.pub

3.Add the following SQL command in the worksheet, replace username with the username you want to use for key pair authentication and also replace public_ssh_key with encrypted public key which we copied before, and then run the command:

ALTER USER username SET RSA_PUBLIC_KEY=’ public_ssh_key’;

Note: Only owners of a user, or users with the SECURITYADMIN role or higher can alter a user.

4.Execute the following command to retrieve the user’s public key fingerprint. Replace username with the desired username, then copy the output:

DESC USER username;

SELECT SUBSTR((SELECT “value” FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()))

WHERE “property” = ‘RSA_PUBLIC_KEY_FP’), LEN(‘SHA256:’) + 1);

5.Run the following command on the command line:

openssl rsa -pubin -in rsa_key.pub -outform DER | openssl dgst -sha256 -binary | openssl enc -base64 writing RSA key

6.Compare both outputs. If both outputs match, the user correctly configured their public key.

Note: –

• • • Snowflake supports multiple active keys to allow for uninterrupted rotation. Rotate and replace your public and private keys based on the expiration schedule you follow internally.
    • Currently, you can use the RSA_PUBLIC_KEY and RSA_PUBLIC_KEY_2 parameters for ALTER USER to associate up to 2 public keys with a single user.

Snowflake key-pair Configuration in Boomi:

• Now, let’s go to Boomi, create a new connection for Snowflake, and implement a simple use case to fetch data from the Nations table, which is present by default in Snowflake.

1. Go to platform.boomi.com
2. Login by giving your valid credentials (Username and Password)

3.Click on ‘Integration’

• Before diving into the implementation make sure snowflake jar file added to your runtime If not follow below steps
• Go to settings and then choose account information and setup.

• Go to the Account Libraries section and upload the JAR file to it.

• Go to the Build tab, Select Create New Component and Choose the Custom Library Component option.

• In the Custom Library component, choose the “Connection” option, select “Snowflake” as the connection type, and then select the Snowflake JAR file that you uploaded into the Account Libraries.

• Create a packaged component and Deploy the Custom Library Component to the runtime that you want to connect with your Snowflake database.
• Then restart your runtime engine.
  4.Create a New Process and Select start shape with “No Data” type

5. Add a Snowflake Shape and Click on “+” in the connection. It’ll open a new component to create new Snowflake connection.

6.Configure the Snowflake connection details. Replace ‘ACCOUNTNAME’ in the URL below with your Snowflake account name and add it to the connection URL field.

jdbc:snowflake:// ACCOUNTNAME.snowflakecomputing.com

7.Enter the required Snowflake details, including the Username, Warehouse Name, Database Name, and Role.

Note: No password is required, as we are using key pair authentication.

Here, I am using Sample Database and Schema which was provided in Snowflake by default.

8.Run the below command in the command line and Copy the content of the public key, but make sure to exclude —–BEGIN ENCRYPTED PRIVATE KEY—–and —–END ENCRYPTED PRIVATE KEY—– lines

type rsa_key.p8

9.Paste the encrypted private key you copied earlier into the Private Key String field. Then, enter the Passphrase you created when generating the private key.

10.Check if the connection is configured correctly by clicking on Test Connection, Then select the runtime and Then Next.

If the connection failed then check wheather Snowflake dependency added to the atom and also configuration details which you have provided.

11.If connection is successful click on save and close.

12.Select Action as “QUERY” and Click on “+” in the Operation.

13.It will open a new operation component and click on import operation.

14.Select Runtime and click on Next.

15.Select the Object, click on Next and then click on finish

Here, I am using Nation Table which was provided by Snowflake as a sample data.

16.Click On Save and close

17.Click on ‘Ok’ for the Snowflake configuration and add a stop shape to end the flow

Finally, our process should look like this:

18.Save and Test the process.

19.Go to the stop shape and Shape Source Data and see the response.

You can check that data has been successfully retrieved from Snowflake