Configuring the JDBC Driver¶

Syntax¶

jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/?<connection_params>

Connection parameters¶

<account_identifier>

Specifies the account identifier for your Snowflake account. For details, see Configuring a client, driver, library, or third-party application to connect to Snowflake. For examples of the account identifier used in a JDBC connection string, see Examples.

<connection_params>

Specifies a series of one or more JDBC connection parameters and session parameters, in the form of <param>=<value>, with each parameter separated by the ampersand character (&), and no spaces anywhere in the connection string.

If you need to set parameter values that use spaces, ampersands (&), equals signs (=), or other special characters, you should URL-encode (link removed) the special characters. For example, if you need to specify a value that contains a space, ampersand, and equals sign in the query_tag session parameter:

String connectionURL = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?query_tag='folder=folder1 folder2&'

Copy

encode the space as %20, the ampersand as %26, and the equals sign as %3D:

String connectionURL = "jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?query_tag='folder%3Dfolder1%20folder2%26'

Copy

As an alternative, rather than specifying these parameters in the connection string, you can set these parameters in a Properties object that you pass to the DriverManager.getConnectionIO method.

Properties props = new Properties();
props.put("parameter1", parameter1Value);
props.put("parameter2", parameter2Value);
Connection con = DriverManager.getConnection("jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/", props);

Copy

Other parameters¶

Any session parameter can be included in the connection string. For example:

BROWSER_RESPONSE_TIMEOUT=<Integer>

Specifies the timeout, in seconds, to wait for a successful authentication from an external browser.

Default is 120.

CLIENT_OUT_OF_BAND_TELEMETRY_ENABLED=<Boolean>

Specifies whether to enable out-of-band telemetry.

Default is true.

CLIENT_SESSION_KEEP_ALIVE=<Boolean>

Specifies whether to keep the current session active after a period of inactivity, or to force the user to login again. If the value is true, Snowflake keeps the session active indefinitely, even if there is no activity from the user. If the value is false, the user must log in again after four hours of inactivity.

Default is false.

CLIENT_SESSION_KEEP_ALIVE_HEARTBEAT_FREQUENCY=<Integer>

Specifies the number of seconds (900-3600) in-between client attempts to update the token for the session.

Default is 3600.

For descriptions of all the session parameters, see Parameters.

Examples¶

The following is an example of the connection string that uses the account name as an identifier for the account myaccount in the organization myorganization.

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?user=peter&warehouse=mywh&db=mydb&schema=public

Copy

The following is an example of a connection string that uses the account locator xy12345 as the account identifier:

jdbc:snowflake://xy12345.snowflakecomputing.cn/?user=peter&warehouse=mywh&db=mydb&schema=public

Copy

Note that this example uses an account in the AWS US West (Oregon) region. If the account is in a different region or if the account uses a different cloud provider, you need to specify additional segments after the account locator.

privateKey property in connection properties¶

This section provides an example of setting the privateKey property to a private key in a file.

