snowflake-jdbc: SNOW-618478: Unable to authenticate with a private key by using spring datasource properties

We are using the standard configuration to setup spring datasources. When we want to use a private key to authenticate, the key is interpreted as a string and not a private key.

net.snowflake.client.jdbc.SnowflakeSQLLoggedException: Invalid parameter value type: java.lang.String, expected type: java.security.PrivateKey.

spring.datasource.username=${snowflake.username}
spring.datasource.driverClassName=net.snowflake.client.jdbc.SnowflakeDriver
spring.datasource.url=jdbc:snowflake://${snowflake.account}.snowflakecomputing.com/?warehouse=${snowflake.warehouse}&db=${snowflake.database.name}&schema=${snowflake.schema}
spring.datasource.dataSourceProperties.privatekey=MII...YKF

The final case is to use Azure AppConfiguration/KeyVault to get properties.

About this issue

  • Original URL
  • State: closed
  • Created 4 years ago
  • Comments: 30 (5 by maintainers)

Most upvoted comments

@sfc-gh-wfateem I just opened a PR to resolve this issue. As further justification of the need, I’d add my blocking concern:

I want to leverage this driver through Spark.read in a cluster. In order to use private_key_file, I’d have to make the file available to every executor in the cluster. If I instead use private_key_base64, I can read the bytes from disk once and allow Spark to pass the base64 encoded String to all the executors transparently.

Happy to make adjustments to the PR as necessary, I’d really like to get this mainlined asap and I’m certain all the Hikari users represented here would appreciate it as well.

We currently use HikariCP + Snowflake JDBC in our project and faced this issue while attempting to inject privateKey as a data source property. By looking at the source code there’s no reason for the driver to accept privateKey as an object, a string should be ok, and the driver can implicitly transform it as a java.security.PrivateKey object, thoughts?

Code Snippet:

import org.bouncycastle.asn1.pkcs.PrivateKeyInfo
import org.bouncycastle.jce.provider.BouncyCastleProvider
import org.bouncycastle.openssl.PEMParser
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter

import java.io.StringReader
import java.security.{PrivateKey, Security}
import java.sql.{Connection, DriverManager, ResultSet, Statement}
import java.util.Properties

object TestJdbc {

  // Private key as a string
  private val PRIVATE_KEY_STRING: String =
    """-----BEGIN PRIVATE KEY-----
      |-----END PRIVATE KEY-----
      |""".stripMargin

  private object PrivateKeyReader {
    def getFromString(privateKeyString: String): PrivateKey = {
      Security.addProvider(new BouncyCastleProvider())
      val pemParser = new PEMParser(new StringReader(privateKeyString))
      val pemObject = pemParser.readObject.asInstanceOf[PrivateKeyInfo]
      pemParser.close()

      val converter = new JcaPEMKeyConverter().setProvider(BouncyCastleProvider.PROVIDER_NAME)
      converter.getPrivateKey(pemObject)
    }
  }

  def main(args: Array[String]): Unit = {
    val url = "jdbc:snowflake://account-prod.snowflakecomputing.com"
    val prop = new Properties()
    prop.put("user", "service_user")
    prop.put("privateKey", PrivateKeyReader.getFromString(PRIVATE_KEY_STRING))
    prop.put("db", "SNOWFLAKE_SAMPLE_DATA")
    prop.put("schema", "TPCH_SF1")
    prop.put("warehouse", "COMPUTE_WH")
    prop.put("role", "READ_ROLE")

    val conn: Connection = DriverManager.getConnection(url, prop)
    val stat: Statement = conn.createStatement()
    val res: ResultSet = stat.executeQuery("select 1")
    if (res.next()) {
      println(res.getString(1))
    }
    conn.close()
  }
}

Setting a DataSource like that should work too, but there’s an easier approach. With the PR in place, you can just pass the new private_key_base64 property into the config using addDataSourceProperty

val privateKeyString = Base64.getEncoder().encodeToString(Files.readAllBytes(Paths.get("key-aes256.p8")))

val hc: HikariConfig = new HikariConfig()
hc.setJdbcUrl(jdbcUrl)
hc.setUsername(user)
hc.addDataSourceProperty("private_key_base64", privateKeyString)
new HikariDataSource(hc)

For reference and my own understanding:

  • the linked PR above (@ets great work) will address the problem of passing privatekeys to pooling libs like hikari
  • the code snippet above from @josecsotomorales works for a single connection, but not for pooling libs (so my snippet below would not currently work)
  • once the linked PR is finished users could simply use privateKeyString and wouldn’t need to self parse the strings to keys
val config = new HikariConfig()
val dataSource = new SnowflakeBasicDataSource()
val privateKey = PrivateKeyReader.getFromString(privateKeyString) // privateKeyString ala '-----BEGIN ...'

dataSource.setPrivateKey(privateKey)
...
config.setDataSource(dataSource)
new HikariDataSource(config)

@NitinSharma1991 I’m reopening this issue because it looks like I erroneously perceived that changes were made in that PR, but I see now that it was closed and the Hikari team commented that they’re not going to make any changes. We’ll need to take a closer look.

On Hikari 4.0.3: was seeing Invalid parameter value type: java.lang.String, expected type: java.security.PrivateKey. even though I gave it java.security.PrivateKey.

private_key_base64 did not work for me: Missing password.

What did work was:

      var dataSource = new SnowflakeBasicDataSource();
      dataSource.setUrl(String.format(URL_TEMPLATE, account));
      if (warehouse != null) {
        dataSource.setWarehouse(warehouse);
      }
      dataSource.setAccount(account);
      dataSource.setUser(user);
      dataSource.setPrivateKey(toPrivateKeyObj(privateKey));
      if (role != null) {
        dataSource.setRole(role);
      }
      if (database != null) {
        dataSource.setDatabaseName(database);
      }
      dataSource.setLoginTimeout(20);

      var config = new HikariConfig();
      config.setDataSource(dataSource);
      return new HikariDataSource(config);