How to setup Key Pair Authentication in Snowflake and How to configure this enhanced security mechanism in Qlik Replicate
Snowflake supports using key pair authentication for enhanced authentication security as an alternative to basic authentication (i.e. username and password). This article covers end-to-end setup for Key Pair Authentication in Snowflake and Qlik Replicate.
This authentication method requires, at minimum, a 2048-bit RSA key pair. You can generate the Privacy Enhanced Mail (i.e. PEM) private-public key pair using OpenSSL.
Qlik Replicate will use the ODBC driver to connect snowflake and ODBC is one of the supported clients which will support key pair authentication.
Let's assume, you decided to use key pair authentication for the Snowflake user which is used in Qlik Replicate to connect to Snowflake. You have to follow the below process to convert user authentication from basic to key pair.
Step 1: Generate the Private Key
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 in the command prompt:
This command generates the public key in PEM format:
Step 3: Store the Private and Public Keys Securely
Copy the public and private key files to a local directory for storage and record the path to the files. Note that the private key is stored using the PKCS#8 (Public Key Cryptography Standards) format and is encrypted using the passphrase you specified in the previous step.
However, the file should still be protected from unauthorized access using the file permission mechanism provided by your operating system. It is your responsibility to secure the file when it is not being used.
Step 4: Assign the Public Key to a Snowflake User
Describe the user to see current information. We can see that there is no public key assigned to the HDW user. Therefore, the user needs to use basic authentication.
Execute an ALTER USER command to assign the public key to a Snowflake user.
Step 6: Configure Qlik Replicate to Use snowflake Key Pair Authentication
Enter the snowflake server name and the username that we configured key pair authentication.
We need to set the below parameters to set the authentication method as SNOWFLAKE_JWT, passing the private key and password from step 1.
Try to do the test connection\browse the database and you can see a missing password error. We can't ignore the password in the endpoint UI and at the same time, we shouldn't enter the password as we are using keypair authentication.
You can key in a dummy password, such as dummy, dummy123, etc., in the UI to eliminate the missing password error.
Finally, browse the database and do the test connection.