This example uses the Bouncy Castle Crypto APIs (https://www.bouncycastle.org/java.html). In order to compile and run this example, you must include the following JAR files in your classpath:

• the provider JAR file (bcprov-jdkversions.jar)

• the PKIX / CMS / EAC / PKCS / OCSP / TSP / OPENSSL JAR file (bcpkix-jdkversions.jar)

where versions specifies the versions of the JDK that the JAR file supports.

To use this example:

1. Copy the sample code below, and replace the following placeholder values:

   Placeholder	Description
   path/rsa_key.p8	Set this to the path and name of the private key file that you generated earlier.
   private_key_passphrase	If you generated an encrypted key, implement the getPrivateKeyPassphrase() method to return the passphrase for decrypting that key.
   account_identifier	Set this to your account identifier.
   user	Set this to your Snowflake login name.
   database_name	Set this to the name of the database that you want to use.
   schema_name	Set this to the name of schema that you want to use.
   warehouse_name	Set this to the name of warehouse that you want to use.
   role	Set this to the name of role that you want to use.

2. Compile and run the sample code. Include the Bouncy Castle JAR files in the classpath.

   For example, on Linux and macOS:

   javac -cp bcprov-jdk<versions>.jar:bcpkix-jdk<versions>.jar TestJdbc.java
   
   java -cp .:snowflake-jdbc-<ver>.jar:bcprov-jdk<versions>.jar:bcpkix-jdk<versions>.jar TestJdbc.java
   

   Copy

   On Windows:

   javac -cp bcprov-jdk<versions>.jar;bcpkix-jdk<versions>.jar TestJdbc.java
   
   java -cp .;snowflake-jdbc-<ver>.jar;bcprov-jdk<versions>.jar;bcpkix-jdk<versions>.jar TestJdbc.java
   

   Copy

Sample code

import org.bouncycastle.asn1.pkcs.PrivateKeyInfo;
import org.bouncycastle.jce.provider.BouncyCastleProvider;
import org.bouncycastle.openssl.PEMParser;
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter;
import org.bouncycastle.openssl.jcajce.JceOpenSSLPKCS8DecryptorProviderBuilder;
import org.bouncycastle.operator.InputDecryptorProvider;
import org.bouncycastle.operator.OperatorCreationException;
import org.bouncycastle.pkcs.PKCS8EncryptedPrivateKeyInfo;
import org.bouncycastle.pkcs.PKCSException;

import java.io.FileReader;
import java.io.IOException;
import java.nio.file.Paths;
import java.security.PrivateKey;
import java.security.Security;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.DriverManager;
import java.util.Properties;

public class TestJdbc
{
  // Path to the private key file that you generated earlier.
  private static final String PRIVATE_KEY_FILE = "/<path>/rsa_key.p8";

  public static class PrivateKeyReader
  {

    // If you generated an encrypted private key, implement this method to return
    // the passphrase for decrypting your private key.
    private static String getPrivateKeyPassphrase() {
      return "<private_key_passphrase>";
    }

    public static PrivateKey get(String filename)
            throws Exception
    {
      PrivateKeyInfo privateKeyInfo = null;
      Security.addProvider(new BouncyCastleProvider());
      // Read an object from the private key file.
      PEMParser pemParser = new PEMParser(new FileReader(Paths.get(filename).toFile()));
      Object pemObject = pemParser.readObject();
      if (pemObject instanceof PKCS8EncryptedPrivateKeyInfo) {
        // Handle the case where the private key is encrypted.
        PKCS8EncryptedPrivateKeyInfo encryptedPrivateKeyInfo = (PKCS8EncryptedPrivateKeyInfo) pemObject;
        String passphrase = getPrivateKeyPassphrase();
        InputDecryptorProvider pkcs8Prov = new JceOpenSSLPKCS8DecryptorProviderBuilder().build(passphrase.toCharArray());
        privateKeyInfo = encryptedPrivateKeyInfo.decryptPrivateKeyInfo(pkcs8Prov);
      } else if (pemObject instanceof PrivateKeyInfo) {
        // Handle the case where the private key is unencrypted.
        privateKeyInfo = (PrivateKeyInfo) pemObject;
      }
      pemParser.close();
      JcaPEMKeyConverter converter = new JcaPEMKeyConverter().setProvider(BouncyCastleProvider.PROVIDER_NAME);
      return converter.getPrivateKey(privateKeyInfo);
    }
  }

  public static void main(String[] args)
      throws Exception
  {
    String url = "jdbc:snowflake://<account_identifier>.snowflakecomputing.cn";
    Properties prop = new Properties();
    prop.put("user", "<user>");
    prop.put("privateKey", PrivateKeyReader.get(PRIVATE_KEY_FILE));
    prop.put("db", "<database_name>");
    prop.put("schema", "<schema_name>");
    prop.put("warehouse", "<warehouse_name>");
    prop.put("role", "<role_name>");

    Connection conn = DriverManager.getConnection(url, prop);
    Statement stat = conn.createStatement();
    ResultSet res = stat.executeQuery("select 1");
    res.next();
    System.out.println(res.getString(1));
    conn.close();
  }
}

Private key file name and password as connection properties¶

You can specify the private key file name and password as separate connection properties, for example:

Properties props = new Properties();
props.put("private_key_file", "/tmp/rsa_key.p8");
props.put("private_key_file_pwd", "dummyPassword");
Connection connection = DriverManager.getConnection("jdbc:snowflake://myorganization-myaccount.snowflake.com", props);

If you specify the private_key_file and private_key_file_pwd parameters, do not specify the privateKey parameter in the connection properties.

Private key file name and password in connection string¶

You can specify the private key file name and password in the connection string, as shown below:

Connection connection = DriverManager.getConnection(
    "jdbc:snowflake://myorganization-myaccount.snowflake.com/?private_key_file=/tmp/rsa_key.p8&private_key_file_pwd=dummyPassword",
    props);

If you specify the private key and password in the connection string, then do not specify the parameters private_key_file, private_key_file_pwd, or privateKey in the connection properties.

Key decryption errors¶

If you use encrypted keys that were generated using OpenSSL V3, you might receive errors similar to the following:

java.security.NoSuchAlgorithmException: 1.2.840.113549.1.5.13 SecretKeyFactory not available

java.security.InvalidKeyException: IOException : DER input, Integer tag error

In this situation, you can use Bouncy Castle to decrypt the key by specifying the following JVM argument:

-Dnet.snowflake.jdbc.enableBouncyCastle=true

Specifying a proxy server by setting Java system properties¶

To connect through a proxy server, you can set the proxy system properties. You can either set these in your code or pass them on the command line to the JVM (Java virtual machine) for your client application.

To set the system properties in your code, call System.setProperty:

System.setProperty("http.useProxy", "true");
System.setProperty("http.proxyHost", "proxyHost Value");
System.setProperty("http.proxyPort", "proxyPort Value");
System.setProperty("http.proxyUser", "proxyUser Value");
System.setProperty("http.proxyPassword", "proxyPassword Value");
System.setProperty("https.proxyHost", "proxyHost HTTPS Value");
System.setProperty("https.proxyPort", "proxyPort HTTPS Value");
System.setProperty("https.proxyUser", "proxyUser HTTPS Value");
System.setProperty("https.proxyPassword", "proxyPassword HTTPS Value");
System.setProperty("http.proxyProtocol", "https");

Copy

To pass the system properties on the command line to your JVM, use the -D command-line option:

-Dhttp.useProxy=true
-Dhttps.proxyHost=<proxy_host>
-Dhttps.proxyPort=<proxy_port>
-Dhttps.proxyUser=<proxy_user>
-Dhttps.proxyPassword=<proxy_password>
-Dhttp.proxyHost=<proxy_host>
-Dhttp.proxyPort=<proxy_port>
-Dhttp.proxyUser=<proxy_user>
-Dhttp.proxyPassword=<proxy_password>
-Dhttp.proxyProtocol="https"

Copy

To bypass the proxy for one or more IP addresses or hosts, set the http.nonProxyHosts system property to the list of these hosts:

• Use a pipe symbol (|) to separate the host names.

• To specify hostnames that match a pattern, use an asterisk (*) as a wildcard character.

The following example demonstrates how to set this system property on the command line:

-Dhttp.nonProxyHosts="*.my_company.com|localhost|myorganization-myaccount.snowflakecomputing.cn|192.168.91.*"

Specifying a proxy server in the JDBC connection string¶

To use a proxy server by setting the following parameters in the JDBC connection string:

• useProxy

• proxyHost

• proxyPort

• proxyUser

• proxyPassword

• proxyProtocol

If your proxy server does not require authentication, you can omit the proxyUser and proxyPassword parameters.

If your proxy server connection requires authentication using a proxy username and proxy password, those credentials may be exposed as plain text by other applications when using the HTTP protocol. To avoid exposing these credentials, use the proxyProtocol parameter to specify the HTTPS protocol.

jdbc:snowflake://<account_identifier>.snowflakecomputing.cn/?warehouse=<warehouse_name>&useProxy=true&proxyHost=<ip_address>&proxyPort=<port>&proxyUser=test&proxyPassword=test

For example:

jdbc:snowflake://myorganization-myaccount.snowflakecomputing.cn/?warehouse=DemoWarehouse1&useProxy=true&proxyHost=172.31.89.76&proxyPort=8888&proxyUser=test&proxyPassword=test

The proxy settings specified in the connection string take precedence over JVM system properties.

If the proxy JVM arguments are set and you do not want to proxy any of your connections, do not set useProxy=false, as it has no effect. Instead, use the following, which effectively bypasses the JVM proxy settings:

useProxy=true
proxyHost=127.0.0.1
proxyPort=8080
nonProxyHosts=*

&nonProxyHosts=<bypass_proxy_for_these_hosts>

&nonProxyHosts=*.my_company.com%7Clocalhost%7Cmyorganization-myaccount.snowflakecomputing.cn%7C192.168.91.*

&proxyProtocol=https

Choosing fail-open or fail-close mode¶

JDBC Driver versions prior to 3.8.0 default to fail-close. Versions 3.8.0 and later default to fail-open. You can override the default behavior in any of the following ways:

• Set the connection property ocspFailOpen to true or false. For example:

  Properties connection_properties = new Properties();
  connection_properties.put("ocspFailOpen", "false");
  ...
  connection = DriverManager.getConnection(connectionString, connection_properties);
  

  Copy

• Set the system property net.snowflake.jdbc.ocspFailOpen to true or false. For example:

  Properties p = new Properties(System.getProperties());
  p.put("net.snowflake.jdbc.ocspFailOpen", "false");
  System.setProperties(p);
  

  Copy

Verifying the OCSP connector or driver version¶

For more information about the driver or connector version, configuration, and OCSP behavior, see OCSP Configuration.

OCSP response cache server¶

Snowflake clients initiate every connection to a Snowflake service endpoint with a “handshake” that establishes a secure connection before actually transferring data. As part of the handshake, a client authenticates the TLS certificate for the service endpoint. The revocation status of the certificate is checked by sending a client certificate request to one of the OCSP (Online Certificate Status Protocol) servers for the CA (certificate authority).

A connection failure occurs when the response from the OCSP server is delayed beyond a reasonable time. The following caches persist the revocation status, helping alleviate these issues:

• Memory cache, which persists for the life of the process.

• File cache, which persists until the cache directory (e.g. ~/.cache/snowflake or ~/.snowsql/ocsp_response_cache) is purged.

• Snowflake OCSP response cache server, which fetches OCSP responses from the CA’s OCSP servers hourly and stores them for 24 hours. Clients can then request the validation status of a given Snowflake certificate from this server cache.

  Important

  If your server policy denies access to most or all external IP addresses and web sites, you must allowlist the cache server address to allow normal service operation. The cache server hostname is ocsp*.snowflakecomputing.cn:80.

  If you need to disable the cache server for any reason, set the SF_OCSP_RESPONSE_CACHE_SERVER_ENABLED environment variable to false. Note that the value is case-sensitive and must be in lowercase.

If none of the cache layers contain the OCSP response, the client then attempts to fetch the validation status directly from the OCSP server for the CA.

Java core logging facilities (Java.util.logging)¶

By default, the java.util.logging uses ConsoleHandler to write to the standard error stream. You can set the Boolean JAVA_LOGGING_CONSOLE_STD_OUT java or connection property to true, which writes all logs to the standard output stream. The default value is false.

If you enable JAVA_LOGGING_CONSOLE_STD_OUT, you can also set the JAVA_LOGGING_CONSOLE_STD_OUT_THRESHOLD java or connection property to set the maximum log level the driver should write to standard output. Any log messages with a higher level than specified are sent to standard error. Possible values for this property include:

• OFF

• SEVERE

• WARNING

• INFO

• CONFIG

• FINE

• FINER

• FINEST

• ALL

To use this logger, specify the following option for the JVM:

-Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.JDK14Logger

Then, you can customize the logging configuration using the application programming interface (API) for the logger.

For example, create a file named logging.properties that includes the following contents:

###########################################################
#   Default Logging Configuration File
#
# You can use a different file by specifying a filename
# with the java.util.logging.config.file system property.
# For example java -Djava.util.logging.config.file=myfile
############################################################

############################################################
#   Global properties
############################################################

# "handlers" specifies a comma-separated list of log Handler
# classes.  These handlers will be installed during VM startup.
# Note that these classes must be on the system classpath.
# ConsoleHandler and FileHandler are configured here such that
# the logs are dumped into both a standard error and a file.
handlers = java.util.logging.ConsoleHandler, java.util.logging.FileHandler

# Default global logging level.
# This specifies which kinds of events are logged across
# all loggers.  For any given facility this global level
# can be overriden by a facility specific level.
# Note that the ConsoleHandler also has a separate level
# setting to limit messages printed to the console.
.level = INFO

############################################################
# Handler specific properties.
# Describes specific configuration information for Handlers.
############################################################

# default file output is in the tmp dir
java.util.logging.FileHandler.pattern = /tmp/snowflake_jdbc%u.log
java.util.logging.FileHandler.limit = 5000000000000000
java.util.logging.FileHandler.count = 10
java.util.logging.FileHandler.level = INFO
java.util.logging.FileHandler.formatter = net.snowflake.client.log.SFFormatter

# Limit the messages that are printed on the console to INFO and above.
java.util.logging.ConsoleHandler.level = INFO
java.util.logging.ConsoleHandler.formatter = net.snowflake.client.log.SFFormatter

# Example to customize the SimpleFormatter output format
# to print one-line log message like this:
#     <level>: <log message> [<date/time>]
#
# java.util.logging.SimpleFormatter.format=%4$s: %5$s [%1$tc]%n

############################################################
# Facility specific properties.
# Provides extra control for each logger.
############################################################

# Snowflake JDBC logging level.
net.snowflake.level = INFO
net.snowflake.handler = java.util.logging.FileHandler

Copy

Specify the JVM parameters in the command line:

java -jar application.jar -Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.JDK14Logger -Djava.util.logging.config.file=logging.properties

Copy

Where application.jar references the application code for the JDBC driver. The log files are located in /tmp/snowflake_jdbc*.

Simple logging facade for Java (org.slf4j)¶

If a logger implementation package (i.e. org.sl4j:sl4j-jdk14 or org.sl4j:slf4j-log4j12) or a custom logger (i.e. your own org.slf4j.impl.StaticLoggerBinder class) has been defined on the classpath, then the driver automatically uses this logger.

You can also explicitly choose to use this logger by specifying the following JVM option:

-Dnet.snowflake.jdbc.loggerImpl=net.snowflake.client.log.SLF4JLogger.

Logging configuration file¶

Alternatively, you can easily specify the log level (https://github.com/snowflakedb/snowflake-jdbc/blob/master/src/main/java/net/snowflake/client/log/SFLogLevel.java) and the directory in which to save log files in the sf_client_config.json configuration file.

This configuration file uses JSON to define the log_level and log_path logging parameters, as follows:

{
  "common": {
    "log_level": "DEBUG",
    "log_path": "/home/user/logs"
  }
}

The driver looks for the location of the configuration file in the following order:

• client_config_file connection parameter, containing the full path to the configuration file.

• SF_CLIENT_CONFIG_FILE environment variable, containing the full path to the configuration file.

• JDBC driver installation directory, where the file must be named sf_client_config.json.

• User’s home directory, where the file must be named sf_client_config.json.

Ensure properties are set correctly¶

The DriverManager.getConnection() method reads only the values of the Properties parameter that match specific, predefined names (“password”, “warehouse”, etc.). If you misspell a property name, or include extra properties, the driver ignores those properties without issuing an error or warning message. This can make it difficult to detect minor misspellings.

Use the right values for connection string and account¶

If you can’t establish a connection, verify that you are specifying the account identifier correctly in the JDBC connection string. For more information about finding your account identifier, see Configuring a client, driver, library, or third-party application to connect to Snowflake.