Authenticating with Snowflake using private key

Get Help
1

Hi,

Is Metabase able to use private key authentication with Snowflake?

In code, I'd do this by setting the privateKey property when creating a JDBC connection.

I tried (slightly speculatively):

  • setting privateKey=[the private key] and;
  • setting the password to the private key;

but no dice.

2

Hi @mslater5
It should be possible, but reading the sample code, it looks like it's decoding the key from file.
Are you able to use DBeaver.io just with the same property?

3

Hi,

I can connect using a private key with DBeaver, by setting the private_key_file driver property:

DBeaver%20Snowflake%20private%20key%20settings
DBeaver%20Snowflake%20private%20key%20settings725×480 18 KB

Note that DBeaver doesn't currently support setting the privateKey property (i.e. achieving the same thing without having to use a file), because the Snowflake driver expects privateKey to be a java.security.PrivateKey and AFAIK, DBeaver can only set String properties. There's some interest in implementing this in DBeaver.

IMHO, the Snowflake documentation on using private keys with the JDBC driver is unnecessarily confusing; the only bit that's tricky is creating the instance of java.security.PrivateKey you need (e.g. using BouncyCastle). Here's some sample Kotlin for using a private key to authenticate to Snowflake:

package sample

import org.bouncycastle.openssl.PEMKeyPair
import org.bouncycastle.openssl.PEMParser
import org.bouncycastle.openssl.jcajce.JcaPEMKeyConverter
import java.io.StringReader
import java.security.PrivateKey
import java.sql.DriverManager
import java.util.Properties

object PrivateKeySnowflakeConnection {

    private const val jdbcUri = "jdbc:snowflake://[redacted].snowflakecomputing.com/?user=metabase_test"

    private const val pemPrivateKeyString = """-----BEGIN RSA PRIVATE KEY-----
[redacted]
-----END RSA PRIVATE KEY-----"""

    @JvmStatic
    fun main(args: Array<String>) {
        DriverManager.getConnection(jdbcUri, Properties().apply { put("privateKey", pemPrivateKeyString.asPrivateKey()) })
    }

    private fun String.asPrivateKey(): PrivateKey = PEMParser(StringReader(this)).use { pemParser ->
        generateSequence { pemParser.readObject() }.map {
            if (it is PEMKeyPair) {
                JcaPEMKeyConverter().getPrivateKey(it.privateKeyInfo)
            } else {
                throw IllegalArgumentException("Unknown entry in PEM data: ${it.javaClass}")
            }
        }.toList()
    }.single()
}
4

@mslater5 Okay, then you should be able to use private_key_file in Metabase too. Note the path and permissions, specially if you're running Metabase in Docker.

But I agree that it's quite complicated docs. I guess it was mainly geared towards developers and not really "end-users" just trying to connect with a key.

5

I can't seem to get it to work. I suspect the problem is that Metabase requires me to set a password, but given the user authenticates using a key, there is no password.

Setting private_key_file=/data/Dev/metabase_test.key (note that using Windows style slashes produces a misleading error) seems to cause the driver to load the key; if I specify a non-existent file, it complains it can't load a key. However, when I try to connect, it says:

JWT token is invalid.

From experience, this is Snowflake-speak for "password incorrect." My theory is that given both password and private key, the Snowflake driver is choosing password.

6

@mslater5 Okay, I guess changes need to be made to the driver, since it requires Password field.
It would likely need a more advanced way of configuring it. You're welcome to open a feature request, though I cannot say when this would be implemented:
https://github.com/metabase/metabase/issues/new/choose

7

OK. Thanks for investigating for me!

8

Just for reference:
https://github.com/metabase/metabase/issues/19191 - upvote by clicking :+1: on the first post