Articles in this section

See more

Configuring Connections: Snowflake with Key-pair Authentication

Snowflake is moving towards requiring more secure authentication methods beyond simple username and password, which it has supported in the past (see this announcement).  Amongst the various secure authentication methods available for Snowflake, key-pair authentication is a viable option and this article will provide the setup steps for connecting QuerySurge to Snowflake using that method.

Setting up a QuerySurge Connection with the Snowflake JDBC Driver

A QuerySurge Snowflake Connection with key-pair authentication is set up using the Connection Extensibility feature of the QuerySurge Connection Wizard.  You should not use the built-in connection wizard for Snowflake, if you intend on using this authentication method.

  1. Download the current Snowflake JDBC driver. The driver download can be found here (only deploy the snowflake-jdbc-x.x.x.jar).  You MUST install version 3.16 or higher of the JDBC driver.
     
  2. Deploy the Snowflake JDBC driver jar to your Agent(s). The procedure for deploying a driver to a QuerySurge Agent is here (for Agents on Windows) and here (for Agents on Linux).  If an existing, older version of the driver already exists in your Agents' JDBC directories, you should replace it with the newer version.
     
  3. Deploy the private key file to a readable location (i.e. readable to the user under which the Agent is running) on each of your Agents.  This location must be the same for every Agent in the environment.
     
  4. Log into the QuerySurge project portal as a QuerySurge Admin user, and navigate to the Administration view. Steps for using the Connection Extensibility feature can be found here. To use the Connection Extensibility option in the Connection Wizard with the Snowflake driver, you'll need the following information:

    • Driver Class:

      net.snowflake.client.jdbc.SnowflakeDriver

    • Connection URL Format:

      jdbc:snowflake://<account_name>.snowflakecomputing.com/?<connection_params>

  5. In addition to the standard connection properties, you will need to add both "private_key_file", to specify the location of your private key file (this should be the location on the Agent where the private key is deployed, from step 3) and "private_key_file_pwd", to specify the password for the private key file.  Do NOT enter any Authentication Info in the Username or Password fields.  When you've entered your Connection information, the Connection Wizard will look like the following:

    jdbc:snowflake://somewhere.snowflakecomputing.com/?user=myUser&warehouse=myWarehouse&db=myDB&schema=mySchema&private_key_file=C:/Certs/Snowflake/rsa_keys.p8&private_key_file_pwd=myPassword

    Note: When specifying the private key file location in the connection you should use forward-slashes when specifying the path, even if the Agent is installed on Windows.
    Note: Your private key path and filename should not contain any spaces.
    Note: The "region_id" parameter has been deprecated. Other connection parameters, such as "warehouse" and "db" can be appended to the Connection URL. For information, see the Snowflake JDBC documentation.
    Note: By default, Snowflake does not limit data to the database identified in the URL. In order to limit your queries to the current schema/database, set the URL parameter CLIENT_METADATA_REQUEST_USE_CONNECTION_CTX to 'true'. If set to true, Snowflake will use the context set in the JDBC URL to restrict the calls to this schema.
    Note: If you receive an error like "SQL compilation error: Object does not exist, or operation cannot be performed", you may need to add the ROLE parameter to the connection URL:

    role=MY_ROLE

    where MY_ROLE is replaced with an actual role value that has access to the Snowflake objects.

  6. If you have a test Query, feel free to enter it in order to verify that your Connection parameters are correct. Any query that returns a small amount of information is appropriate - one row is enough. You can use the Test Connection button if you've entered a Test Query:


    The Connection Test gives a popup showing whether the Connection was successfully completed:

Important Note
If you encounter issues with your private key being read/decrypted by the Snowflake JDBC driver, as described here, you may have to make configuration modifications in order to enable the internal decryption libraries:

  • Stop the Agent service
  • For Windows
    • Run the QuerySurgeAgentw.exe executable (\\<QuerySurge Install Dir>\QuerySurge\agent\QuerySurgeAgentw.exe) as an administrator and click on the Java tab
    • Add the following string to the Java Options field: -Dnet.snowflake.jdbc.enableBouncyCastle=TRUE and click OK
  • For Linux
    • Open the Agent's ctl startup script (e.g., /opt/QuerySurge/agent/ctl.sh) in an editor like vi or VIM
    • Locate the line that begins with nohup and enter -Dnet.snowflake.jdbc.enableBouncyCastle=TRUE between "java/bin/java" and "-Xmx${HEAP}" and save the changes
  • Start the Agent service

Related articles